SQL Server – Fixing a Corrupt Log File with Minimal Downtime

corruptionsql serversql-server-2008-r2

I am working with a client who has been receiving the following error from their Log backups of one of their SharePoint databases:

Backup detected log corruption in database FOOPORTAL_SP2010_Config. Context is FirstSector. 
  LogFile: 2 'D:\MSSQL2K8\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FOOPORTAL_SP2010_Config_log.LDF' 
  VLF SeqNo: x864 VLFBase: x172d0000 LogBlockOffset: x176b1800 
  SectorStatus: 2 LogBlock.StartLsn.SeqNo: x771 LogBlock.StartLsn.Blk: x1f0c 
  Size: x400 PrevSize: x200

I understand that this is indicative of problems with the disk HW and or IO subsystem, and we have been pursuing that with the Hosting Provider. Specifically, this appears to be a "bad spot" on the/a disk, because it is consistently reported for the log file of this DB, but not other errors have been reported for the other 6 DB log backups over the last few week, nor for any of the 20+ other DB's full data backups.

My immediate issue is that the client has been running this way for several weeks, because this is a mandated 24×7 service and they haven't been able to give me a downtime service window to fix this problem until (suddenly) tonight, for 2 hours. Unfortunately, DB corruption and this kind of Admin work is not my specialty in SQL Server, and I am not certain what the best/safest/most reliable way to proceed is.

My tentative plan right now is:

  1. Take a full backup of the DB as soon as the downtime window starts (full backups have been working without error through out this)
  2. Detach the old DB
  3. Delete the data files for the space
  4. Rename the Log file (still retaining it)
  5. Restore a new copy of the DB from the fresh backup from step 1.

My concerns/anxiety over this are:

  • Should this work? Or is htere a safer/more reliable approach?
  • I only have 2 hours, so is there a more time efficient approach?

Any other suggestions are welcome.

Best Answer

If your log file is corrupt, my concern is that a backup/restore would retain the corruption. My approach (which would probably be faster to complete) would be:

  1. Run a full backup.
  2. Detach database.
  3. Delete/rename the log file.
  4. Attach the database and rebuild the log file.
  5. Take a second full backup.

To attach the database and rebuild the log file, it's just additional syntax in the CREATE DATABASE statement:

CREATE DATABASE [foo]
ON (FILENAME=<<Path to data file>>)
FOR ATTACH_REBUILD_LOG;

This will force SQL Server to rebuild the log file when it attaches the database.

The risk in this is losing whatever transactions happen between the completion of the full backup and detaching the database, but it sounds like this will be a minimal risk. If you can "freeze" Sharepoint at the beginning of the downtime window, this would help mitigate that risk.

Related Question