Home > Recover Database from Lost or corrupted Log File, Recovery > Recover Database from Lost or corrupted Log File

Recover Database from Lost or corrupted Log File

Not getting much into theoretical part, you can read more about features in details from BOL or Search it on the web, let’s start with the issue and resolution.
Issue:  

SQL Server shutdown unexpectedly (not clean shutdown) or Server Crashed or disk Crashed. On recovering the system or on starting the SQL Server services Database state changes to suspect. On checking the error you found

Message1:|
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Database_LogFile.LDF’. Diagnose and correct the operating system error, and retry the operation.

Message2:|
File activation failure. The physical file name “D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Database_LogFile.LDF” may be incorrect.

Message3:|
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Messag4:|
Backup detected log corruption in database system. Context is FirstSector. LogFile: 2 “D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Database_LogFile.LDF “’ VLF SeqNo: x2572 VLFBase: x4a60000 LogBlockOffset: x4ae3200 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x2043 LogBlock.StartLsn.Blk: x200000 Size: x0 PrevSize: x0

 

Message5:|
Msg 945, Level 14, State 2, Line 1

Database ‘Database Name’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Resolution

 

In case u lost the Log file (.Ldf)(Disk corruption, file deleted or SQL Server not able access the log file due to latency or disk issue) then the transaction which are not committed or not written to the data pages (hardened) will be lost (Might be possibility that u may see some data consistency issue).

Check the status of the Database by querying the system tables or views

select name,state,state_desc from sys.databases  where  name=’XYZ’

Name State State_desc
Database 3 RECOVERY_PENDING

 The only possible way to bring the database online is rebuilding the log file.

Set the database in Emergency Mode

ALTER DATABASE XYZ SET EMERGENCY

select name,state,state_desc from sys.databases  where  name=’XYZ’

Name State State_desc
Database 5 EMERGENCY

 Set the database in single user mode with rollback immediate mode

ALTER DATABASE XYZ SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Rebuild the log file

DBCC CHECKDB(‘XYZ’,REPAIR_ALLOW_DATA_LOSS)

Result MessageFile activation failure. The physical file name “D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\XYZ_log.LDF” may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Warning: The log for database ‘XYZ’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

 Check the database status

select name,state,state_desc from sys.databases  where  name=’XYZ’

Name State State_desc
Database 0 Online

The Database is still in single user mode, set it to multi user mode

ALTER DATABASE XYZ SET MULTI_USER

 

Backup the Database immediately.

  1. January 11, 2011 at 6:14 am

    good

  2. vaxa
    November 8, 2012 at 2:07 pm

    Amazing, works well, I used this process once 8 months ago, then had the same issue yesterday, found URL in one-Note (glad I took the reference) and used it again, great post, thank you

  3. msufian
    November 8, 2012 at 2:29 pm

    I have recently noticed that in some cases where storage is SAN,NAS or DAS and the disk reas reponse time is very slow due to HBA card , or Switch link between Storage and Server sql server is not able to read properly and leaves the databases in suspect or in “In Recovery State”. To resolve the issue recycle / bounce the SQL Server Services and if stil database is not recovered engage the Storage and Windows team to investigate the issue.

  4. vaxa
    November 9, 2012 at 11:36 am

    also I have come across the issue that DB did not recover properly and I could not enter data into the DB

  5. chandra
    June 15, 2013 at 11:30 am

    Good post

  6. September 20, 2014 at 9:01 pm

    It’s going to be finish of mine day, however before finish I am
    reading this great piece of writing to improve my experience.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: