Database Trouble Shooting, how to recover suspect database in SQL
One of Basic knowladge to understand and doing trouble shooting in SQL Server platform is understanding Database Status, each status is store on master.dbo.sysdatabases object. and the database status in SQL Server defined as
| State |
Definition |
| ONLINE |
Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed. |
| OFFLINE |
Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed. |
| RESTORING |
One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable. |
| RECOVERING |
Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable. |
| RECOVERY PENDING |
SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed. |
| SUSPECT |
At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem. |
| EMERGENCY |
User has changed the database and set the status to EMERGENCY. The database is in single-user mode and may be repaired or restored. The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect can be set to the EMERGENCY state. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state. |
When the database status is suspected, it might be from trouble of Infra structure or hardware that impacted to SQL Server database performance.
Here are some step by step to recover database that have been suspended by the system
1. Look in the SQL Server Logs in Enterprise Manager(EM), There should be multiple logs starting with Current and then followed by 6 or more archived logs.
2. Review all recent Error Log(s). There WILL be an indication here as to why the database has been marked suspect.
3. When the problem has been fixed and you're either sure that the data is going to be ok, or you have no backup anyway, so you've nothing to lose, then change the database status to normal and restart SQL Server.
4. To change the database status we will us the following store procedure: sp_resetstatus.
The steps are as follows:
5. Change database status to allow update
USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_resetstatus
GO
-- Example: sp_resetstatus ‘Washington’
6. After the procedure is run, immediately disable updates to the system tables:
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO
If database is rolling back to suspect than you should change db status to emergency mode, you have to extract all data out from database using BULK Copy or DTS, Note that the data may be corrupt or transactionally inconsistent
1. Issue the following command to put the database into emergency mode:
a. USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
b. UPDATE master..sysdatabases
SET status = 32768
WHERE name = 'DatabaseName'
2. Stop and Restart ALL SQL Server Services.
We are now ready to pull whatever data we can out of the tables in the corrupt database. Remember, some tables may be corrupt, thus you may have to play with various T-SQL statements to get the data out. First try DTS