What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general.

Learn more

PRTG Network Monitor

Intuitive to Use. Easy to manage.
More than 500,000 users rely on Paessler PRTG every day. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free Download

Top Tags


View all Tags

How to check for SQL Query Blocks and Blocking

Votes:

0

Hi,

I have 18 days left of my trial. PRTG does everything i want it too to be able to finally allow me to move from using Nagios, but i need 1 more thing to be able to accomplish before my boss will part with the cash for a licence....

I need to be able to monitor databases for Blocking and be alerted when there is a query being blocked for longer than X minutes.

I am trying to use the MS Sql v2 Sensor to run a .sql query file which is thus:

DECLARE @WhoTable TABLE
(
       SPID INT,
       [Status] VARCHAR(MAX),
       [Login] VARCHAR(MAX),
       HostName VARCHAR(MAX),
       BlkBy VARCHAR(MAX),
       DBName VARCHAR(MAX),
       Command VARCHAR(MAX),
       CPUTime INT,
       DiskIO INT,
       LastBatch VARCHAR(MAX),
       ProgramName VARCHAR(MAX),
       SPID_1 INT,
       REQUESTID INT
)

INSERT INTO @WhoTable EXEC sp_Who2

SELECT SPID, BlkBy
FROM   @WhoTable

This outputs a table:
https://imgur.com/rcl2y73

Now id like to pass the SPID and BlkBy data into the sensor

Not sure how to go about this though as the BlkBy value is neither NULL or numeric if nothing is being blocked, just a -

In an ideal world id like the sensor to be OK when there is nothing being blocked, to report as down when blocking takes place and BlkBy will have the SPID of the user blocking it.

Is this possible please, if so could somebody help me out as all im getting when trying different combinations setting the sensor up is [System.String] value can not be parsed

Many Thanks

mssql sql sqlv2

Created on Jul 3, 2018 11:01:11 AM

Last change on Jul 4, 2018 9:46:20 AM by Dariusz Gorka [Paessler Support]



2 Replies

Accepted Answer

Votes:

0

Hi Dariusz,

Since no one is answering, I thought I give you a hint..

Why don't you just alter your SELECT in the end - if I understand you right you want to know if something is blocked. The easiest way to go would be to just alter the script like this:

DECLARE @WhoTable TABLE
(
       SPID INT,
       [Status] VARCHAR(MAX),
       [Login] VARCHAR(MAX),
       HostName VARCHAR(MAX),
       BlkBy VARCHAR(MAX),
       DBName VARCHAR(MAX),
       Command VARCHAR(MAX),
       CPUTime INT,
       DiskIO INT,
       LastBatch VARCHAR(MAX),
       ProgramName VARCHAR(MAX),
       SPID_1 INT,
       REQUESTID INT
)

INSERT INTO @WhoTable EXEC sp_Who2

SELECT COUNT(SPID) 
FROM   @WhoTable
WHERE BlkBy LIKE '-'

What I changed is simply the last select. Instead of just dumping the table to PRTG I actually allow PRTG to process the amount of blocks. Now, the next question is of course, do you really just want to see.. your issue sure gonna be the amount of time a certain query is executed. The CPUTime might help you there, but you as well should filter it on DBName and STATUS.

Lets say you have a DB that might have blocked queries/commands that cause issues. Then you should see the CPUTime increasing and going going through the roof - I did not research CPUTime but I assume it is either in milliseconds or in ticks.

If that does not help you, you would need to create a script around your query - or write a log-table in a database that lets you process this stuff further - like SPID and LastBatch for certain commands on certain databases that are e.g. in the (Status NOT LIKE 'BACKGROUND' OR Status NOT LIKE 'sleeping') etc. - it depends a bit on fine tuning.

Finally the question itself is - what is it that you want to accomplish - there might be other SQL commands that would help you more like the following one, that actually looks in to current processes in SQL

select spid, db_name(dbid), * from master..sysprocesses

Hope this helps you..

Regards

Florian Rossmark www.it-admins.com

Created on Jul 11, 2018 2:12:27 PM



Votes:

0

Hi there,

Unfortunately, we didn't had any valuable input from our side (the support team). The thread was originally opened by "ftl", I just changed the post for a better readability.

But, thank you very much for the answer! I hope "ftl" is able to use your example.

Best regards.

Created on Jul 11, 2018 7:40:06 PM by Dariusz Gorka [Paessler Support]




Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.