Rebuilding Log when SQL Server Services were not properly closed and log file is deleted.

  1. We have a database that is in a “Recovery Pending State”.

1

We can see “AdventureWorks2012” is in Recovery Pending state.

We can check the logs to see what happened.

2

  1. Put the database in Emergency mode.

3

4

When the database is in Emergency mode we can only read the data from the database. As the Startup Recovery was not done the database is in inconsistent state.

  1. To bring back the database ONLINE. (Remember it is inconsistent and SHOULD NOT be used in production). Run DBCC CHECKDB with REPAIR ALLOW_DATA_LOSS.

But before running DBCC CHECKDB we have to put the database in SINGLE_USER mode. Because Emergency is a MULTI_USER mode and REPAIR_ALLOW_DATA_LOSS needs a database that is in SINGLE_USER mode.

5

Now run DBCC CHECKDB

6

The Message is:

File activation failure. The physical file name “C:\AdventureWorks2012_Database\AdventureWorks2012_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 ‘AdventureWorks2012’ 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.

We can see the message that Log has been rebuilt.

7

Put the database back in MULTI_USER mode.

8

NOTE: DATABASE IS STILL CORRUPT AND SHOULD NOT BE USED IN PRODUCTION AS IT COULD BE INCONSISTENT.

How To: Rebuild Log when SQL Server Services were not properly closed and log file is deleted.

Leave a Reply