Category Archives: ldf

Rebuilding MS SQL Log File

There are situation where you need to rebuild your Microsoft SQL Server’s transaction log file. Today we discuss about such situations and how we can resolve it. In production environments where lot of data is being handled, the possibility of increasing the log file size we can’t control. We can recover the space by running the shrink command. But there will be situations even after running the shrink command the size will not reduce or the total database performance will not improve. One more situation is that log file is getting removed accidentally. So what can we do now? This is where we need to think about rebuilding the log file. DBCC REBUILD_LOG command can be handy to rebuild the SQL log file.
One thing we need to make sure before running this command is that some how we need to have the database exists in SQL Server. Setting the database to emergency recovery mode will solve this problem. Databases which lost its log file accidentally can be directly set to emergency recover mode, but for the first case (log file performance issue) we need to follow the below steps.

  • Stop SQL Server.
  • Delete Log file (.LDF) file.
  • Start the SQL Server


Since the log file got deleted if you try to access the database it will throw error, so don’t try to access still the time it got rebuild. Now we see how we can set the database to emergency recovery mode. Run the following commands to do so. You have to run these commands under master database.

USE Master
GO

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN
UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'DATABASE_NAME'
IF @@ROWCOUNT = 1
BEGIN COMMIT TRAN
RAISERROR('Emergency Mode Successfully Set', 0, 1)
END
ELSE
BEGIN ROLLBACK
RAISERROR('Setting Emergency Mode Failed', 16, 1)
END
GO

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

Once you have finished running the above commands start SQL Server. Again don’t forget one thing “Don’t try to access the database.”

Above commands changed the database to emergency mode. Now run the following commands to rebuild the log file.

DBCC REBUILD_LOG(''DATABASE_NAME'','C:\Database\logfilename.ldf')
ALTER DATABASE 'DATABASE_NAME' SET MULTI_USER
GO

DBCC CHECKDB ('DATABASE_NAME')

A brand new transaction log file got created for your existing database from the scratch. You may lose some transactional integrity by using this method, but it is not really a big issue comparing to the situation you have.