SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Corrupt LOG file - sp_attach_single_file_db
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/21/2013 :  17:51:42  Show Profile  Reply with Quote
Hi gang ... long time no speak :)

I have been asked to move a DB to new hosting, and from SSMS=10.50.2500 SSMS=10.50.4021, and from dedicated server in data centre to a virtual machine on the cloud.

Went to copy the latest FULL Backup to the new Cloud VM and found it was a month old ... on checking it appears that the TLog backup has been failing for a month (which, due to the way the tasks are scheduled, has prevented the FULL backup running and, luckily!, old stale backups have not been cleared down).

I ran a TLog backup and got:

"BACKUP detected corruption in the database log. Check the errorlog for more information.
BACKUP LOG is terminating abnormally.
"

SQL Error log has:

"Backup detected log corruption in database MyDatabase. Context is FirstSector. LogFile: 2 'E:\MSSQL\Log\MyDatabase.ldf' VLF SeqNo: xd5440 VLFBase: x177040000 LogBlockOffset: x19365fe00 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x1000004 LogBlock.StartLsn.Blk: x5b50000 Size: xd PrevSize: xe
Backup Error: 3041, Severity: 16, State: 1.
Backup BACKUP failed to complete the command BACKUP LOG MyDatabase. Check the backup application log for detailed messages.
"

I ran

USE MyDatabase
GO
DBCC CHECKDB WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY, ALL_ERRORMSGS 
GO

and got no output at all - no errors. (It took 12 minutes to run, so I assume it did do something!)

DBCC was run on the original machine, I've copied and restored (without error) a FULL backup from earlier today onto the new Cloud server and then a "final" DIFF backup (after the DIFF backup got to 100% there was then a further 10 minute delay - I am assuming that was committing transactions in the DIFF - the DIFF file was 3GB, which is a huge amount for a single day's differences [compared to normal running])

So ... hopeful that MDF is OK ... I was going to try a Single File Attach next. I took the restored DB (on new Cloud server) offline and am now copying the 50GB MDF file somewhere safe, plus the 52GB LDF file too (I guess its been stockpiling transactions for a month to have grown that big!!) in case I need to have a second go.

Any advice or suggestions would be appreciated, thanks.

Edited by - Kristen on 08/22/2013 04:26:20

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/21/2013 :  18:17:54  Show Profile  Reply with Quote
Hmmm ... I stupidly took the DB offline (ready for copying the files to a safe location) rather than Detaching it ... I copied the files and then attempts a Single File Attach and it appeared to work:

EXEC sp_attach_single_file_db 'MyDatabase', 'F:\MSSQL\DATA\MyDatabase.mdf'


File activation failure. The physical file name "G:\MSSQL\LOG\MyDatabase.ldf" may be incorrect.
New log file 'F:\MSSQL\DATA\MyDatabase.LDF' was created.


I had already renamed MyDatabase.ldf so it would not be found by sp_attach_single_file_db, looks like this might have worked (other than that the LDF file is now on the wrong drive).

I'm going to do a DBCC and then a Backup/Restore to get everything in the right place

Edited by - Kristen on 08/22/2013 04:21:21
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/22/2013 :  04:25:12  Show Profile  Reply with Quote
This morning I found that the Log Backup task was complaining ... I hadn't realised that sp_attach_single_file_db created Log File as SIMPLE Recovery Model. Fixed that now (and all the palaver for pre-initiaising the Log re: VLFs) ... although that required yet-anther-full-backup to kickstart the TLogging.

Blinking slow the virtual machine that the client has chosen ... no wonder its cheaper than what they had before; backup is 16.581 MB/s instead of the 62.592 MB/s they had before.

How to make a drama into a 3-part-mini-series eh?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000