MS SQL 2005

Raja P
Raja P used Ask the Experts™
on
We need to Monitor the SQL Blocks and currently we don’t have any tool in place,

Is it possible to monitor the SQL blocks like which user ,SPID ,query blocked via script?

The alert supposed to trigger via E-mail with valid information,

Step by step method much appreciated
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
what about starting with this support note?
http://support.microsoft.com/kb/271509
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Along similar lines, there is a good dialogue and explanation about blocked processes : http://visualstudiomagazine.com/articles/2008/06/01/identify-blocked--sql-processes-quickly.aspx

The typical source to identify blocks is via the DMV sys.dm_os_waiting_tasks and a good description / guide.

If you were to simply do :
select * from sys.dm_os_waiting_tasks

Open in new window


You'll get an idea of what is available, but read the article so you are not watching for legit wait states and only look for those problem waits.

Author

Commented:
I need to monitor the SQL Blocks not to identify SQL blocks

My requirement is ---  if there is any block for 30 Min then it need to trigger the alert via Email to notify the DBA for further investigation.

I have already tried http://support.microsoft.com/kb/271509   but it generating the Output file in specified location, this will useful  for further investigation  once we got the Email notification for SQL Blocks.

Looking for Live monitor for SQL Blocks
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ryan McCauleySenior Data Architect

Commented:
I wrote about 75% of an application one time that would execute an "sp_who2" on a server every couple of seconds and display the results as a "blocked process tree" of sorts, displaying processes that were blocked and by whom. It had the option to alert you in the system tray, but it could easily be enhanced to send emails when certain events occurred. The core functionality works fine, but the app crashes occasionally and the polish isn't there, but it gets the job done.

I've attached a screenshot - is this what you're looking for? I haven't done any development on it in about three years, but if it is, I'll open a Codeplex project and post the source (it's in VB.NET). I originally had plans to finish and sell it, but never got around to it and I'm more than happy to open-source it at this point.Screenshot of SQL Contention Monitor

Author

Commented:
Rayn,

Looks Good ,Can you share the source code if it open soure

Thank you so much
Ryan McCauleySenior Data Architect

Commented:
Sure, I'll throw it up on Codeplex so others have access as well. there may even be some desire to improve it. I know Redgate sells a product that's similar now (they didn't when I began development a few years ago - alas), but I think it's $200/server.

I'll wander through the source code tonight to see if I need to scrub it at all, and then I'll post it to Codeplex and post an update here.
Top Expert 2012

Commented:
I know Redgate sells a product that's similar now (they didn't when I began development a
few years ago - alas), but I think it's $200/server.

Are you referring to SQL Monitor ?
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Looking forward to seeing ryanmccauley's codeplex...

And yes, Redgate does have a product - but it is a bit more than a couple of hundred (try several hundred) : http://www.red-gate.com/products/dba/sql-monitor

But there are others I have heard of like SQL Block Monitor (has alerts) for less than a hundred on single server : http://www.sql-tools.net/  and Idera has similar (but wider range of monitoring) : http://www.idera.com/SQL-Server/SQL-diagnostic-manager/

It would be possible to set up your own monitor, but would be involved and require a fairly high understanding of the internals. Done right it can be made to work. Done wrong and then you might find significant performance impacts, and you dont want the lock manager to be resident and continually running (might want to double check the advanced sp_configure option 'blocked process threshold').

That's why the third party options suddenly come into their own - they watch and alert in such a way that they minimise impacts.

It also depends a little on how responsive you need the alerts to be... If there is a block for thirty minutes then that is a lifetime in SQL terms. So if it is to identify and subsequently analyse the root cause (and fix) then it is a little bit different to being alerted of a currently running process so you can intervene - and in that case it is more likely to be minutes to raise an alarm.

If writing your own code and applications, then there are other tools like ApexSQL that can help with the development process and help you tune and optimise to avoid potential problems.

Do you have budget for third party tools, or is this something that you really want to resolve / develop yourself ?
Senior Data Architect
Commented:
After playing around with the code a bit, I'm realizing it's more rough than I remember, but it still gets the (basic) job done. It was also my first shot at mainstream multi-threading, so it's a bit dirty in that regard and I'd probably do things a bit differently if I had it to design over again. I'm open to suggestions in this area for small enhancements that might stabilize things.

I've created the project and uploaded both the source code and the compiled EXE to Codeplex:

https://mssqlblockmonitor.codeplex.com/

Please let me know how it works (or doesn't) for your needs. A few notes:

It only supports integrated auth at this point. The methods and storage is set up to handle SQL auth as well, but the interface doesn't have any way for you to store credentials
When you first launch it, you'll see LOCALHOST. The third button down on the left is "Settings" and will let you add more servers to monitor.
For some reason, the panels don't display correctly at first and aren't lined up. Clicking on another panel, and then back on the initial panel again will fix this. I'd alos love to know why this happens
The project, since it was only intended to be a tool I used, is remarkably devoid of comments. If you have any questions about why I'm doing something they way I am, please post to the discussion board on the Codeplex project and I'll answer it promptly! Also, feel free to suggest features or enhancements on that site as well - while this isn't a primary priority of mine, I'll add what enhancements I can, especially as it helps stabilize the application and add basic functionality.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial