dba task to prevent dbase suspect mode

Question

Post reply

dba task to prevent dbase suspect mode

jamessdba

SSCrazy

Points: 2256
More actions
June 25, 2010 at 4:04 am

#147695

Dudes,
Our one Production Db Frequently going to Suspect Mode .
Need some clarifications for root cause analysis
why it is frequently going suspect mode.
As a dba what are the pre steps to take care it should not to happen suspect state Next time
My Db Size is Less =166.00 MB
Appreciate your replies
By reading this Forum i came to Know
if corruption (data file or log file) is encountered during a transaction rollback or during restart-recovery when bringing a database online.
how to analyze Whare exactly corruption happened

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Cowboy DBA SSCrazy Points: 2135 More actions · Answer 1

See what was running at the time: sys_dm_exec_requests

Check free disk space at run-time

Check log growth

Check what the autogrowth of the data and log file is set at

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

If you're getting frequent database corruptions, your IO subsystem is likely the cause. Check to IO-related errors, check the RAID/SAN logs, check that all your firmware is updated. Or just get the DB off onto another set of disks.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Cowboy DBA (6/25/2010)
See what was running at the time: sys_dm_exec_requests
Check free disk space at run-time
Check log growth
Check what the autogrowth of the data and log file is set at

None of those are relevant to a DB going suspect.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jamessdba SSCrazy Points: 2256 More actions · Answer 4

Thanks you for reply

1)See what was running at the time: sys_dm_exec_requests

2) Check free disk space at run-time

3) Check log growth

Please let me Know how to check above three

4) Check what the autogrowth of the data and log file is set at

Size MaxSize Growth

168960 KB Unlimited50%

1024 KB Unlimited10%

Cowboy DBA SSCrazy Points: 2135 More actions · Answer 5

GilaMonster (6/25/2010)
Cowboy DBA (6/25/2010)
See what was running at the time: sys_dm_exec_requests
Check free disk space at run-time
Check log growth
Check what the autogrowth of the data and log file is set at
None of those are relevant to a DB going suspect.

OK thanks

Cowboy DBA SSCrazy Points: 2135 More actions · Answer 6

jamessdba (6/25/2010)
Thanks you for reply
1)See what was running at the time: sys_dm_exec_requests
2) Check free disk space at run-time
3) Check log growth
Please let me Know how to check above three
4) Check what the autogrowth of the data and log file is set at
Size MaxSize Growth
168960 KB Unlimited50%
1024 KB Unlimited10%

1. SELECT * FROM sys.dm_exec_requests - thought being that you can monitor what is going on to see if there is a regular pattern of processes that occur before the database goes into suspect mode.

2. Perfmon counters (Logical Disk --> % Free Space) or xp_fixeddrives - thought being could free disk space go to zero and a process attempts to carry on and ending up messing the database up?

3. Perfmon counters (SQL Server:Databases --> Log File Size (KB)) -- to see how your log is growing and to see if that is a ause for the system falling over