database marked as SUSPECT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

database marked as SUSPECT

Author

Topic

shohan_db
Starting Member

36 Posts

Posted - 2010-08-17 : 04:53:38

Dear Sir
I have a 8 GB database,
few days ago i got a exception that "database is suspect"

i tried to change the status of database and failed
then tried to attache mdf file manually and failed

then

USE [master]
GO
-- Method 1: I use this method
EXEC sp_attach_single_file_db @dbname='TestDb',
@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf'

CREATE DATABASE TestDb ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH

but i can't recover my database....

and finally i forced to do restore my last full backup....
and unfortunately i lost about 1 hours data.....

my question is:

01. Why this problem is appear? (i have no power supply problem)

02. how can i investigate the exception?
03. what are the best practice to handle this problem.

please help me by some suggestions

regards

Shohan

shohan

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-17 : 05:38:41

The suspect status is a result of SQL encountering data or log corruption during a rollback/rollforward operation. Corruption is typically an IO subsystem problem. Do some diagnostics of the drives, RAID, SAN, etc. Check the error logs for IO or hardware-related errors.

Restore from backup was the best thing you could have done, that's the recommended fix for corrupt/suspect databases. Other methods of repairing could have lost data, could have caused serious side effects, could have failed entirely.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-08-17 : 06:25:48

"what are the best practice to handle this problem."

1) If you are using FULL Recovery Model then increase TLog backups to be every 15 minutes (the general consensus that I have seen is that 15 minutes is the ideal interval, and that more frequently than that the occasional benefit outweighs other factors (the discussion thread is on SQL Team, so you should be able to find it with a search).

2) Usually when there is a problem you should be able to make a "tail" TLog backup. Thus when you, then, restore you will have zero data loss. I've never had to make a Tail Backup from a Suspect databases, but I'm pretty sure I've read about how it is done. Someone more experienced than me can comment.

3) Now you have done the restore I suggest you check the database - e.g.

DBCC CHECKDB('MyDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-17 : 06:43:35

quote:
Originally posted by Kristen

I've never had to make a Tail Backup from a Suspect databases, but I'm pretty sure I've read about how it is done.

It works, providing it's not the log that's damaged. If damage to the log is what set the DB suspect, log backups may well fail. In that case - SOL.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-08-17 : 06:49:13

Thanks Gail. Do you have to do something fancy to connect to it (in order to make the Tail backup)? Like restart the Service in single user or somesuch?

I ought to go Google really ...

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-17 : 06:52:08

No. Just do a tail-log backup (backup with no_truncate). That's specifically designed to work when the DB's not accessible.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-08-17 : 07:33:58

Brill. Thanks Gail. I've added that to my little "snippets" file for just such sort of rainy days