Friday, February 17, 2012

How to Fix SQL Server Database Error 3707?

-->
Yesterday night, I had got a mail from one of my friend. He has explained about getting an SQL server error message 3707 on his SQL server database. After reading full mail, I went there and try to fix his problem as soon as possible. Here I will share all the steps that have performed to resolve SQL server error message 3707.

See what the error message was:

Cannot detach a suspect or recovery pending database
It must be repaired or dropped. (Microsoft SQL Server, Error: 3707)

Causes for the error: He had got above error message due to connectivity problem due to which MSSQL server marks his database in suspect mode. SQL server marks database in suspect mode due to several reasons which could be like connectivity problem, power outage, memory outage etc. In your case, the cause may be different.

Steps to solve the issue: I had performed below steps to recover SQL server database.

1. Set Database to Emergency: First of all, set your database in emergency mode. It will mark your database read only, limited access to sysadmin & disabled logging. You can do it with the help of below query.

ALTER Database Employee_Details SET Emergency;
GO

2. Set Database to Single-User Mode: Now change the access control of your database from multi-user to single user by this query.

ALTER Database Employee_Details Set Single_User
GO

3. Run DBCC CHECKDB on Database: After setting your database in single user mode, Run DBCC CHECKDB command with repair_allow_data_loss option. It will repair your database with minimum data loss.

DBCC CHECKDB (Employee_Details, REPAIR_ALLOW_DATA_LOSS) With ALL_ERRORMSGS;
GO
Note: This command will try to repair and recover data as much as possible but you might be loss some amount of data.

PS: I will recommend you, if you are not the boss of the database then consult your supervisor and discuss the effect of above command.

4. Set Database to Multi-User Mode: Now again change the access control of your database from single-user mode to multi-user mode by this command.

ALTER Database Employee_Details set multi_user
GO

5. Successfully done.

Share Your Opinion: You can share your experience with this error through comments.