question

perkins avatar image
perkins asked

Sql database flagged as suspect

We run SQL on a Win2K server. The database for an OOB product began to be flagged as 'suspect' a few days ago. The first couple of times we were able to stop/start MSSQL and the database would come back up. Today it wouldn't. In an attempt to repair the problem, I detatched the db. When I attempt to re-attach, I was getting the following message error 5101: Device activation error. The physical file name 'C:\%path to db%\db_log.LDF ' may be incorrect. Then I get db Attach failed. I resolved this by creating a new DB of the same name, stopping SQL and replacing the MDF with the corrupt file. I'm now back to 'suspect' on the DB in Enterprise Manager. When I run DBCC CHECKDB ('dbname', REPAIR_REBUILD) I get the response below: Server: Msg 945, Level 14, State 2, Line 1 Database 'dbname' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. Any ideas?
db
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

More importantly, do you have a backup of the database and have you verified that it's sound?
0 Likes 0 ·
OrnSveinsson avatar image
OrnSveinsson answered
If you have no reliable backups, I would create a new database and start copying tables and any other objects as fast as you can, before it dies permanently. If the data is inaccessible then can try the tool SQL Server Recovery Toolbox. It is possible you will be able to help. [ http://www.oemailrecovery.com/sql_repair.html][1] Also try this. This probably won't work, but it forces the suspect flag off. You will first need to turn on update for systables. So run his USE Master Go EXEC sp_configure 'allow updates' , 1 Go Reconfigure Go Then run the following code: update sysdatabases set status = status & ~256 where name = 'MySuspectDatabase' If that doesn't fix it. You will want to start copying all the objects to a new db as ACPerkins has suggested. You will want to place the db in emergency mode. First so run this: update sysdatabases set status = status | -32768 where name = 'MioSuspectDatabase' Then use dts, Select into's and whatever else to get the objects from the suspect DB to a new one. Dont forget to undo the allow updates option: EXEC sp_configure 'allow updates' , 1 Go Reconfigure Go [1]: http://www.oemailrecovery.com/sql_repair.html
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
never, never detach a suspect database. Check this: - are all datafiles available? - additional messages in errorlog
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jason_clark03 avatar image
jason_clark03 answered
Check if there any log space issue and use sp_add_log_file_recover_suspect_db() to add new log file in your database. Read the guidelines here for SQL error 945 [ http://www.sqlserverlogexplorer.com/suspect-sql-server-error-926-and-945/][1] [1]: http://www.sqlserverlogexplorer.com/suspect-sql-server-error-926-and-945/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

OrnSveinsson avatar image
OrnSveinsson answered
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

perkins avatar image
perkins answered
Thank you guys for your feedback and help. Sorry, that long time did not respond. This is because the issue was solved and I forgot to tell.) Thank you!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.