SQL Server Database In Suspect Mode

Today a coworker has problems with SQLServer 2005, it seems a node had a crash (two-node cluster) and after a several minutes the other node get crash too. When start the instance one of the databases stay in “suspect” mode. Applying the logic as Microsoft documentation indicates we should go into “emergency” mode and launch a dbcc checkdb. The problem is that this database It is of a size of several Teras, so an estimate that checkdb may take more than ten hours, too long for a production database operating in 24×7.

There is a tape backup and also several snapshots cabin is made, this cluster using NetApp, so restore the last snapshot is question a few minutes and applied recovery from there. Finally this is the chosen option because as I say is a matter of minutes. If you haven’t this technology, provided by the cabin would have to try a DBCC with REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD options (you need set emergency mode), but these options are not recommended since it may lose data.

As a final option would have to go to the last backup, remember that databases should always be in full recovery mode, and apply corresponding logs.
Possible causes that produce a database get suspect

The database could have become corrupted.
There is not enough space available for the SQL Server to recover the database during startup.
The database cannot be opened due to inaccessible files or insufficient memory or disk space.
The database files are being held by operating system, third party backup software etc.
There was an unexpected SQL Server Shutdown, power failure or a hardware failure.
Please check all these items befores start a restore/recover database.

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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