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
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.
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
Here are the possible options:
1) Reset the database status
exec sp_resetstatus [dbHR]
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
</pre> ALTER DATABASE <database-name> SET EMERGENCY;
ALTER DATABASE <dbHR> set emergency;
2) As the database is mirrored, disengage mirroring
ALTER DATABASE SET DATABASE MIRRORING
alter database [database-name] set partner off;
alter database [dbHR] set partner off;
3) Detach the database
exec sp_detach_db ;
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:
- Roll-forward of un-hardened log data will not occur
- Un-doing of logged operation will not occur
CREATE DATABASE / for attach_rebuild_log
create database <database-name> on (filename = filenameData1) , (filename = filenameData2) , (filename = filenameData3) for attach_rebuild_log;
create database [dbHR] on ( filename = 'n:\data\dataHR.mdf') , ( filename = 'n:\data\dataHRRecruitment.ndf') , ( filename = 'n:\data\dataHRStockOptions.ndf') for attach_rebuild_log;
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.
- Rebuilding the log when attaching multiple MDF Files
- Suspect database in MS SQL Server v2005
- SQL 2008 Data Recovery
- Recovering DB with only MDF
- How to handle db in suspect