Microsoft – SQL Server – Error: 3314, Severity: 17, State: 3. During undoing of a logged operation in database [db-name]

Background

Getting the error pasted below:

Microsoft – SQL Server – Error: 3314, Severity: 17, State: 3. During undoing of a logged operation in database [db-name]

This is a special case; what makes it special:

  • The database is mirrored
  • The database engine has tried repairing the corruption; even so far as accessing the partner node and reading data from it
  • It very much appears that corruption is limited to the log files, and not to the actual database files

ErrorLog

Here is a sample errorlog entries:

Recovery of database 'dbHR' (5) is 35% complete (approximately 342 seconds remain). Phase 2 of 3.

103431 transactions rolled forward in database 'dbHR' (5).

Recovery of database 'dbHR' (5) is 35% complete (approximately 342 seconds remain). Phase 3 of 3.

Database mirroring is active with database 'dbHR' as the principal copy. This is an informational message only. No user action is required.

Database mirroring is attempting to repair physical page (1:6749899) in database "dbHR" by requesting a copy from the partner.

Database mirroring is attempting to repair physical page (3:4464209) in database "dbHR" by requesting a copy from the partner.

Database mirroring successfully repaired physical page (1:6749899) in database "dbHR" by obtaining a copy from the partner.

Database mirroring successfully repaired physical page (3:4464209) in database "dbHR" by obtaining a copy from the partner.

Using 'dbghelp.dll' version '4.0.5'

**Dump thread - spid = 0, EC = 0x000000008069EB20

***Stack Dump being sent to d:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0002.txt

........
........

Error: 3314, Severity: 17, State: 3.
During undoing of a logged operation in database 'dbHR', an error occurred at log
record ID (16152:1943503:10). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

Restore was successful but deferred transactions remain.
These transactions cannot be resolved because there are data that is unavailable.

Either use RESTORE to make that data available or drop the
filegroups if you never need this data again.

Dropping the filegroup results in a defunct filegroup.

Error: 3414, Severity: 21, State: 2.
An error occurred during recovery, preventing the database 'dbHR' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup.

If errors are not corrected or expected, contact Technical Support.

Fix

To fix:

At a high level, here are the basic steps:

  • Determine the current state of the database by querying databasepropertyex
  • If Status returned indicates that we are in “Suspect” mode, Issue sp_resetstatus/ alter database – set emergency to take the database out of suspect mode
  • In our database the database is mirrored and so pretty much un-touchable from an Administrative standpoint, so we need to take out of mirroring mode

0) Determine database’s current state

DatabasePropertyex
http://msdn.microsoft.com/en-us/library/ms186823.aspx

Syntax:

        select databasepropertyex(<database-name>);

Sample:

        select databasepropertyex('dbHR');

Result:

Here are the possible options:

  • OFFLINE
  • RESTORING
  • RECOVERING
  • SUSPECT
  • EMERGENCY

1) Reset the database status

sp_resetstatus
http://msdn.microsoft.com/en-us/library/ms188424.aspx
Syntax:

        exec sp_resetstatus

Sample:

        exec sp_resetstatus [dbHR]

 

Result:


Result: Warning : You must recover this database prior to access.

Note that one benefit from writing things down is that understanding changes all the time.

It was upon reviewing this posting that I found out that sp_resetstatus is indeed deprecated.

It has been replaced with ALTER DATABASE / set emergency.

ALTER DATABASE / set emergency
http://msdn.microsoft.com/en-us/library/bb522682.aspx

Syntax:

</pre>

ALTER DATABASE <database-name> SET EMERGENCY;

Sample:


ALTER DATABASE <dbHR> set emergency;

2) As the database is mirrored, disengage mirroring

ALTER DATABASE SET DATABASE MIRRORING
http://msdn.microsoft.com/en-us/library/bb522476.aspx

Syntax:

         alter database [database-name] set partner off;

Sample:

         alter database [dbHR] set partner off;

3) Detach the database

sp_detach_db
http://msdn.microsoft.com/en-us/library/ms188031.aspx

Syntax:

        exec sp_detach_db ;

Sample:

       exec sp_detach_db [dbHR];

Likely, you will get an error such as :


  msg 947, level 16, state 3, line 1 Error while closing database '<database-name>'

It is OK to disregard this message as it appears to be indicative of the corruption.

4) Next step is to re-attach the database.  Keep the database data files, while enlisting the database log files for rebuilding.

Rebuilding in the sense that:

  1. Roll-forward of un-hardened log data will not occur
  2. Un-doing of logged operation will not occur

CREATE DATABASE / for attach_rebuild_log
http://msdn.microsoft.com/en-us/library/ms176061.aspx


Syntax:

       create database <database-name>
       on
              (filename = filenameData1)
             , (filename = filenameData2)
             , (filename = filenameData3)
       for attach_rebuild_log;

 

Sample:

        create database [dbHR]
        on
             ( filename = 'n:\data\dataHR.mdf')
           , ( filename = 'n:\data\dataHRRecruitment.ndf')
           , ( filename = 'n:\data\dataHRStockOptions.ndf')
        for attach_rebuild_log;

Summary

As hinted earlier, a blog posting is a blow by blow recording of a debugging session.  There are so many things that force corruption and so many ways to try to correct him.

 

References

  1. Rebuilding the log when attaching multiple MDF Files
    http://www.databasejournal.com/features/mssql/article.php/3836971/Rebuilding-the-log-when-attaching-multiple-MDF-files.htm
  2. Suspect database in MS SQL Server v2005
    http://www.thecomputerguy.co/node/17
  3. SQL 2008 Data Recovery
    http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/4b8ad8c6-b26f-4477-90ec-33257e8585b3/
  4. Recovering DB with only MDF
    http://social.msdn.microsoft.com/forums/en-US/sqldisasterrecovery/thread/d48bb79b-0403-46d8-abb9-11fff40e7e72/
  5. How to handle db in suspect
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=152976

Leave a Reply

Please log in using one of these methods to post your comment:

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