Tuesday, February 4, 2014

Error: 9003, Severity: 20, State: 9 SQL Server 2012

I have got this error message earlier this morning and all my user database backups failed due to this.

The log scan number (87817:27:0) passed to log scan in database 'AdventureWorks2012' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during Startup.

These are the steps that I took to resolve the issue.

I ran DBCC CHECKDB 
and there are lot of allocations errors and database has been corrupted. 

Fix1: Restore the Previous nights good backup can fix this. 

Before you restore make sure you have good backup it can be verified by running the below.

RESTORE VERIFYONLY FROM DISK = H:\Backups\AdventureWorks2012.BAK
GO
and then restore to the earlier nights good backup.

================================================================

Fix2: 
Rename your log file or move your log file to different location. 
You can you do this by detaching the database first and then move the files or rename the files. 

USE master;
GO
EXEC sp_detach_db @dbname = 'AdventureWorks2012';
Now rename the .ldf  file or move to a different location.
CREATE DATABASE AdventureWorks2012 
    ON (FILENAME = 'D:\MSSQL\DATA\AdventureWorks2012_DATA01.mdf')
   --(FILENAME = 'E:\MSSQL\LOGS\AdventureWorks2012_LOG01.ldf') 
    FOR ATTACH;

OR


EXEC sp_attach_single_file_db @dbname = 'AdventureWorks2012', 
    @physname = 
N'MSSQL\DATA\AdventureWorks2012_DATA01.mdf';

Note: I commented .LDF file attachment, without it a NEW log file will be created in the default location.
and run the DBCC CHECKDB (Databasename)