View source for Troubleshooting: Cannot open database “AED XXX”…

<b>Question:</b> Failed to login, message: "Cannot open database AED_...."

<b>Version:</b> 1.8 / 1.9 / 2.0

[[File:Suspected db.png]]


<u><b>Answer:</b></u>

This message is prompted for a few possible reasons, two of the most likely reasons are:
# The database has not been attached to SQL Server, OR more accurately, has been detached from SQL Server.)
# 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 <span style="color:red">Microsoft SQL Server Management Studio Express</span>...(also refer to Unable to attach suspected database)


[[File:Suspected db1.png]]



The following steps are recommended to repair a Suspect database (that are recoverable).
 
#	Go to Microsoft SQL Server Management Studio Express  (Go to <span style="color:red">Start > All Programs > Microsoft SQL Server 2005 > SQL Server Management Studio Express</span>, click on <span style="color:red">Connect</span> …. )
#	Click on <span style="color:red"> New Query</span>, key in the following queries, then <span style="color:red">Execute.</span>


[[File:Suspected db2.png]]


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


3.	<span style="color:red">Clear</span> the contents of Query box, and key in the following query, follow by <span style="color:red">Execute:</span>

[[File:Suspected db3.png]]


?	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.
 
?	<span style="color:red">Query completed with errors</span>... scroll downwards to read through the message.... you may see some lines saying errors were found and fixed/repaired...
?	Click on <span style="color:red">Execute</span> again to ran the query (you may need to do this for several times) until the message showing: <span style="color:red">Query executed successfully.</span>


[[File:Suspected db4.png]]


4.	<span style="color:red">Clear</span> the contents of Query box, and key in the following query, follow by <span style="color:red">Execute</span>: 
 

[[File:Suspected db5.png]]

•	ALTER DATABASE <span style="color:red">AED_ABC</span> 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.



By:Peter@2013; P170801
{{NavigateForUser}}