Suspect Database - Emergency!

Suspect Database - Help! this morning I came into work, voicemail light vigorously flashing, post-it notes everywhere asking me to call people - no one was able to access any data. When I checked the server, I noticed that i had a suspect database - the databases were marked as 'SUSPECT'. Tables had gone, views had gone, users had gone � everything had gone!I�ve rebooted the server (twice!), all the services seem to be running, I checked the event viewer and did not see anything wrong there. I have 200 rather irate and frustrated users out there - What can I do to escape the wrath??

This sound familiar? If not, read on anyway because prevention is better than cure.

When researching for this article, I typed in �SQL Server Database Marked as Suspect� into Google, I had back 117,000 hits, with Yahoo I had over 1 and a half million hits! And I�m sure the web forums are filled with queries on how to fix a �suspect� database � must be a common issue then?

Another one of my disclaimers here, I am trying to write this as simple as possible, whilst working from past experiences etc. If there is something I have missed, technically, please do not hesitate to contact us

So what is a �Suspect� database?

Whenever SQL Server can not open any file required for the proper functioning of the database, it mark that database "Suspect". You have a suspect databaseWell despite the apparent abundance of material on the issue of suspect database, in my experience encountering a suspect database is a rare occurrence. It can happen, however, most often due to faulty hardware or operational mistakes, like inadvertently deleting the .ldf file (transaction log file)).

A database can become a suspect database for different reasons, the more common ones are as follows:-

  • Transaction Log growing and running out of available space
  • A database resource is being held by the operating system. Third party backup software (e.g. BackupExec) or defrag software. We�ve even had virus scanners cause this once.
  • As mentioned, a database or log file could be missing for some reason.
SQL Server will prevent you from getting into the database because it doesn't know what state the data in the database is in.

So what do you do if you have suspect database?

Try the old favourite before reading the rest of this article � stop & restart the SQL Server services � you never know it might work and save a lot of time and hassle.

If that didn�t work, you need to find out why this happened. OK it might be something obvious as the example given in www.gre-sqlserver-solutions.com/Backup.html - where multiple power failure was the cause. (Where was the UPS to allow a graceful shutdown!)

If its reason (a) from above - your transaction log has grown rapidly and hence consumed all the disk space on your drive (very common in my workplace!)Your database will be marked suspect (suspect database) by SQL Server, as it can no longer expand the transaction log file to record operations.

This can be caused by any of the following:-

  • Uncommitted Transactions
  • Un-replicated Transactions
  • Transactions that are simply too large to be handled
  • Certain operations such as DBCC DBREINDEX and CREATE INDEX
  • While restoring from transaction log backups
  • Client Applications are not able to process all results

The above problems can be easily resolved by simply truncating the inactive portions of your transaction log file or shrinking the actual transaction log file. Its not good practice to shrinking of your data and log files but it is very good practice to monitor their growth.

If the reason is "Un-replicated Transactions" from above - A database file might have been in use by, for instance, an anti-virus program and restarting SQL Server might be all that is needed - but you still want to read logs etc. to find out what happened.

Check eventlogs and do HW diagnostics etc.; search Books Online and KB for those errors. You don't want this to happen again!

But what if its reason (c) from above � missing or corrupt datafile

Firstly let�s hope you have backups!? Having a good backup strategy (as described in www.gre-sqlserver-solutions.com/Backup.html is key. It allows you in most instances to simply perform recovery from corruption and hardware failure. Obviously, the better the strategy (e.g. frequent transaction log backups), the less data you will risk losing.

If you can, run DBCC CHECKDB and DBCC CHECKCATALOG against the database. There might be specific recommendations for your error messages.

1. If there is a hardware problem, ensure the faulty hardware is replaced.
2. Backup the log. This assumes that log backup schedule is in place. If the database is suspect, you must use the NO_TRUNCATE option for the BACKUP command. Also, you might want to do a file backup of the mdf and ldf files, for extra safety.Restore is usually the best and only option. If you managed to perform a log backup, then you will most probably have no data loss. Which let�s face it should be your main concern here. Then you just need to restore the latest clean database backup and the subsequent log backups, including the one taken in above step.

If you have a recent backup of the database, USE IT! Forget about the rest of this article and do a normal restore procedure. Otherwise read on.

Suspect Database - sp_resetstatus

Another option could be to try to fix the suspect database using sp_resetstatus. It might help but if the database is too damaged, it might just pop back to suspect again. Carry out the following procedures:

1. Start SQL Server in single user mode. (using �m option)
2. Run sp_resetstatus with the @dbname parameter. (e.g. sp_resetstatus @dbname = "jhddemo")
3. Perform detailed DBCC checks (CHECKDB, CHECKALLOC, etc)
4. Run a few random queries to see if you experience any problems.
5. If no problems occur, stop and start SQL Server and open the database to production.

If all else fails, or dare I say it you have no adequate backup & recovery mechanism then there still may be hope. You can use Emergency Mode.In versions prior to SQL Server 2005, EMERGENCY mode wasn't documented and you had to hack the sysdatabases table to get a database into it using:-

UPDATE master..sysdatabases set status = -32768 WHERE name = 'jhddemo'

You could then BCP the required data out and place it into a different database.The use of the word �hack� in the preceding paragraph alone tells me that this option should only be used if all other avenues have been exhausted!

In 2005 though, EMERGENCY mode was documented and proper syntax added to support it. EMERGENCY mode is possibly you�re �get out of jail free card�.

Members of the sysadmin role can put the database into EMERGENCY mode using:

ALTER DATABASE jhddemo SET EMERGENCY;

Once in EMERGENCY mode, the database is accessible only by members of the sysadmin role. Note here though, EMERGENCY mode is not SINGLE_USER mode - a database needs to be in SINGLE_USER mode for repair to run and EMERGENCY mode allows multiple connections from members of the sysadmin role. You can set SINGLE_USER mode as well as EMERGENCY mode.The database is also read-only as nothing can be written to the transaction log.

Imagine the following scenario. You have an unexpected power failure; the database wasn�t cleanly shut down and the transaction log, for some reason (loss of disk for example) isn't available - so that rules out recovery! You�ll get the usual �database cannot be opened� messages.

But if you put the database into EMERGENCY mode you can access the data. The data may not be consistent but at least you can get to it and if need be �bcp� it out into another database or onto another server.

When in EMERGENCY mode, you can use DBCC CHECKDB to bring the database back online again. The only repair option allowed in EMERGENCY mode is REPAIR_ALLOW_DATA_LOSS - but beware it's a one-way operation and can't be rolled back.You could always take a copy of the database files before doing this in case something goes wrong or there are unrepairable errors.

I will re-emphasise here, you should only use this as a last resort, but if you do get yourself into trouble, you know that EMERGENCY mode should be able to help you.

And if it doesn�t? Either find a good hiding place or revamp your CV, because you should have had adequate backup and recovery strategies in the first place! (www.gre-sqlserver-solutions.com/RecoveryModels.html)Be cautious and good luck����������.

Enter your E-mail Address
Enter your First Name (optional)
Then

Don't worry -- your e-mail address is totally secure.
I promise to use it only to send you GRE Newsletter.

footer for Suspect Database page