June 28, 2008 at 10:10 am
I have my development server where in I see some 20 odd databases out of the 50 which are marked in Suspect mode. For the last two days the full backup and transaction Log has been failing. Even though there is a least priority for this server in our environment i would like to sort this issue out. There was not sufficient space in the disk for the backups, which I have provided now, but not able to pin point the cause of the DB's going to Suspect mode. I have the backup (native) that is scheduled through a T-Sql script which includes all the databases. How can I recover the databases to normal mode. Any help will be highly appreciated !!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
June 28, 2008 at 1:12 pm
check the errorlog to see if it gives any clues as to why they are in suspect mode, perhaps the database files are missing?
if they are marked suspect/offline just use alter database set online command to bring them back.
otherwise best method to get them out of suspect is to restore from a backup.
---------------------------------------------------------------------
June 28, 2008 at 2:17 pm
Often there's some OS error, maybe file not available or something like that, which causes suspect mode. The error log often pinpoints this, so post the error, as suggested above, and we can help.
June 29, 2008 at 8:32 pm
Ok, off all the errors listed in the error log, all I could pin point to the time of the DB's going suspect are as shown below, hope it helps
Device Activation Error. The physical Disk name 'G:\SQL2KData\Staging
FCB::Open Failed:Could not open device G:\SQL2KData\Staging
udopen:Operating System Error 3(The system cannot find the path specified.) du
The above set of 3 errors go on repeating in the error log, if I m not wrong this is the time when the DB's went suspect, I had no time to check it cos it was my first day at work.
Now what is the detailed way of successfully bringing all the DB's to normal..
Thanks in Advance...
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
June 30, 2008 at 12:35 am
Firstly, make sure that G: exists and that the path G:\SQL2KData\Staging does exist and does have the required data and/or log files that SQL's expecting to be there.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply