SQLServerWiki

“The Only Thing That Is Constant Is Change”

Rebuilding Transaction Log in SQL Server 2000, 2005, 2008

Posted by database-wiki on March 6, 2011

Warning: You will lose all the uncommitted transactions in the logfile and chances are database might become inconsistent.

For SQL Server 2000:

At a point the database is marked suspect, as the log corrupt. We need to place it in Emergency Mode and rebuild the log. I am going to ellaborate on the first option

1. Place database in single user mode.
   sp_configure ‘allow’, 1
   reconfigure with override
   go
   update sysdatabases set status = 32768 where name = ‘ops_data’
   go

2. The database is in Emergency Mode.

3. dbcc rebuild_log (‘ops_data’,’D:\logs\ops data test2_log.ldf’)

4. Remember to turn ‘allow updates’ off
  

sp_configure ‘allow’, 0
reconfigure with override
go

5. Database will come up in dbo access to bring it online, go to the database properties and remove the restricted access check box.

For SQL Server 2005 and 2008:

Here are the steps:

In the management studio query window.

1. Get the dbid for the database to which you are going to rebuild the log.

Select * from sys.sysdatabases

2. Pass the dbid and get logical name and the physical name from the sysaltfiles. In my case I have randomly choosen ‘8’.

select name,filename from sys.sysaltfiles where dbid=’8′

3. To rebuild we need to put the database in emergency.

alter database mydatabase set emergency

4. To rebuild we need to put the database in single user so that no one access it.

alter database mydatabase set single_user

5. stop sql server and remove the old file (the location of the old file is give by filename output of the query in step 2)and keep it separately.

6. Once the old file is backup in different location, give the same name, filename returned by step 2 in the below command and run it.

alter database mydatabase rebuild log on

(Name=mydatabase_log,filename=’mydatabase_log.ldf’)

7. Finally bring the database online.

alter database mydatabase set online

mydatabase is the name of the database for which you are rebuilding the log.

5 Responses to “Rebuilding Transaction Log in SQL Server 2000, 2005, 2008”

  1. RZ said

    Of all the forums I’ve read related to the above topic, this posting got me the closest to rebuilding the LDF file giving only the MDF. I was able to rebuild the LDF and brought the actual database (MDF) back on line; but with some minor tweeks (for my scenario) of the above steps…I am rerunning the steps to verify…

  2. pratichi said

    very useful

  3. emmy said

    thanks a lot for this. very helpful

  4. TripeHound said

    Thanks for this … saved a personal (and therefore not backed up 🙂 ) database whose log was corrupt.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: