sqlskills-logo-2015-white.png

Creating, detaching, re-attaching, and fixing a SUSPECT database

(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?

Summary

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”

  1. 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.

    Reply

  3. 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?

    Thanks,

    Rob

    Reply

  4. 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.

    Thanks

    Reply

  5. hi paul,

    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.
    Thanks

    Reply

  7. When I get to the step where:

    ALTER DATABASE DemoSuspect SET EMERGENCY;
    GO
    ALTER DATABASE DemoSuspect SET SINGLE_USER;
    GO
    DBCC CHECKDB (DemoSuspect, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
    GO

    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?

    Reply

  8. 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.

    Reply

  11. 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.

    The Solution
    /*

    Attach database with wizard organization_MSCRM

    */

    ALTER DATABASE [organization_MSCRM] SET EMERGENCY;
    GO
    ALTER DATABASE [organization_MSCRM] SET SINGLE_USER;
    GO
    DBCC CHECKDB (N’organization_MSCRM’, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
    GO

    — IF every think ok, execute this:
    ALTER DATABASE [organization_MSCRM] SET MULTI_USER;
    go

    Reply

  13. 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?

    Reply

    1. 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.

      Reply

  16. 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?

    Thank you,
    Amit

    Reply

  17. Paul,

    A life saver although I only had to recreate the databases & replace the files with the suspect ones & all was well.

    Many thanks.

    Reply

  18. 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 :)

    Reply

  19. 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.

    Reply

  22. Hi,

    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.

    Thanks,
    Dan

    Reply

    1. 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.

      Reply

  23. 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.

    -DH

    Reply

  24. 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 .

    Reply

    1. 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.

      Reply

    1. 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.

      Reply

  26. Great post.
    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.

    Background info.
    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.
    Geir

    Reply

    1. 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:
      10.50.1617.0

      If the old one is an older Version what do I do now?

      All help is appreciated.
      Geir

      Reply

  27. 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.
    -David

    Reply

  28. I help me please step you indicate doing after you:
    ALTER DATABASE [base] SET EMERGENCY;
    GO
    ALTER DATABASE [base] SET SINGLE_USER;
    GO
    DBCC CHECKDB (base’, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
    GO
    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!

    Reply

    1. 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.

      Reply

      2. 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..

        Thanks.

        Reply

  30. Awesome, worked great on a development VM running SQL 2005 where two databases were suspect and I detached them.

    Reply

  33. 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!

    Reply

  34. 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.

    Sweet !!

    Thanks!

    Craig

    Reply

  36. 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 :)

    Reply

  38. 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?

    Reply

  40. Hi Paul

    Thanks really nice article. What happens if the database was involved in replication either publisher or subscriber?

    Thanks

    Reply

  42. 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;
    GO

    /*
    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.
    */

    Reply

  43. Hi Paul,
    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.

    Reply

  44. Standing the tests of time.
    Thank you!!!!

    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! :)

    Reply

  45. This method saved a database that was in a server turned off and system drive formatted

    Thanks a lot

    Reply

  47. e have database when trying to make read only throwing below error: with stack dump

    Location: recovery.cpp:4517

    Expression: m_recoveryUnit->IsIntendedUpdateable ()

    SPID: 51

    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.

    Investigation DONE:

    – 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.

    Reply

  48. 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

    Reply

  49. Hi Paul,
    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.

    thanks…

    Reply

  51. 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!

    Reply

  52. 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;
    GO
    DBCC CHECKDB (N’ReportServer$SQLEXPRESS’, REPAIR_ALLOW_DATA_LOSS)
    WITH NO_INFOMSGS, ALL_ERRORMSGS;
    GO

    This has been executing for nearly 2 hours now….

    Reply

  53. 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

    Reply

  54. Dear Paul
    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’)

    Reply

    1. 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.

      Reply

  56. 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!!!

    Thanks again,

    Mat

    Reply

  57. Dear Paul

    I have same problem in Sql 2000 database file.
    I rebuild Log File By this code:

    use master
    go
    exec sp_configure ‘allow updates’, 1
    go
    reconfigure with override
    go
    UPDATE master.dbo.sysdatabases SET Status = -32768 WHERE [Name] = ‘HIS’
    GO
    dbcc rebuild_log (‘HIS’, ‘G:\TVR\DATA\OLD\HIS_Log.ldf’)
    go

    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?

    Reply

  60. Hi,

    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;
    GO
    ALTER DATABASE [DemoSuspect] SET SINGLE_USER;
    GO
    DBCC CHECKDB (N’DemoSuspect’, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
    GO)

    But the databases keep coming back to the suspect mode. Is there any way to for my databases to not go back that way?

    Thanks,
    Allan.

    Reply

    1. 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 request@sqlskills.com or you can contact Microsoft Product Support for help too. Thanks

      Reply

  62. 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.

    Reply

    1. 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.

      Reply

      1. 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.

        Reply

        1. 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.

          Reply

          1. 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.

          2. Hi Paul,

            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?

  63. 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;
    dbcc checkdb(db,repair_allow_data_loss)

    2. set the db into single_user(from online);
    dbcc checkdb(db,repair_allow_data_loss);

    does there anything different? or just because if the db is suspect or recovery pending,it must be set emergency mode first?

    Reply

  66. 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.

    Reply

  67. 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

    Any thoughts?

    Reply

    1. 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.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course