March 14, 2008 at 6:52 pm
HI
WE HAVE TWO DATABASES AT OUR WORK PLACE. BOTH ARE SQL 2000. ONE OF THEM IS SHOWING SUSPECT IN SYSTEM TRAY. CAN ANY OF YOU TELL ME HOW TO RECOVER THE DATABASE
THANKS IN ADVANCE.
March 15, 2008 at 7:35 am
Please don't type in full caps. It's the online equivalent of shouting.
Suspect in system tray? Not quite sure I understand.
If you open enterprise manager and connect to the server in question, do you see the databases? Can you access 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 15, 2008 at 9:18 am
Please also look in the error logs and there should be some clue about why they are suspect.
March 15, 2008 at 7:42 pm
sorry for typing in caps. the system tray icon has become grau in color, in enterprise manager if i click on plus next to database it is not expanding. the lodf file is corupted i think.
March 16, 2008 at 6:51 am
If you expand out the server in enterprise manager, can you see the system databases? Can you see the user databases? If you can see the user database, is there the word (suspect) next to the db name?
Can you connect to the server with query analyser? If so, run the following please
SELECT name, state from master.sys.databases
What's the state of the database in question?
Are there any entries in the sql error log concerning that database? Anything in the windows event log?
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 17, 2008 at 4:14 am
Please run the sp_restetstatus system stored procedure.otherwise you can recycle the SQL services.If still this is the case,follow the below things.
place your database in to emergency mode by using this
__________________________________________________
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
UPDATE master..sysdatabases set status = -32768 WHERE name = 'pubs'
GO
SP_CONFIGURE 'allow updates', 0
RECONFIGURE WITH OVERRIDE
After running the command, the database appears in Enterprise Manager in Read-Only\Offline\Emergency Mode. While the database is in this mode, you can only read from it. If you try to update any values, you receive the following error:
Server: Msg 3908, Level 16, State 1, Line 1
Could not run BEGIN TRANSACTION in database 'pubs'
because the database is in bypass recovery mode.
The statement has been terminated.
March 17, 2008 at 5:44 am
Ack. That should be the very last resort when all else fails, not the first thing to try.
The system tables should not be updated. Doing so can have nasty consequences.
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 18, 2008 at 6:19 am
I find that taking the database offline and then bringing it back online typically works. Or is that not the preferred way of doing it?
March 18, 2008 at 6:31 am
MANJUG05 (3/14/2008)
HIWE HAVE TWO DATABASES AT OUR WORK PLACE. BOTH ARE SQL 2000. ONE OF THEM IS SHOWING SUSPECT IN SYSTEM TRAY. CAN ANY OF YOU TELL ME HOW TO RECOVER THE DATABASE
THANKS IN ADVANCE.
Hey,
As already commented, you will need to find out why db has been marked suspect. You may want to check if disk space was exhaused. Also check for any unexpected power issues.
Thanks,
Phillip Cox
MCITP - DBAdmin
March 23, 2008 at 10:42 pm
hi all
i googled on how to recover a suspect db.
i found the most viable thing to do is taking the database offline, dertach the db, see whether ldf file is corrupted, if so use sp_attach_single_file_db and then bringing it back online. but i am not able to get exact procedure for this. can anybody help.
thanks in advance.
March 24, 2008 at 2:06 pm
Sure we can help. But first we need to know why the database is suspect. Please have a look in the SQL Server error log. There will very likely be something in there indicating why SQL's marked the database as suspect.
Once we know the cause, we can suggest an appropriate solution.
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
April 3, 2008 at 8:08 pm
database is on windows nt server. its a terminal sever. due to heavy network activity the system dumped. on restart sql server is showing suspect db. but error logs i checked, but am not able to make anything out of it
April 4, 2008 at 12:13 am
Have a look through the error log and post any sections that look like tehy contain errors or that you don't understand.
If you like, zip the entire error log up and attach it to your post (providing it's not several MB in size, if it is the earliest 200 or so rows of the latest error log file will probably contain the info).
The thing is, to help you we need to know why SQL has decided to mark that db suspect. That info should be in the error log somewhere
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
April 19, 2014 at 12:12 am
If the Database is in Suspect mode then follow the below steps to recover ur DB
>Stop the SQL Service
>>Copy the MDF and LDF file and place in other safe location
>>>Then Start SQL service
>>>>Now also the Database will be in suspect mode only
>>>>>Drop are Detach the Database
>>>>>>Then Replace the MDF and LDF Files where it was Previously
>>>>>>>And Attach DB by Selecting the MDF File....
Regards
Chowdary...
April 19, 2014 at 6:28 am
Jagadeesh Chowdary (4/19/2014)
If the Database is in Suspect mode then follow the below steps to recover ur DB>Stop the SQL Service
>>Copy the MDF and LDF file and place in other safe location
>>>Then Start SQL service
>>>>Now also the Database will be in suspect mode only
>>>>>Drop are Detach the Database
>>>>>>Then Replace the MDF and LDF Files where it was Previously
>>>>>>>And Attach DB by Selecting the MDF File....
First, bad advice if you haven't determined why the database is suspect in the first place.
Second, it may simply be better to restore from a known good backup (full + t-logs).
Third, this is a 6 year old thread.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply