2

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 24x7 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.

Improve this question
5
  • Alternative storage isn't mentioned in your steps. Given that a fault with the physical disk is the likely root cause, you have somewhere else to put the database right?
    – Mark Storey-Smith
    Commented Apr 22, 2014 at 20:39
  • @MarkStorey-Smith Perfectly right. Unfortunately, disputes with the hosting provider are limiting the options. That's about as diplomatic as I can put it.
    – RBarryYoung
    Commented Apr 22, 2014 at 20:43
  • Not ideal. If you haven't already done so, a disclaimer/risks email to whoever is going to get fired if there is a catastrophic failure as a result of both doing this or ignoring it might be sensible. And CC that persons boss. Given the constraints a CHKDSK would be a worthwhile step to add. However, a bad block is usually a sign of impending doom and excluding them with CHKDSK is just buying time.
    – Mark Storey-Smith
    Commented Apr 22, 2014 at 20:51
  • @MarkStorey-Smith Not a problem, they are aware in great detail and in writing of my position on this matter. At the moment, the hosting provider is on the hook.
    – RBarryYoung
    Commented Apr 22, 2014 at 20:56
  • Weird. I just got a badge for 2500+ views of this question, yet it has never received any votes, Up or Down. I'm not complaining mind you, just seems odd...
    – RBarryYoung
    Commented Jun 18, 2015 at 15:49

1 Answer 1

Reset to default
6

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.

Improve this answer
6
  • First, I am not backing up nor restoring the Log file, so AFAIK, that would preclude any way of retaining the corruption. Secondly, our two approaches are almost identical, except for step #4, Attach the database and rebuild the log file. Does this actually work? I was under the impression that when Attaching an MDF you had to have the LDF also, can you really "rebuild" it? If so, how? I do know that RESTORE does this automatically (which is why I planned to use it), but I have never heard that abouut ATTACH?
    – RBarryYoung
    Commented Apr 22, 2014 at 16:08
  • 1
    When you backup the database, you're backing up the data and the log file both. The log file is required for phase 2 and 3 database recovery, which is done whenever a database is brought online. I have added instructions on how to attach and rebuild the log to the answer. This works, as I have done it several times in the past. The risk is you essentially throw out your log file, but as long as your database isn't changing between backup and detach, you should be fine.
    – Mike Fal
    Commented Apr 22, 2014 at 16:17
  • That's not quite right. If a DB full backup backed up both the entire data and log files, then they would have to be almost twice as large in most cases. From size alone you can tell that a BAK does not have both the MDFs and all of the LDFs too. The article says that the full backups only contain "sufficient transaction log records" to allow rolling forward the active transactions and to roll back the uncommitted ones. So, it should only be capturing the current transactions in the backup, though I suppose there's the chance that old corrupt log records are also be picked up.
    – RBarryYoung
    Commented Apr 22, 2014 at 17:56
  • However, the ATTACH_REBUILD_LOG option is attractive as it seems like this approach could be both faster and smaller. thanks..
    – RBarryYoung
    Commented Apr 22, 2014 at 17:56
  • 2
    @RBarryYoung your presumption about the size of a backup is incorrect, in at least two ways: (1) An MDF file is not necessarily "full" - it can contain a lot of empty pages, and these are not backed up. (2) A backup can be performed with compression/encryption, and this can drastically change the file size as well.
    – Aaron Bertrand
    Commented Apr 22, 2014 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.