database crash

Question

database crash

elchuru

Default port

Points: 1435
More actions
March 3, 2009 at 9:24 am

#131485

Hi,
Recently i faced a question "how do we know database damage/crash happened in sql server?" and
Can anybody give answer to this question!
Thanks in Advance
Vijaya Reddy

Viewing 9 posts - 1 through 8 (of 8 total)

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

SQL_EXPAT SSCrazy Points: 2708 More actions · Answer 1

Some db 'crashes' will generate dumps in SQL error log directory. All db crashes should be reported in the SQL event log.

A DBCC will pick up some formms of database corruption. Basically , you need to run regular DBCCs and scan your error log to pick these up.

thanks

SQL_EXPAT

iainthepitman SSCommitted Points: 1666 More actions · Answer 2

running dbcc checkdb is a good way of finding out if your database has any logical and physical errors.

http://msdn.microsoft.com/en-us/library/ms176064.aspx

CrazyMan SSChampion Points: 12560 More actions · Answer 3

when you are not able to access data from the database, in case server is fine

Saying this , this is a open end Question what exactly are they looking for 🙂

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

prvreddy2000 (3/3/2009)
Recently i faced a question "how do we know database damage/crash happened in sql server?"

Depends what you mean by 'database crash'

If you mean corruption, use CheckDB

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

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 5

checkdb, which should be regularly run, and error log checks (again, regularly scanned), will alert you.

What you do depends on what you find. Good suggestions above.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 6

prvreddy (3/3/2009)
Hi,
Recently i faced a question "how do we know database damage/crash happened in sql server?" and
Can anybody give answer to this question!
Thanks in Advance
Vijaya Reddy

Above suggestions are good

check this out from Paul Randall should give you more idea:

http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx

Sakthivel Chidambaram SSCertifiable Points: 5531 More actions · Answer 7

If you mean "How will we come to know that Database is Crashed?" then answer is

1. Run DBCC CHECKDB('DatabaseName') which checks for integrity of database and it reports error found.

Note:

This check can take some time to run (especially for the object database, due to its size). Therefore, If you planning to run this at regular intervals it can lock or block the database while running. It should be run at non-peak times to minimize performance impact.

2. You will not be able query against the database.

3. You will see Dumps created in LOG folder of SQL Server Installation folder.

4. Event Viewer will record this issue.

5. If Log file of database is corrupted then Database will go into Suspect Mode.

6. SELECT DatabasePropertyEX('DB', 'Status') AS [Database Status] will give the current status.

Thanks!

You can also check this video by Paul on How shrink can cause serious fragmentation problem to DB

http://wtv.watchtechvideos.com/topic194.html

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

maechismo_8514 SSCoach Points: 17510 More actions · Answer 8

prvreddy (3/3/2009)
Hi,
Recently i faced a question "how do we know database damage/crash happened in sql server?" and
Can anybody give answer to this question!
Thanks in Advance
Vijaya Reddy

You would not really know, unless you check your error logs as part of your early morning check list and also if it is severely damaged this might go into suspect mode and you can see it on SSMS.

Include DBCC checks as your regular maintenance tasks at off peak hours.