Stored Procedure to Monitor Database and Powershell Script to Monitor Database and Blocking

— ==========================================================

— Author Name: Manjunath C Bhat

— Author EMail ID: manjunathcbhat@gmail.com

— Author Designation: Senior Database Engineer

— Stored Procedure Purpose: To Monitor the Database Activity

— Stored Procedure Name: SQLActivityMonitor

— Parameter1: @MaxBlockWaitTime Type Int

— Parameter2: @StatusCode Type TinyInt OUTPUT

— Parameter3: @StatusMessage Type Varchar OUTPUT

— ==========================================================

— Stored Procedure To Monitor SQLDatabaseActivity

— ==========================================================

— Drop stored procedure if it already exists

IF EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE SPECIFIC_SCHEMA = N’Dbo’

AND SPECIFIC_NAME = N’SQLActivityMonitor’

)

DROP PROCEDURE [DBO].[SQLActivityMonitor]

GO

CREATE PROCEDURE [DBO].[SQLActivityMonitor]

@MaxBlockWaitTime INT,

@StatusCode TINYINT OUTPUT,

@StatusMessage VARCHAR(8000) OUTPUT

AS

BEGIN

— SET NOCOUNT ON added to prevent extra result sets from

— interfering with SELECT statements.

SET NOCOUNT ON;

–Determine # of Active Connections, Open Transactions and Blocked Transaction

DECLARE @BlockedCount INT

DECLARE @OpenTranCount INT

DECLARE @ConnectionCount INT

DECLARE @ExceptionBlockedCount INT

SELECT @ConnectionCount=COUNT(*) FROM MASTER..SYSPROCESSES

SELECT @OpenTranCount = SUM(OPEN_TRAN) FROM MASTER..SYSPROCESSES

SELECT @BlockedCount=COUNT(*),

@ExceptionBlockedCount = SUM(CASE WHEN WAITTIME>@MaxBlockWaitTime THEN 1 ELSE 0 END)

FROM MASTER..SYSPROCESSES

WHERE BLOCKED<>0

IF @ExceptionBlockedCount>0

BEGIN

–Failure if blocked transactions are present that exceeded Timeout

SET @StatusCode=3

SET @StatusMessage = CAST(@ExceptionBlockedCount AS VARCHAR(255)) + ‘ Blocked transactions (with wait time > specified timeout) are present.’

END

ELSE

BEGIN

IF @BlockedCount>0

BEGIN

–Warn regarding blocked transactions

SET @StatusCode=2

SET @StatusMessage = CAST(@BlockedCount AS VARCHAR(255)) + ‘ Blocked transactions were present.’

END

ELSE

BEGIN

–OK

SET @StatusCode=1

SET @StatusMessage = ‘OK.’

END

END

–Return Key counters

DECLARE @Counters TABLE (Counter VARCHAR(50), [Value] INT, OrderBy TINYINT)

INSERT INTO @Counters (Counter, [Value], OrderBy) VALUES(‘# Conns’, COALESCE(@ConnectionCount,0), 1)

INSERT INTO @Counters (Counter, [Value], OrderBy) VALUES(‘# Open Trans’, COALESCE(@OpenTranCount,0), 2)

INSERT INTO @Counters (Counter, [Value], OrderBy) VALUES(‘# Blocked’, COALESCE(@BlockedCount,0), 3)

INSERT INTO @Counters (Counter, [Value], OrderBy) VALUES(‘# Block Exceptions’, COALESCE(@ExceptionBlockedCount,0), 4)

SELECT COUNTER, [Value]

FROM @Counters

ORDER BY OrderBy

END

GO

— ==========================================================

— HOW TO RUN THE PROCEDURE

— ==========================================================

DECLARE @SCode TINYINT,

@SMessage VARCHAR(8000)

EXECUTE [dbo].[SQLActivityMonitor]

@MaxBlockWaitTime = ‘15000’,

@StatusCode = @SCode OUTPUT,

@StatusMessage = @SMessage OUTPUT

SELECT StatusCode = @SCode,StatusMessage = @SMessage

GO

The OutPut Values can be Retrived in FrontEnd application like Powershell,C# etc.

Below is a sample for using the above Procedure and Retrieving the values in Powershell.

Note: Change the ServerName,DatabaseName and MBwaittime as per your convinience.

Also make a note that the MBWaittime is considered in terms of MilliSeconds.

–POWERSHELL SCRIPT TO MONITOR SQL Database.

$ServerName = ‘YOURSERVERNAME’

$DatabaseName = ‘YourDatabaseName’

$Query = ‘SQLActivityMonitor’

$MBWaittime = ‘YourDesiredBlockTime’

$connString = “Server=$serverName;Database=$databaseName;Integrated Security=SSPI;”

$conn = new-object System.Data.SqlClient.SqlConnection $connString

$conn.Open()

$cmd = new-object System.Data.SqlClient.SqlCommand(“$Query”, $conn)

$cmd.CommandType = [System.Data.CommandType]”StoredProcedure”

$cmd.Parameters.Add(“@MaxBlockWaitTime”, [System.Data.SqlDbType]”Int”)

$cmd.Parameters[“@MaxBlockWaitTime”].Value = $MBWaittime

$cmd.Parameters.Add(“@StatusCode”, [System.Data.SqlDbType]”TinyInt”)

$cmd.Parameters[“@StatusCode”].Direction = [System.Data.ParameterDirection]”Output”

$cmd.Parameters.Add(“@StatusMessage”, [System.Data.SqlDbType]”Varchar”,8000)

$cmd.Parameters[“@StatusMessage”].Direction = [System.Data.ParameterDirection]”Output”

$cmd.ExecuteNonQuery()

$conn.Close()

$cmd.Parameters[“@StatusCode”].Value

$cmd.Parameters[“@StatusMessage”].Value

Advertisements

One thought on “Stored Procedure to Monitor Database and Powershell Script to Monitor Database and Blocking

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s