—>
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.
Hi all
We have a strange problem lately where all data on 3 HDD on our 2 server (Win 2008 Ent 64bit & Win 2008 std 32bit) has been deleted.
it is funny that we were working normally and after a problem occur at our first site, and after 15 min the problem occur on our second site. When I came to the company, I notice that 2 HDD on the first server were totally data deleted, and 1 HDD on thr second server were totally deleted. (The partition is there but not even single folder exist)
I managed to get the hdd recover on the server which run Win 2008 Ent and still wating the recovery to finish on the second server.
I have many problems, so bare with me if I ask to much question.
-I had Drive C & D on Win 2008 Ent server. C is for OS and D for data, Drive C is safe. D is totallt wiped.
-I recover the MSSQL 2008 database and put them in there original folder D:\SQL_DATA\
-I open the SQL server management studio (SSMS) and I notice that the DB are appearing in the SSMS but with no «+» sign on the left side, so I cannot open the database.
-I Open [msdb].[dbo].[backupfile] and check the path of each DB for MDF and LDF and they are correct.
-I tried to deattach the DB it gives error Cannot detach a suspect or recovery pending database. It must be repaired or dropped. (Microsoft SQL Server, Error: 3707)
— I tried to delete the DB (success for DB that has no replication, falied for DB with replication)
is there command to make the DB work again without losing the replication setting?
Thank you in advance.
when i am trying to create a table like below
CREATE SET TABLE pp_scratch.HADOOP_FPTI_DASHBOARD ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
id decimal(10,0) NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, MAXVALUE 999999999999, NO CYCLE)
job_status VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
current_processing_hr VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
no_of_files_moved VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
PRIMARY INDEX id;
CREATE TABLE Failed. [3707] Syntax error, expected something like a
name or a Unicode delimited identifier or a ‘CYCLE’ keyword between an
integer and ‘,’.
could you please help what went wrong.
Thanks in advance
Gordon Linoff
1.2m58 gold badges647 silver badges791 bronze badges
asked Mar 30, 2016 at 23:51
There are no commas between the Identity options:
CREATE SET TABLE pp_scratch.HADOOP_FPTI_DASHBOARD ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
id decimal(10,0) NOT NULL
GENERATED ALWAYS AS IDENTITY (START WITH 1
INCREMENT BY 1
MAXVALUE 999999999999
NO CYCLE),
job_status VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
current_processing_hr VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
no_of_files_moved VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
PRIMARY INDEX id;
answered Mar 31, 2016 at 8:34
dnoethdnoeth
59.6k4 gold badges40 silver badges57 bronze badges
The line defining id is missing a comma at the end:
id decimal(10,0) NOT NULL
GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, MAXVALUE 999999999999, NO CYCLE),
-----------------------------------------------------------------------------------------------------^
answered Mar 31, 2016 at 0:04
Gordon LinoffGordon Linoff
1.2m58 gold badges647 silver badges791 bronze badges
1
Loading…
Error message was:
Cannot detach a suspect or recovery pending database
It must be repaired or dropped. (Microsoft SQL Server, Error: 3707)
Ref URL: http://sql-server-recovery.blogspot.in/2012/02/how-to-fix-sql-server-database-error.html
This entry was posted in SQL. Bookmark the permalink.