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.