Suspected Database

Top  Previous  Next

 

Question: When login to an account book, the following message prompted:

 

 

faqts41

 

 

 

Answer:

 

This message is prompted for a few possible reasons, two of the most likely reasons are:

1. The database has not been attached to SQL Server, OR more accurately, has been detached from SQL Server.) (Refer to Cannot open database.)

2. The database attached to SQL Sever has been marked as 'Suspect' by SQL Server.

Note: To determine if a database status is marked as Suspect, take a look at the name of database at Microsoft SQL Server Management Studio Express...(also refer to Unable to attach suspected database)
 

 

faqts42
 
 
 

The following steps are recommended to repair a Suspect database (that are recoverable).

 

1.Go to Microsoft SQL Server Management Studio Express  (Go to Start > All Programs > Microsoft SQL Server 2005 > SQL Server Management Studio Express, click on Connect …. )
 
2.Click on New Query, key in the following queries, then Execute.
 

 

faqts36

 

 

?EXEC sp_resetstatus ‘AED_ABC’ -- this query is to reset the 'Suspect' status, or turn off the Suspect flag on a database.
?ALTER DATABASE AED_ABC SET EMERGENCY -- this query is to allow SQL administrator to take control of the database for trouble-shooting purpose.
?ALTER DATABASE AED_ABC SET SINGLE_USER WITH ROLLBACK IMMEDIATE -- this query will set database to allow only one user at a time access to the database.

 

 
 

3.Clear the contents of Query box, and key in the following query, follow by Execute:

 

 

faqts37

 

?DBCC CHECKDB (‘databasename’, REPAIR_ALLOW_DATA_LOSS) -- this query is used to check the errors and fix/repair them.
 

Note: there are 2 more modes of repair that can be used in certain occasions that involved only minor errors or and re-indexing:

(1) DBCC CHECKDB (‘databasename’, REPAIR_FAST) -- used to perform minor, non time-consuming repair actions such as repairing extra keys in non-clustered indexes. These repairs can be done quickly and without risk of data loss.

(2) DBCC CHECKDB (‘databasename’, REPAIR_REBUILD) -- to perform all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss.
 

?Query completed with errors ... scroll downwards to read through the message.... you may see some lines saying errors were found and fixed/repaired...
?Click on Execute again to ran the query (you may need to do this for several times) until the message showing: Query executed successfully.
 

 

faqts38

 

Note:

Please take note that if the database is badly damaged, some errors may persist even after few round of checking and fixing. In such cases, the recovery process will become more complicated and unassured.
Again, my best advice, the best precaution step is to backup your database frequently.

 

 

4.Clear the contents of Query box, and key in the following query, follow by Execute:
 

 

faqts39

 

ALTER DATABASE AED_ABC SET MULTI_USER -- this query will set the database back to multi-user mode to allow connection of all users that have appropriate permission.

 

 
 

5.Now, you should be able to login the account book.

 
 

 

____________________________________________________________

Send feedback about this topic to AutoCount. peter@autocountSoft.com

© 2013 Auto Count Sdn Bhd - Peter Tan. All rights reserved.