The Databases - Unavailable alarm becomes active when Spotlight on SQL Server detects that a SQL Server database is not available for reading. Users attempting to access an unavailable database will receive an error message.
This alarm detects unusual database statuses, including Suspect, Offline, Recovering, Loading, Restoring, Emergency Mode, and others.
When the alarm is raised
Some of the more common unavailable statuses are listed below:
Databases can only be set offline manually, using the sp_dboption procedure (or ALTER DATABASE command under SQL Server 2000). If you have Offline databases, you should check who has set the database offline, and consider using sp_dboption or ALTER DATABASE to bring the database online again.
Databases that are marked as Loading or Restoring are currently being restored by a RESTORE DATABASE or RESTORE LOG command. The database cannot be accessed by anyone while this is the case.
This status is also assigned to databases that have been restored using the NORECOVERY option. Specifying this parameter on a RESTORE statement tells SQL Server that there are more transaction logs to be restored, and that no access to the database is permitted until they are applied.
You should check the Sessions tab on the SQL Activity drilldown for active sessions that are processing a RESTORE command (where the Last Command column contains Restore). If there are no sessions processing a RESTORE command, then the database is most likely unavailable because the last restore was done with the NORECOVERY keyword.
To remove Loading/Restoring status, you should complete the RESTORE process. This can involve either waiting for the active RESTORE command to complete, or restoring the remaining transaction logs. The last transaction log should be restored without the NORECOVERY keyword.
Databases will be Recovering (or InRecovery) for a while when SQL Server is restarted, or the database is first set online. This is the status that SQL Server uses to indicate that it is re-applying committed transactions, or removing uncommitted transactions after a SQL Server failure.
Normally, SQL Server only takes a short time to re-apply these transactions, however, if there were long-running transactions open when SQL Server ended abnormally, then it can take an extended period. The general rule is that the automatic recovery will take between 1.5 and 2 times the age of the oldest transaction (although the exact time depends on your Recovery Interval setting.) For example, if a large UPDATE statement had been running for 2 hours when SQL Server terminated unexpectedly, you can expect the automatic recovery to take between 3 and 4 hours.
Usually, the best course of action is to let SQL Server proceed with its automatic recovery. If SQL Server is stopped during its automatic recovery, the next time it starts it not only has to re-process the transactions that were in progress when the original failure occurred, but it also has to reprocess the failed automatic recovery.
In some cases, it can be beneficial to bypass the SQL Server recovery process. For example, you may not want to wait for a lengthy recovery process if you are going to drop the database as soon as it completes. See Bypassing SQL Server Recovery below for details on how to do this. Be aware that bypassing recovery can corrupt your database.
Databases can be Suspect if they fail SQL Server's automatic recovery. This is most common after a SQL Server restart when something goes wrong with the automatic recovery process. In some rare situations, a database can become Suspect without a SQL Server restart (if serious database corruption is detected by SQL Server). This can also happen when SQL Server runs out of disk space for the database files while performing recovery.
The first thing to do when you have a Suspect database is to check the SQL Server error log, and look for error messages indicating recovery failure or database corruption. This should give you an indication of what caused the problem.
Actions you can consider to correct a suspect database include:
In most cases, the correct action to take for a suspect database is to restore from your last good full database backup, and transaction logs.
If restoring is not an option for you and you cannot fix the problem using the tips above, there are a few more options you can attempt. These will not work in all cases, but sometimes they may give you enough to extract data from the corrupt database that cannot be retrieved any other way.
Note: These procedures should only be considered as a last resort. They are not supported by Microsoft or Quest, and could irretrievably corrupt your databases and data.
Using Emergency Mode: Emergency mode is a special status that can be set on an individual database that causes SQL Server to skip recovery for that one database. In some cases, this can make the corrupt database available in order to extract data that cannot be retrieved any other way.
Allow direct updates to your system tables by running the following SQL:
sp_configure 'allow updates', 1
reconfigure with override
Put your Suspect in Emergency mode:
set status = 32768
where name = 'YourDBName'
This causes SQL Server to skip the recovery of this database, and will prevent the database being made suspect. However, the database may contain partially-complete transactions, and there may be inconsistencies between data and indexes (logical and physical corruptions). You should not perform any database changes or updates when SQL Server is started this way.
Another high risk option to access a suspect database is to start SQL Server with Trace Flag 3608. This trace flag causes SQL Server to skip its automatic recovery process on ALL DATABASES when it starts. Again, this may be enough to allow you to extract data that cannot be retrieved any other way.
With both Emergency Mode and Bypassing SQL Server Recovery, you may then be able to extract your data using BCP.EXE and/or script the database to get the latest database definitions. This can then be loaded into a new database using BCP.EXE or BULK INSERT. Be aware that the extracted data may not be complete.
Emergency Mode is a special status that can be manually set for an individual database to cause SQL Server to skip recovery for that one database. See Using Emergency Mode above for more details.
Databases can be set out of Emergency Mode only by manually updating the master..sysdatabases table directly. This can be done using SQL similar to the following:
set status = status ^ 32768
where name = 'YourDBName'
Note: The database may still contain corrupt data, and a full database restore is recommended.