(New for 2020: we’ve published a range of SQL Server interview candidate screening assessments with our partner Kandio, so you can avoid hiring an ‘expert’ who ends up causing problems. Check them out here.)
(Check out my Pluralsight online training course: SQL Server: Detecting and Correcting Database Corruption.)
[Edit 2017: Although this is an old post, it’s entirely relevant in all versions of SQL Server still.]
This is a post I’ve been trying to get to since I started blogging a couple of years ago: how to re-attach a detached SUSPECT database. This is a pretty common scenario I see on the forums – a database goes SUSPECT so the DBA tries to detach/attach, which fails. I wrote a demo for my corruption session at TechEd this year that shows how to create a SUSPECT database with a hex editor, then detaches it and shows how to re-attach and fix it. It’s going to be a long blog post, but bear with me – you never know when you’ll need to know how to recover from this.
Creating a SUSPECT Database
First off I’m going to create a simple database to use, called DemoSuspect with a table and some random data.
USE [master]; GO CREATE DATABASE [DemoSuspect]; GO USE [DemoSuspect]; GO CREATE TABLE [Employees] ( [FirstName] VARCHAR (20), [LastName] VARCHAR (20), [YearlyBonus] INT); GO INSERT INTO [Employees] VALUES ('Paul', 'Randal', 10000); INSERT INTO [Employees] VALUES ('Kimberly', 'Tripp', 10000); GO
Now I’ll perform an update in an explicit transaction and force it to be written out to disk with a CHECKPOINT. I’ve accidentally deleted Kimberly’s bonus!
-- Simulate an in-flight transaction BEGIN TRAN; UPDATE [Employees] SET [YearlyBonus] = 0 WHERE [LastName] = 'Tripp'; GO -- Force the update to disk CHECKPOINT; GO
Then in another window, I’ll simulate a crash using:
SHUTDOWN WITH NOWAIT; GO
Now that SQL Server is shutdown, I’m going to simulate an I/O failure that corrupts the log file. I’m going to use a hex editor to do this – my editor of choice is the immensely popular and useful XVI32, written by Christian Maas. I opened the log file, filled the first section with zeroes, and then saved it again. See the screenshot below. (As a small note of warning, this hex editor will truncate files that are over 2GB. Used the HxD editor instead for larger files.)
When I start up SQL Server again, it will try to run recovery on the DemoSuspect database and fail. This will put the database into the SUSPECT state.
So I restarted SQL Server, let’s try getting in to the DemoSuspect database.
USE [DemoSuspect]; GO
Msg 945, Level 14, State 2, Line 1 Database 'DemoSuspect' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Now let’s check the database status:
SELECT DATABASEPROPERTYEX (N'DemoSuspect', N'STATUS') AS N'Status'; GO
Status ------- SUSPECT
At this point, the correct procedure is to restore from backups. If there are no backups available, then the next best thing is to get the database into EMERGENCY mode and extract as much data as possible, or run EMERGENCY-mode repair. However, I’m going to try the detach/attach route instead.
Detaching the Database
On SQL Server 2005 you can detach a SUSPECT database using sp_detach_db, but on later versions SQL Server won’t let you do this:
EXEC sp_detach_db N'DemoSuspect'; GO
Msg 3707, Level 16, State 2, Line 1 Cannot detach a suspect or recovery pending database. It must be repaired or dropped.
I was *so* pleased when I saw this change was made. I’m going to have to set the database offline to release the NTFS-locks on the files, copy the files to somewhere safe, then drop the database and delete the files. It’s no longer possible to accidentally detach a SUSPECT database.
-- Not allowed on 2008 - let's copy then drop ALTER DATABASE [DemoSuspect] SET OFFLINE; GO -- ***** COPY THE FILES ***** -- Copy... then: DROP DATABASE [DemoSuspect]; GO
Now the DemoSuspect is really detached from SQL Server, and now the fun starts, which is why I’m sure many of you are reading this post.
Re-attaching a SUSPECT Database
Let’s try the obvious sp_attach_db:
EXEC sp_attach_db @dbname = N'DemoSuspect', @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DemoSuspect.mdf', @filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DemoSuspect_log.ldf'; GO
Msg 5172, Level 16, State 15, Line 1 The header for file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DemoSuspect_log.ldf' is not a valid database file header. The PageAudit property is incorrect.
Hmm. How about using the ATTACH_REBUILD_LOG option on CREATE DATABASE? That should create a new log file for me:
CREATE DATABASE [DemoSuspect] ON (NAME = N'DemoSuspect', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DemoSuspect.mdf') FOR ATTACH_REBUILD_LOG GO
Depending on the version of SQL Server you’re using, you’ll see either:
Msg 5172, Level 16, State 15, Line 1 The header for file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect_LOG.ldf' is not a valid database file header. The PageAudit property is incorrect. File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect_LOG.ldf" may be incorrect. The log cannot be rebuilt because the database was not cleanly shut down. Msg 1813, Level 16, State 2, Line 1 Could not open new database 'DemoSuspect'. CREATE DATABASE is aborted.
or the slightly less helpful:
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DemoSuspect_log.LDF" may be incorrect. Msg 1813, Level 16, State 2, Line 1 Could not open new database 'DemoSuspect'. CREATE DATABASE is aborted. Msg 5243, Level 22, State 8, Line 1 An inconsistency was detected during an internal operation. Please contact technical support.
Hmm. The database knows that there was an active transaction. Using the ATTACH_REBUILD_LOG command only works if the database was cleanly shut down and the log is missing. Even removing the damaged log file makes no difference.
Basically the problem is that the database wasn’t cleanly shutdown, which means that recovery HAS to run and complete before the database can be attached again. Given that our log file is corrupt, that’s impossible.
So, never detach a suspect database.
The only way to get the database back into SQL Server is to use a hack. I’m going to create a new dummy database with the exact same file layout as the detached database. Then I’m going to set the dummy database offline, swap in the corrupt database files, and bring the database online again. If all goes well, the corrupt database will be attached again.
The one major downside of this is that if the SQL Server instance doesn’t have instant initialization enabled (see How to tell if you have instant initialization enabled?), then creating the dummy database could take a long time if the data files are very big. This means that your application is offline while the files are created and zero’d out.
You’ll need to delete the existing files. Before doing this you want to make absolutely sure you’ve got multiple copies of the corrupt database files… just in case. After deleting the files, I can create my dummy database and set it offline.
CREATE DATABASE [DemoSuspect]; GO -- Check the files are there... ALTER DATABASE [DemoSuspect] SET OFFLINE; GO
If you forget to delete the existing corrupt files first, you’ll get the following error:
Msg 5170, Level 16, State 1, Line 1 Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DemoSuspect.mdf' because it already exists. Change the file path or the file name, and retry the operation. Msg 1802, Level 16, State 4, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Now I’ll delete the file created for the dummy database, copy back in the corrupt database files, and bring the database online, checking its state:
ALTER DATABASE [DemoSuspect] SET ONLINE; GO SELECT DATABASEPROPERTYEX (N'DemoSuspect', N'STATUS'); GO
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DemoSuspect_log.LDF" may be incorrect. Msg 945, Level 14, State 2, Line 1 Database 'DemoSuspect' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed. Msg 5243, Level 22, State 8, Line 1 An inconsistency was detected during an internal operation. Please contact technical support.
This looks like it failed, but it didn’t. If I try the status check again, I get:
Status ------- SUSPECT
Woo-hoo – I’m back to having a SUSPECT database attached again – after having to mess about deleting and copying files around. Not good. Now I can actually fix it.
Repairing a SUSPECT Database
If you don’t have any backups, then the only way to get into the database is to use EMERGENCY mode. This lets you into the database but you need to be aware that recovery has not completed so the contents of the database are transactionally (and possibly structurally) inconsistent. I’m going to choose to repair the database using EMERGENCY-mode repair. See CHECKDB From Every Angle: EMERGENCY mode repair – the very, very last resort for a detailed description of this tool. Note that you have to put the database into EMERGENCY and SINGLE_USER modes to do this.
ALTER DATABASE [DemoSuspect] SET EMERGENCY; GO ALTER DATABASE [DemoSuspect] SET SINGLE_USER; GO DBCC CHECKDB (N'DemoSuspect', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS; GO
Msg 5172, Level 16, State 15, Line 1 The header for file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DemoSuspect_log.LDF' is not a valid database file header. The PageAudit property is incorrect. File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DemoSuspect_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. The Service Broker in database "DemoSuspect" will be disabled because the Service Broker GUID in the database (B72D1765-80C6-4C2F-8C12-5B78DAA2DA83) does not match the one in sys.databases (001AE95A-AE22-468F-93A4-C813F4A9112D). Warning: The log for database 'DemoSuspect' 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.
First off it tries to do the regular ATTACH_REBUILD_LOG. When that fails, DBCC CHECKDB takes over and forces the log to be rebuilt, after trying to force as much of the damaged log to be processed for recovery as it can. It then runs a full repair, in case there’s anything corrupt in the database – in this case there isn’t so there are no corruption messages in the output.
Notice the line about the Service Broker GUID being wrong. I had to use the hack method to get the database attached again, but when I created the dummy database, it created a Service Broker GUID for the DemoSuspect database in master.sys.databases. When I swapped in the corrupt database, it has a different GUID – so now I can’t use Service Broker until the Service Broker GUID is reset using the NEW BROKER option of ALTER DATABASE (see this post for details).
So what’s the state of the data after all of that?
-- Now try again... USE [DemoSuspect]; GO -- Check the state SELECT DATABASEPROPERTYEX (N'DemoSuspect', N'STATUS') AS N'Status'; GO -- What about the data? SELECT * FROM [Employees]; GO
Status ------- ONLINE (1 row(s) affected) FirstName LastName YearlyBonus ---------- --------- ------------ Paul Randal 10000 Kimberly Tripp 0 (2 row(s) affected)
Kimberly doesn’t get a bonus this year – she won’t be happy! This is contrived and flippant, of course, but it illustrates the point that after doing an EMERGENCY-mode repair, transactions that were active at the time the log was damaged will not get a chance to roll-back, most likely. In this case, I know what was going on when the crash occurred, but what about on a busy OLTP system with hundreds or thousands of active transactions? What state will the data be in?
Yes, you can recover from a detached SUSPECT database, but it’s not pretty and you have to be very careful. The best course of action is always to have a comprehensive backup strategy that allows you to restore as quickly as possible. If you do have a SUSPECT database and no backups, use EMERGENCY mode to access and/or repair the database. Hopefully this article will help people that find themselves in these situations – let me know if it helped you.
105 thoughts on “Creating, detaching, re-attaching, and fixing a SUSPECT database”
Is there any way to recover a detached database with a corrupt file header in a secondary file?
Long story short, some months back I had a detached database (detached for a good reason) with two files in the primary filegroup, few other files in other filegroups and, as far as I could tell, the second file in the primary group had a whole bunch of damage, including to the header page.
Hi Gail – I don’t believe so. I’ll need to try it to be 100% certain though. Cheers
So what happens after you are up and running? Should you do a full backup before you can take log backups or do any other maintenance? Should you recreate the database so that the GUID problem is fixed?
Hey Rob – well, it totally depends on how you want to move forwards. Do you really want to stay on the same hardware that corrupted the database? Do you want to keep using that database knowing that the data within it is transactionally inconsistent? If you want to use Service Broker ever again then you’ve got no choice but to create a new database – if not then it’s not relevant. Yes, if you want to continue using this database and taking log backups then you’ll need to take a full – rebuilding the log breaks the log backup chain.
i am using sql server 2000. yesterday my server shows blue screen after that when i start again it was showing messges like registry or system files corrupted….
after that i remove HDD from server and put it on another machine to copy databse files(.mdf and .ldf) but i found that mdf file size has changed from 1.6 GB to 2048 KB and
LDF file showing 1.6 GB from 2048 KB. after that i was trying to copy both files into new location, but only able to copy MDF file. while copying ldf file i am getting message like
I/O error. i don’t have latest backup.
so i want to know after recovering ldf file from HDD can i would be able to get back my original database. pls help.
I’ll let your thread on sqlteam.com handle this.
When I get to the step where:
ALTER DATABASE DemoSuspect SET EMERGENCY;
ALTER DATABASE DemoSuspect SET SINGLE_USER;
DBCC CHECKDB (DemoSuspect, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
my database actually doesn’t get in emergency mode (sys.databases showsRecovery_Pending). As a result the DBCC will get and error:
Msg 922, Level 14, State 1, Line 1
Database ‘CLM’ is being recovered. Waiting until recovery is finished.
and will not execute.
Is there a way to force the database to actually get into Emergency status?
The only way to do it in your case is to shutdown the server, remove the log file and then do the same set of steps. The recovery system has got itself into a twist based on corruptions in your log. Hope this helps.
PingBack :) Excellent Work!
Thanks for your approach, very useful.
Hi, thank you, its works for me. Look at abouve solution.
I tried restore database any forms, but i can’t.
Look message error:
System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided.
Attach database with wizard organization_MSCRM
ALTER DATABASE [organization_MSCRM] SET EMERGENCY;
ALTER DATABASE [organization_MSCRM] SET SINGLE_USER;
DBCC CHECKDB (N’organization_MSCRM’, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
— IF every think ok, execute this:
ALTER DATABASE [organization_MSCRM] SET MULTI_USER;
Nice article, you helped me a lot!
Recently my database was stopped because of insufficient space for log file. The mount volume was full. SQL server had restarted the database and database went into recovery state. It is a large database (4 TB),so it took its own sweet time to recover (13 hours!).
I was not able to take it in Emergency mode (Same error as “Gabriela Nanau”).
During such scenario, can we shutdown the SQL Server,remove the file and do the given steps? Will be get any time advantage or should we always wait for database to recover itself?
You could, but I would never do that as you will be corrupting your data and possible your database. You need to let the crash recovery complete. There’s no alternative without causing data loss + corruption.
Thanks for your reply Paul!
Hi, I also can confirm that this really works. Many thanks, Paul!
While trying to repair a suspect database I get following error message:
SQL Server Assertion: File: , line=476 Failed Assertion = ‘pHoBtAccess->GetHoBtColumnCount () > 0’. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
Any idea what this means and what can be done to resolve this?
Yes, you’ve got metadata corruption and need to restore from your backups.
A life saver although I only had to recreate the databases & replace the files with the suspect ones & all was well.
Thanks, this helped me out a lot today in fixing a bad database in development. I’m ready for when someone screws it up in production, hopefully that someone won’t be me :)
Thanks Paul, this article saved me a lot of time. First time it happened and it worked flawless on a sql server 2008 r2. You rock! Thank you very much.
You save my day thanks.
Thank you, Paul very much!
I have a database that was detached cleanly however when reattaching it using Management Studio there was permissions problem with accessing the LDF file. An error was thrown and the attach dialog said that because it couldn’t access the old log file it created a new one. The DB got attached and it’s now online and because it’s not a critical DB I think I’m fine. The problem is I’d like to point the DB back to the original ldf file since I do have it but everything I’ve tried throws an error saying that it doesn’t match the MDF. Any idea on how to do this? Most scenarios I’ve seen described similar to this are cases where people want to attach the mdf and the ldf file is missing but I actually have a perfectly good ldf.
You can’t now that there’s a new log file. There’s no need to do so either. You’ll just need to grow the new log file to whatever size it was before, as the new one will only be 0.5MB with two VLFs.
This was extremely helpful! It saved me a BUNCH of effort, so thank you!
I just have a minor modification that might be related to my use of sqlserver express. After I took the dummy database offline and copied over the corrupted database, when I tried to bring it back online it failed. So I had to run a restore task on the database. The restore process failed, but it came online and allowed me to run queries on the database.
excellent..I am pretty new to databases,Please let me know if my summarization of above issue is correct
We did a checkpoint ,so the data went to mdf file.But server didnt shut down properly and the log file was made corrupt by us,so sql after restarting tried to rollback uncommitted transactions,but due to corrupt log file,it was not able to do.
So when we ran DBCC check DB ,mdf file was not corrupt ,so we were able to bring database to state before shutdown .
Yes, it was the state before shutdown – with the effects of the uncommitted transactions still in the database as they were not rolled back. Your data is transactionally inconsistent and you need to manually figure that out based on your knowledge of the workload and possible in-flight transactions.
Stupid question here, but what if it is the master database?
You can’t detach the master database. If it’s suspect, the instance won’t start and you need to rebuild master and then restore from your most recent backup, and then recreate all server-scoped objects created since your last master backup.
I really hope it can help me, but I have gotten into some problems.
I’m not experienced in handling problems in MSSQL.
Hope you can help me.
I had a server crash about two weeks ago and sorry to say I had no backup.
What is worse is that the Whole accounting system for my two Companies was on that server.
I have managed to copy out all the important data through the program to datarecovery.com.
Whats left is getting to use the files. I am not exactly sure what did crash the server. It migt be the disk getting full. Anyway I was entering the database in this case at the time when I discovered the problem. The data files was on another partition on the same disk as the system boot (that get full).
I did try to follow Your guide to create an identical database as you discribe set it ofline, canged the database files and tried to set it online again.
I had to rename the log file from AxNF.ldf to AxNF_log.ldf in that process. Not sure if thats the way to do it. If that will work.
I got this error Message:
Msg 926, Level 14, State 1, Line 1
Database ‘AxNF’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 4060, Level 11, State 1, Line 65536
Cannot open database “AxNF” requested by the login. The login failed.
Msg 18456, Level 14, State 1, Line 65536
Login failed for user ‘AX\geir’.
Hope you can help.
I was a little quick.
The database got into suspect mode.
Setting it into Emergency mode was sucsessful.
Setting it into single_user mode was sucsessful.
But I got this Message when I tried to run the checkdb:
Msg 946, Level 14, State 1, Line 1
Cannot open database ‘AxNF’ version 655. Upgrade the database to the latest version.
I am pretty sure both severs are 2008r2.
The one I’m transferring to is Version:
If the old one is an older Version what do I do now?
All help is appreciated.
You’ll need to take the database back to a 2008 server and attach it there. Emergency mode won’t work if the database needs to be upgraded.
I know this is an ancient post but I wanted to say thanks a bunch — this really came in handy. And now we have a solid backup strategy as a result. You really helped me dodge a bullet. Much appreciated.
Glad to have helped!
I help me please step you indicate doing after you:
ALTER DATABASE [base] SET EMERGENCY;
ALTER DATABASE [base] SET SINGLE_USER;
DBCC CHECKDB (base’, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
I get the following message
Msg 922, Level 14, State 1, Line 1
The database ‘base’ is recovering. Waiting until recovery is finished.
is URGENT! PLEASE!
Not the right way to get urgent help – luckily I happened to be doing email when this comment came in.
Try setting the database online again. If it doesn’t work, you need to go back into emergency mode and extract everything into a new database, or restore from your backups.
I know, sorry … I’ll try again … thanks
Paul, I don’t have any backups of it.
Can be possible to recover a database and get it back to online again?
My current database status (using DATABASEPROPERTYEX) is EMERGENCY, but if I check using DBCC CHECKDB the information of database is raising error code 922.
“Msg 922, Level 14, State 1, Line 1
The database is recovering. Waiting until recovery is finished.”
Please, need your help..
Try setting it offline and then online again. If EMERGENCY-mode repair doesn’t work after that, you’ve lost the database.
Mate thanks that just saved my day and it works on SQL2012 to
Awesome, worked great on a development VM running SQL 2005 where two databases were suspect and I detached them.
Informative, useful post
Thank you! Worked perfectly!
This worked nicely on a database where the power cut out while in the midst of configuring it for a go live in 3 days. This had left the database “suspect”. Thanks!
Thanks yet again. I have been using this for a while now with a few clients.
This time my client lost their SAN log drives leaving us with databases having no log files …
After hacking them up for a while (100 gig sizes) we prevailed with minimal data loss.
Thanks a lot sir..!
Really Great.. it was a real life saver for me.. My db logfile got deleted and this really helped me. Thanks again for an excellent hack :)
Congratulations ! You are a life saver !
Hi…im trying to use this blog to resolve the same problem im having but unfortunately the database that having the “The header for file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DemoSuspect_log.ldf’ is not a valid database file header. The FILE SIZE property is incorrect” error message is msdb. so when got to creating a dummy msdb and taking it offline, i got the msg “Option ‘OFFLINE’ cannot be set in database ‘msdb’.” Please how can u help me?
Msdb is more complicated and beyond the scope of a comment reply. I recommend you restore from your msdb backups or just recreate msdb from the install media (see http://msdn.microsoft.com/en-us/library/dd207003.aspx) and then restore backups of the other system databases. Make sure you practice and know what you’re doing before you do this for real.
Thanks man! Worked like a charm!
Thanks really nice article. What happens if the database was involved in replication either publisher or subscriber?
Than you’ll need to reinitialize the replication topology.
Great thanks Paul!
And Happy New year!
Recently days, we encounter the same issue. We did everything like this post blog says.
1.create new database and replace mdf & log.
2.take into emergency model & single user (even if exception throws output, but we could do that successfully)
3.but when we use checkdb, we failed, failed at the last step.
DBCC CHECKDB (N’xxxx’, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
Msg 945, Level 14, State 2, Line 1
Database ‘xxxx’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Are you sure all the data files are there?
I don’t have any backups. Database is in Emergency mode. and I have only 25GB in drive. Transaction log files are full. DBCC is not working because of space issue. What is your suggestion? How can I delete the transaction Log file when the database in emergency mode. Thank you for your help.
You shouldn’t do that. Create more space on the drive.
Standing the tests of time.
We had a corrupt Tran Log here at the office for one of our Production systems.
I followed these steps and we are back.
Thank you! :)
This method saved a database that was in a server turned off and system drive formatted
Thanks a lot
save my database with inexistent log file and database corrupt!!
e have database when trying to make read only throwing below error: with stack dump
Expression: m_recoveryUnit->IsIntendedUpdateable ()
Process ID: 6448
Msg 926, Level 14, State 1, Line 1
Database ‘XXXX’ cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server errorlog for more information.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 3313, Level 21, State 2, Line 1
During redoing of a logged operation in database ‘XXXX’, an error occurred at log record ID (0:0:0). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 3414, Level 21, State 1, Line 1
An error occurred during recovery, preventing the database ‘XXXX’ (database ID 7) 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.
– DBcc checkdb shown Clean
– database is online and able to access
-Detached database and attached with rebuild log, still could not bring database read_only
SQL version : SQL server 2008 SP4.
Emergency mode repair or restore from backups.
Thank you Paul
Hi Paul – I have taken backup of same databases and restored in SQL 2012 instance, no i am able to make database read_only, offline\online without any issues. Trying to understand why is this behaviour
Thank you very much for this post. It helped one of my customers again (I have used it some times over the years).
Really helpful, and if you ever come to Denmark (or if I ever again participate in some of your brilliant courses), then I owe you a beer.
Thank you for the great info!
Hi Paul, I’m facing with a statement that I found when I was looking for how can I repair a suspect database:
“DBCC CHECKDB (databaseName, repair)”
I can’t identify which repair level is it, because in the msdn’s article about “dbcc checkdb” only talks about three repair parameters: REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD. And, I don’t find any reference about use “repair” alone.
Thanks Paul, your article was very helpful!
There is no such option. As my post shows, you have to use REPAIR_ALLOW_DATA_LOSS.
Does anyone have any time frames for the completion of the DBCC?
I have put my DB into emergency mode and then run;
ALTER DATABASE [ReportServer$SQLEXPRESS] SET SINGLE_USER;
DBCC CHECKDB (N’ReportServer$SQLEXPRESS’, REPAIR_ALLOW_DATA_LOSS)
WITH NO_INFOMSGS, ALL_ERRORMSGS;
This has been executing for nearly 2 hours now….
It takes as long as it takes – I can estimate if I know your schema and hardware but only way to know for sure is to run it regularly.
This just saved my life!!!! Nearly lost 2 years of work when a power cut shut down my PC halfway through an executable on a table containing over 10million rows of data….
4hour recovery but all as was.
Thanks Paul, you are a genius
Cool – you’re welcome!
first thanks for such a good article :)
how about this solution when we have problem with log file?
ALTER DATABASE AdventureWorksDW2012 REBUILD LOG ON (‘adress’)
I don’t like recommending that as it doesn’t even attempt to recover transactions from the log – just throws it away completely, and doesn’t do any consistency checks. People may run that and then move on, without realizing that their database is corrupt.
Thanks alot, this was very helpful
Dear Mr. Randal,
Thanks again for a great article. A co-worker had moved some files to new drives and this is what I received a call for. I followed what you have discussed above and everything is working great. Since SQL was shut down before the files were moved I wasn’t too worried about data corruption. I was able to bring everything back online and using the new drives, thanks to you!!!
You’re welcome – glad I was able to help!
I have same problem in Sql 2000 database file.
I rebuild Log File By this code:
exec sp_configure ‘allow updates’, 1
reconfigure with override
UPDATE master.dbo.sysdatabases SET Status = -32768 WHERE [Name] = ‘HIS’
dbcc rebuild_log (‘HIS’, ‘G:\TVR\DATA\OLD\HIS_Log.ldf’)
it’s worked and Log file rebuild successfully.
Then I run “DBCC CheckDB(‘HIS’)” and get some error.
When I run “dbcc checkdb(‘HIS’,repair_allow_data_loss)” I get this error:
“Server: Msg 3908, Level 16, State 1, Line 1
Could not run BEGIN TRANSACTION in database ‘HIS’ because the database is in bypass recovery mode.”
Is there any way to recover my database?
Try resetting the database status again.
THANK YOU. You saved my A@#
Almost 10 years old and still relevant today :) Thank you Paul!
PS. You see how far the repair is by running SP_WHOISACTIVE from Adam Mechanic (http://whoisactive.com/)
The Sharepoint team changed the default protocol to TLS 1.2 on the servers. After that reboot of the servers many of the databases have been in suspect mode. I have tried to bring them back to online mode using the same below code.
(ALTER DATABASE [DemoSuspect] SET EMERGENCY;
ALTER DATABASE [DemoSuspect] SET SINGLE_USER;
DBCC CHECKDB (N’DemoSuspect’, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
But the databases keep coming back to the suspect mode. Is there any way to for my databases to not go back that way?
There’s some corruption that’s preventing the databases from recovering correctly. Without more in-depth information, it’s impossible to say. If you’d like some consulting assistance, please email email@example.com or you can contact Microsoft Product Support for help too. Thanks
It worked! Thank you for this!
Can you run a recovery with a dummy log file during RESTORE operation. One of our non-prod database turn into a SUSPECT mode while running a RECOVERY during RESTORE operation.
RESTORE WITH NORECOVERY runs fine but when we run the final step of RECOVERY it chokes and run into a SUSPECT mode. Does that mean that log file was already corrupted during backup?
we are not in a emergency situation but would be nice know if database can be recovered with a dummy log file during RESTORE similar to detach-attach.
No, you can’t do that. And no, it’s more likely that something in a data file was corrupted that prevented rolling back a transaction as part of the final recovery phase of the restore – that will always make the database suspect. You can try putting the database into EMERGENCY mode, otherwise you’ll need to experiment with which log backup causes the failure and you’ll only be able to restore to there. One other thing to try is using WITH_CONTINUE_AFTER_ERROR on the final recovery phase, and then you’ll need to perform and emergency-mode repair.
Thanks Paul, WITH_CONTINUE_AFTER_ERROR helped me to move to a next step.
But next step failed probably because we use In-Memory Objects :
Msg 41316, Level 16, State 0, Line 43
Restore operation failed for database ‘errored_out’ with internal error code ‘0x82000008’.
Msg 41836, Level 16, State 1, Line 44
Rebuilding log is not supported for databases containing files belonging to MEMORY_OPTIMIZED_DATA filegroup.
Msg 7909, Level 20, State 1, Line 44
The emergency-mode repair failed.You must restore from backup.
Ah – you’re stuck I’m afraid. There’s no workaround if you have in-mem objects. You’ll need to go back to just before the log backup that causes the database to go suspect.
Thanks Paul for Prompt replies. Fortunately, it was a non-prod instance and we have had some good backups too.
But today i learned an edge case where detach-attach wins over backup-restore. Always something to learn :)
Have a good one.
Some interesting turn in this corruption story. I still had that corrupt backup and was trying to learn something out of it in my free time.
Surprisingly while trying different RESTORE options I found that I could restore the corrupt database on a machine where we had latest patch installed ( SQL Server 2016 SP2 + CU1). Corruption error persist on SP1 + CU6 though.
Do you think latest patch handled some edge case our backup encountered and it was not a real corruption?
hello,paul! I want to ask you a question? what’s the different that the dbcc checkdb will do in the following two situation?
1. set the db into emergency mode;
set the db into single_user;
2. set the db into single_user(from online);
does there anything different? or just because if the db is suspect or recovery pending,it must be set emergency mode first?
@Jon S: Certainly sounds like it!
Just another software anomaly. I am scared to see self driving cars on free way!!
Holy crap. I can’t believe that worked. I think we just became best friends! Thanks Paul!
Thanks for a great post. A client wanted to monitor the state_desc column in sys.databases for a value of SUSPECT. Overwriting the start of the log file returns SUSPECT from SELECT DATABASEPROPERTYEX (N’DemoSuspect’, N’STATUS’) AS N’Status’; but sys.databases shows RECOVERY_PENDING. By overwriting the middle of the log file, I was able to get sys.databases and sys.databases to reflect SUSPECT.
Wanted to share in case anyone else had the same issue. Once again, thanks for this post and all of the work you do to share your knowledge.
We have an interesting one:
What if the database is in SUSPECT mode but also a principal in a database mirror-relationship?
We can’t use SET EMERGENCY because of the mirror, but we also can’t disconnect the mirror because of the SUSPECT mode.
This is a restore test so it’s not very urgent but we would like to know how to solve this.
SQL-version used is 2016 standard
I haven’t tried that scenario, but my guess is you’ll need to force a failover to the mirror, break mirroring, and then reestablish the mirroring relationship from a backup from the new primary.