October 9, 2009 at 4:21 am
Hi All
One of my Critical Database went to Suspect mode due to unavailablility of a SAN Drive.The Drive is up now but not the database.Should i need to give exec sp_resetstatus 'dbname' and restart the Services or the Db will come up if i just restart the Services.
Thanks in Advance
October 9, 2009 at 5:18 am
Firstly, have you run CHECKDB on the data base...
ALTER DATABASE [DBNAME] SET EMERGENCY;
GO
ALTER DATABASE [DBNAME] SET SINGLE_USER;
GO
DBCC CHECKDB ([DBNAME], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
Then check the status
SELECT state_desc FROM sys.databases WHERE name='[DBNAME]';
GO
If all is ok
ALTER DATABASE [DBNAME] SET MULTI_USER;
GO
October 9, 2009 at 5:30 am
sql_lock (10/9/2009)
Firstly, have you run CHECKDB on the data base...ALTER DATABASE [DBNAME] SET EMERGENCY;
GO
ALTER DATABASE [DBNAME] SET SINGLE_USER;
GO
DBCC CHECKDB ([DBNAME], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
Then check the status
SELECT state_desc FROM sys.databases WHERE name='[DBNAME]';
GO
If all is ok
ALTER DATABASE [DBNAME] SET MULTI_USER;
GO
Why would you automatically run REPAIR_ALLOW_DATA_LOSS, check what dbcc checkdb returns first. using any of the repair options is last resort until you see what the problem is.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 9, 2009 at 9:08 am
sudarram (10/9/2009)
Hi AllOne of my Critical Database went to Suspect mode due to unavailablility of a SAN Drive.The Drive is up now but not the database.Should i need to give exec sp_resetstatus 'dbname' and restart the Services or the Db will come up if i just restart the Services.
Please locate the SQL error log, find all error messages relating to this DB and post them here.
Do not do anything else. Do not detach the database. Do not run repair. The messages in the error log will indicate what needs to be done.
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
October 9, 2009 at 9:12 am
Silverfox (10/9/2009)
Why would you automatically run REPAIR_ALLOW_DATA_LOSS, check what dbcc checkdb returns first. using any of the repair options is last resort until you see what the problem is.
If a database is suspect (and taken into emergency mode) the only repair option allowed for CheckDB is repair_allow_data_loss, regardless of what CheckDB returns.
Just hoping that an emergency mode repair is not required. Shouldn't be, that's usually when there's actual corruption or a damaged/missing transaction log, not just a drive that vanished for a brief period.
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
October 9, 2009 at 9:57 am
-> As Gail said check the error logs.
-> Dont run check db now .
-> If you want do a sp_resetstatus and then dbcc dbrecover .
-> You will get the error infornt of you whether its the data or log file which is corrupt .
-> You might also have your database corrupt due to file handle taken by other process , OS error 2 ,3 or 5 ..So checking errorlogs is important .
-> In case your log file is gone then rebuild it
-> If your data file is gone then run checkdb to see what all are the objects that are corrupt.
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply