March 21, 2014 at 5:40 am
My sql databases in SQL Server 2014 has the status "suspend" as I saw in SQL Management Studio. I can't restore to serviceable condition sql databases through standard procedures. I need to restore .mdf file.
March 21, 2014 at 5:55 am
1)
USE master
GO
SELECT NAME,STATE_DESC FROM SYS.DATABASES
WHERE STATE_DESC='SUSPECT'
GO
2)
USE master
GO
ALTER DATABASE database_name SET EMERGENCY
GO
3)
DBCC CHECKDB (database_name)
GO
4)
ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
5)
DBCC CHECKDB (database_name, REPAIR_ALLOW_DATA_LOSS)
Please check for error and use appropriate option.
6)
ALTER DATABASE database_name SET MULTI_USER
March 21, 2014 at 6:59 am
If above solution won't work. Use latest good backup to restore the database. Make sure in that case you might have data loss. Hope this is not your production server. Thus you can recover .MDF
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 21, 2014 at 9:07 am
Sushant Yadav (3/21/2014)
1)USE master
GO
SELECT NAME,STATE_DESC FROM SYS.DATABASES
WHERE STATE_DESC='SUSPECT'
GO
2)
USE master
GO
ALTER DATABASE database_name SET EMERGENCY
GO
3)
DBCC CHECKDB (database_name)
GO
4)
ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
5)
DBCC CHECKDB (database_name, REPAIR_ALLOW_DATA_LOSS)
Please check for error and use appropriate option.
6)
ALTER DATABASE database_name SET MULTI_USER
I would be EXTREMELY hesitant to suggest running this. Firing off a CHECKDB with the ALLOW_DATA_LOSS option WILL torch data in your database if it has to. Maybe a little, maybe a lot, maybe the whole darn thing (though doubtful), who knows?
The first thing to do is a DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS. Post the results of that here; it may be that this can be fixed more gracefully. And yes, if you have backups (please, have backups!), have them ready just in case.
- 😀
March 21, 2014 at 9:54 am
Sushant Yadav (3/21/2014)
1)5)
DBCC CHECKDB (database_name, REPAIR_ALLOW_DATA_LOSS)
Please check for error and use appropriate option.
Don't do this. Whatever else you do, do not follow this advice. Repairing with no idea what's wrong is irresponsible and foolish. We may end up repairing eventually, but it is not the automatic thing to do.
Post the error logs
Put the DB into emergency mode and run CheckDB with no_infomsgs and all_errormsgs options and post the output.
And locate your backups, you'll probably need them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 21, 2014 at 10:19 am
Backing up Gail, piling on and reinforcing this one.
REPAIR_ALLOW_DATA_LOSS is extremely dangerous. It will attempt to fix the database without any regard to your data at all. You could lose a single page worth, you could lose the entire thing. This is a very dangerous choice and should only be done after all other options have been exhausted.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 12, 2014 at 8:32 am
enrique.mallon (7/12/2014)
Hi tommySuch condition arises when SQL Server database becomes deadlock and you are unable to perform action or transaction on it. It means that your SQL database got trapped in Suspect mode and for this i think this tutorial may be relevant to you by which you can solve this issue. So must go through this :- ...
Nope, don't agree. You don't run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS without knowing what is wrong.
July 12, 2014 at 5:21 pm
enrique.mallon (7/12/2014)
Hi tommySuch condition arises when SQL Server database becomes deadlock and you are unable to perform action or transaction on it. It means that your SQL database got trapped in Suspect mode and for this i think this tutorial may be relevant to you by which you can solve this issue.
I don't agree either. This is a very dangerous approach and can lead to severe data loss. Go to your backups and restore in this situation. If you don't have backups, well, now is a really good time to start.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 13, 2014 at 6:29 am
Could you two please remove the link from the spam post that you quoted?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2014 at 7:21 am
I don't agree that it's spam, but you're right, no sense in reinforcing any kind of clicks to bad info like that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 13, 2014 at 9:18 am
Grant Fritchey (7/13/2014)
I don't agree that it's spam...
Accounts that don't post anything else, no useful information, often patently incorrect info as to causes (deadlocks don't cause suspect databases) and links to database recovery tools even when they wouldn't be useful (eg root cause was deleted data file or incorrect permissions), many times on months or years old posts.
Can we call them "Posts with negative content value and an advertising link"?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply