SQL Server Performance Forum – Threads Archive

Databse (suspect)

hi, i accidentally rename the data and log files of a particular database.
when i open enterprise manager the database is marked as SUSPECT. i again rename those files to their original names… but the suspect flag is not removed.
and i am unable to access the database objects i tried these commands too….but it won’t work.. USE master sp_configure ‘allow updates’, 1
RECONFIGURE WITH OVERRIDE sp_resetstatus ‘kranthi’ sp_configure ‘allow updates’, 0
RECONFIGURE WITH OVERRIDE
Is there any way to bring the databse back to original state without restoring it from backup’s? krishna chaitanya.s
project engineer-SQLDBA

You need to reboot the SQL Server in order to take the settings.
BOL specifiesResetting 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. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

hi satya i stopped the sql server services and restart the services… the suspect flag is not there.
now i am able to access the database objects,,, thanx a lot krishna chaitanya.s
project engineer-SQLDBA

BOL is your good friend mate. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>