We help IT Professionals succeed at work.

method to find out the number of blocking, deadlock, SP running long. and then alert us

marrowyung
marrowyung used Ask the Experts™
on
Dear all expertist,

Right now if we want to autmatically receive the email alert of a report for number of blocking per hour, deadlock per hours and (the queries cause this) and which SP running longest daily, what tools/method will you all use ?

DBA100.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
marrowyungSenior Technical architecture (Data)

Author

Commented:
good answer ! but what else other than this tools?

this tools don't give me:

1)Report for number of blocking per hour.
2)Report deadlock per hours and (the queries cause this)
3) Which SP running longest daily which longer than 15 sec.

also how to customize the report in an easy way ?

DBA100.
marrowyungSenior Technical architecture (Data)

Author

Commented:
It seems that the built in report and the MDW report dont' have deadlock report. agree?

Commented:
No average deadlock report  don't loocks so surprising to me. A deadlock is more something for an allert http://www.mssqltips.com/sqlservertip/1523/how-to-setup-sql-server-alerts-and-email-operator-notifications/
If you have them so regular that you want an average per hour ... I think you should look into the cause of the deadlocks and your users will be very happy.
marrowyungSenior Technical architecture (Data)

Author

Commented:
jogos,

Thanks for this and I got this before, good article.

The SQL profiler don't help on automate the whole process.

Any SQL script to find that out any deadlock relationship ? I can make use of it.

DBA100.

Commented:
Get more info about your deadlock in sql error log
http://msdn.microsoft.com/en-us/library/ms178104.aspx
marrowyungSenior Technical architecture (Data)

Author

Commented:
I am thinkng about the same thing,

But in SQL 2008, 1204 or 1222 is the deadlock trace? I tried that before and it doenst work.
marrowyungSenior Technical architecture (Data)

Author

Commented:
one thing is how to make the check log into a script and if I can save it as .sql file, then it will be easlier.

DBA100.
marrowyungSenior Technical architecture (Data)

Author

Commented:
Do you know if the datbase created by the data collector warehouse can be config ?

I don't see any.

For example, every 2 minutes ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
Anyone know how frequent is the MDW tool from SQL server 2008 gather data? any way to change the retention days of the data collection?
marrowyungSenior Technical architecture (Data)

Author

Commented:
it seems that I can't see the utility information report from MDW, how to solve it ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
it seems that right now I can't find out any deadlock reporting method, should I just run the followings:

exec xp_readerrorlog 0,1,'deadlock'

can this return all necessary of the process that cause/participate in the deadlock relation? will it include SPID ?