Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Corrupt LOG file - sp_attach_single_file_db

Author  Topic 

Kristen
Test

22859 Posts

Posted - 2013-08-21 : 17:51:42
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.

Kristen
Test

22859 Posts

Posted - 2013-08-21 : 18:17:54
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
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2013-08-22 : 04:25:12
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
   

- Advertisement -