How to recover a Database SQL server to Normal from Suspect status
Troubleshooting
Resetting the Suspect Status
Microsoft® SQL Server™ 2000 returns error 1105 and sets the status column of sysdatabases to suspect if SQL Server is unable to complete recovery on a database because the disk drive no longer has any free space. Follow these steps to resolve the problem:
Execute sp_resetstatus.
Use ALTER DATABASE to add a data file or log file to the database.
Stop and restart SQL Server.
With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database.
Free disk space and rerun recovery.
sp_resetstatus turns off the suspect flag on a database, but leaves all other database options intact.
Caution Use sp_resetstatus only when directed by your primary support provider or as recommended in Troubleshooting. Otherwise, you might damage your database.
Because this procedure modifies the system tables, the system administrator must enable updates to the system tables before creating this procedure. To enable updates, use this procedure:
USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
After the procedure is created, immediately disable updates to the system tables:
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO
sp_resetstatus can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure.
The syntax is:
sp_resetstatus database_name
This example turns off the suspect flag on the PRODUCTION database.
sp_resetstatus PRODUCTION
Here is the result set:
Database 'PRODUCTION' status reset!
WARNING: You must reboot SQL Server prior to accessing this database!
Resetting the Suspect Status
Microsoft® SQL Server™ 2000 returns error 1105 and sets the status column of sysdatabases to suspect if SQL Server is unable to complete recovery on a database because the disk drive no longer has any free space. Follow these steps to resolve the problem:
Execute sp_resetstatus.
Use ALTER DATABASE to add a data file or log file to the database.
Stop and restart SQL Server.
With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database.
Free disk space and rerun recovery.
sp_resetstatus turns off the suspect flag on a database, but leaves all other database options intact.
Caution Use sp_resetstatus only when directed by your primary support provider or as recommended in Troubleshooting. Otherwise, you might damage your database.
Because this procedure modifies the system tables, the system administrator must enable updates to the system tables before creating this procedure. To enable updates, use this procedure:
USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
After the procedure is created, immediately disable updates to the system tables:
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO
sp_resetstatus can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure.
The syntax is:
sp_resetstatus database_name
This example turns off the suspect flag on the PRODUCTION database.
sp_resetstatus PRODUCTION
Here is the result set:
Database 'PRODUCTION' status reset!
WARNING: You must reboot SQL Server prior to accessing this database!
Comments
Post a Comment