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.