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