Re-building database log files that have been corrupted for MSSQL

Firstly check the database for log corruption with:

Bare in mind that DBCC CHECKDB WITH ALL_ERRORMSGS,NO_INFOMSGS will NOT check the logs and hence will not identify any corruptino in the logs!

You should also check the event log for any hardware or MSSQL related errors such as:

"Description: Executing the query "BACKUP LOG [PCDB_PROD] TO  DISK = E'\\MyDatabase2..." failed with the following error: "BACKUP detected corruption in the database log. Check the errorlog for more information."

and you could also run chkdsk to identify any file system related errors.

The easiest way to remedy this situation is to firstly ensure you have a FULL backup of the database.

There are two methods to remove the courrpt logs - the first can be performed while the database is online and hence not affect downtime:

Firstly run a DBCC CHECKDB WITH ALL_ERRORMSGS,NO_INFOMSGS on the database to look for any errors.

Method 1: which will incur downtime! as described below:

*** You should also ensure there are no transactions pending on the database by using the activity monitor on the SQL server - as if there are the operation of setting the database into recovery mode will hang! ***

Put the database into emergency mode:

ALTER DATABASE <dbname> SET EMERGENCY, SINGLE_USER

Proceed by re-building the log file as follows:




 ALTER DATABASE <dbname> REBUILD LOG ON (NAME=<logical file name>, FILENAME='<full path to new file>')

 Now we can simply bring the database back online, putting it into multi-user mode:

ALTER DATABASE MYDATABASE set ONLINE ONLINE;
ALTER DATABASE MYDATABASE set ONLINE MULTI_USER;

Method 2: requires database downtime and is as follows:

*** You should also be aware that transactions occuring between the backup above and when you detach the database below that they will be lost! ***

Proceed by detaching the database and then renaming (or deleting) the log file assosiated with the database.

We should then attach the database to the SQL server and re-build the logs with:

CREATE DATABASE [mydatabase]
ON (FILENAME=E:\Path\to\database.mdb)
FOR ATTACH_REBUILD_LOG;

Now ensure that the recovery model is set to the appropriate mode!

and then run a integrity check on the database as follows:

DBCC CHECKDB WITH ALL_ERRORMSGS,NO_INFOMSGS

Finally make a full backup of the database.

Sources

http://blogs.msdn.com/b/glsmall/archive/2013/11/14/recovering-from-log-file-corruption.aspx
http://blogs.msdn.com/b/suhde/archive/2009/07/11/database-corruption-part-5-dealing-with-log-file-corruption.aspx