Service Broker Queue Monitor

2020-02-01 · by Hannah Vernon · in service broker, tools

My previous post shows how to configure an Event Notification to fire whenever a login event occurs. The post uses Service Broker to receive those Event Notifications into a queue, which is then processed by a stored procedure and saved into a standard SQL Server database. This post provides a quick+dirty VB.Net command line monitor that shows how full a Service Broker queue is.

Not service broker queue monitor related, but still pretty nice! The Quilting Fabric - 1813 by John Lewis Krimmel

The Quilting Fabric – 1813 – John Lewis Krimmel

The following code should be pasted into a blank Visual Studio VB.Net console project. It is trivially easy to translate this into C#, but I like VB – what can I say.

Module Module1

    Sub Main()

        Dim Server As String = ""
        Dim Database As String = ""
        Dim QueueSchema As String = ""
        Dim QueueName As String = ""
        Dim PromptForLogin As Boolean = False
        Dim iExp As Int16 = 1
        Dim CommandText As String = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @cmd nvarchar(max);
SET @cmd = N'SELECT MessageCount = COUNT_BIG(1)
    , ServerDateTime = GETDATE()
FROM |||;';
EXEC sys.sp_executesql @cmd;
"

        Console.Error.WriteLine("ServiceBrokerQueueMonitor " + My.Application.Info.Version.ToString + " (c) 2020 Hannah Vernon")
        Console.Error.WriteLine()

        If My.Application.CommandLineArgs.Count < 4 Or My.Application.CommandLineArgs.Count > 5 Then
            ShowUsage()
        Else
            For Each s In My.Application.CommandLineArgs
                Dim bOK As Boolean = False
                If s.StartsWith("/Server:", StringComparison.OrdinalIgnoreCase) Then
                    Server = Right(s, Len(s) - (InStr(s, ":")))
                    bOK = True
                End If
                If s.StartsWith("/Database:", StringComparison.OrdinalIgnoreCase) Then
                    Database = Right(s, Len(s) - (InStr(s, ":")))
                    bOK = True
                End If
                If s.StartsWith("/QueueName:", StringComparison.OrdinalIgnoreCase) Then
                    QueueName = Right(s, Len(s) - (InStr(s, ":")))
                    bOK = True
                End If
                If s.StartsWith("/QueueSchema:", StringComparison.OrdinalIgnoreCase) Then
                    QueueSchema = Right(s, Len(s) - (InStr(s, ":")))
                    bOK = True
                End If
                If s.StartsWith("/PromptForLogin") Then
                    PromptForLogin = True
                    bOK = True
                End If
                If bOK = False Then
                    ShowUsage()
                End If
            Next
        End If
        Console.Error.WriteLine("Server:     " & Server)
        Console.Error.WriteLine("Database:   " & Database)
        Console.Error.WriteLine("Queue Name: " & QueueName)
        Console.Error.WriteLine("")
        Console.Error.WriteLine("Hit Q to quit.")
        Console.Error.WriteLine("")
        CommandText = Replace(CommandText, "|||", "[" + QueueSchema + "].[" + QueueName + "]")
        Console.Error.WriteLine("")

        Dim cb As New SqlClient.SqlConnectionStringBuilder With {.ApplicationIntent = SqlClient.ApplicationIntent.ReadOnly,
            .ApplicationName = "Service Broker Queue Manager",
            .Authentication = IIf(PromptForLogin = False, SqlClient.SqlAuthenticationMethod.ActiveDirectoryIntegrated, SqlClient.SqlAuthenticationMethod.SqlPassword),
            .Encrypt = True,
            .InitialCatalog = Database,
            .MultipleActiveResultSets = False,
            .NetworkLibrary = "DBMSSOCN",
            .Pooling = False,
            .DataSource = Server,
            .TrustServerCertificate = True,
            .WorkstationID = My.Computer.Name
            }
        If PromptForLogin Then
            Dim PWD As String = ""
            Console.Error.WriteLine("Enter Login:")
            Dim UserID As String = Console.ReadLine()
            Console.Error.WriteLine("Enter Password:")
            Dim c As ConsoleKeyInfo = Console.ReadKey(True)
            Do Until c.Key = ConsoleKey.Enter
                PWD += c.KeyChar
                c = Console.ReadKey(True)
            Loop
            cb.UserID = UserID
            cb.Password = PWD
        End If
        Using Connection As New SqlClient.SqlConnection(cb.ConnectionString)
            Connection.Open()
            If Connection.State = ConnectionState.Open Then
                While 1 = 1
                    Using cmd As New SqlClient.SqlCommand(CommandText, Connection)
                        Dim reader As SqlClient.SqlDataReader = cmd.ExecuteReader
                        While reader.Read
                            Dim iCount As Int64 = reader.GetSqlInt64(reader.GetOrdinal("MessageCount"))
                            Dim iDateTIme As DateTime = reader.GetSqlDateTime(reader.GetOrdinal("ServerDateTime"))
                            While iCount > iExp * 100
                                iExp *= 10
                                Console.Error.WriteLine("Server Date Time    Waiting Messages (x" & iExp & ")")
                                Console.Error.WriteLine("==================  ==========================")
                            End While
                            Console.WriteLine(iDateTIme.ToString(format:="yyyy-MM-dd hh:mm:ss") & " " & New String("*", iCount / iExp))
                        End While
                        reader.Close()
                    End Using
                    If Console.KeyAvailable AndAlso Console.ReadKey().KeyChar.ToString.ToUpper = "Q" Then
                        End
                    End If
                    System.Threading.Thread.Sleep(15000)
                End While
            End If
            Connection.Close()
        End Using

    End Sub

    Private Sub ShowUsage()
        Console.Error.WriteLine("Usage Is:")
        Console.Error.WriteLine("")
        Console.Error.WriteLine("SBQM.exe /Server:<SQLServerInstanceName> /Database:<DatabaseName> /QueueSchema:<ServiceBrokerQueueSchema> /QueueName:<ServiceBrokerQueueName> [/PromptForLogin]")
        Console.Error.WriteLine("")
        Console.Error.WriteLine("ex:  SBQM.exe /Server:SERVER\INSTANCE /Database:msdb  /QueueSchema:dbo /QueueName:mail_queue")
        Console.Error.WriteLine("")
        Console.Error.WriteLine("[/PromptForLogin] is optional, and will ask for authentication details.")
        Console.Error.WriteLine("")
        Console.Error.WriteLine("")
        End
    End Sub

End Module

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

Module Module1

Sub Main()

Dim Server As String = ""

Dim Database As String = ""

Dim QueueSchema As String = ""

Dim QueueName As String = ""

Dim PromptForLogin As Boolean = False

Dim iExp As Int16 = 1

Dim CommandText As String = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @cmd nvarchar(max);

SET @cmd = N'SELECT MessageCount = COUNT_BIG(1)

, ServerDateTime = GETDATE()

FROM |||;';

EXEC sys.sp_executesql @cmd;

Console.Error.WriteLine()

If My.Application.CommandLineArgs.Count < 4 Or My.Application.CommandLineArgs.Count > 5 Then

ShowUsage()

Else

For Each s In My.Application.CommandLineArgs

Dim bOK As Boolean = False

If s.StartsWith("/Server:", StringComparison.OrdinalIgnoreCase) Then

Server = Right(s, Len(s) - (InStr(s, ":")))

bOK = True

End If

If s.StartsWith("/Database:", StringComparison.OrdinalIgnoreCase) Then

Database = Right(s, Len(s) - (InStr(s, ":")))

bOK = True

End If

If s.StartsWith("/QueueName:", StringComparison.OrdinalIgnoreCase) Then

QueueName = Right(s, Len(s) - (InStr(s, ":")))

bOK = True

End If

If s.StartsWith("/QueueSchema:", StringComparison.OrdinalIgnoreCase) Then

QueueSchema = Right(s, Len(s) - (InStr(s, ":")))

bOK = True

End If

If s.StartsWith("/PromptForLogin") Then

PromptForLogin = True

bOK = True

End If

If bOK = False Then

ShowUsage()

End If

Console.Error.WriteLine("Server: " & Server)

Console.Error.WriteLine("Database: " & Database)

Console.Error.WriteLine("Queue Name: " & QueueName)

Console.Error.WriteLine("")

Console.Error.WriteLine("Hit Q to quit.")

Console.Error.WriteLine("")

CommandText = Replace(CommandText, "|||", "[" + QueueSchema + "].[" + QueueName + "]")

Console.Error.WriteLine("")

Dim cb As New SqlClient.SqlConnectionStringBuilder With {.ApplicationIntent = SqlClient.ApplicationIntent.ReadOnly,

.ApplicationName = "Service Broker Queue Manager",

.Authentication = IIf(PromptForLogin = False, SqlClient.SqlAuthenticationMethod.ActiveDirectoryIntegrated, SqlClient.SqlAuthenticationMethod.SqlPassword),

.Encrypt = True,

.InitialCatalog = Database,

.MultipleActiveResultSets = False,

.NetworkLibrary = "DBMSSOCN",

.Pooling = False,

.DataSource = Server,

.TrustServerCertificate = True,

.WorkstationID = My.Computer.Name

}

If PromptForLogin Then

Dim PWD As String = ""

Console.Error.WriteLine("Enter Login:")

Dim UserID As String = Console.ReadLine()

Console.Error.WriteLine("Enter Password:")

Dim c As ConsoleKeyInfo = Console.ReadKey(True)

Do Until c.Key = ConsoleKey.Enter

PWD += c.KeyChar

c = Console.ReadKey(True)

Loop

cb.UserID = UserID

cb.Password = PWD

End If

Using Connection As New SqlClient.SqlConnection(cb.ConnectionString)

Connection.Open()

If Connection.State = ConnectionState.Open Then

While 1 = 1

Using cmd As New SqlClient.SqlCommand(CommandText, Connection)

Dim reader As SqlClient.SqlDataReader = cmd.ExecuteReader

While reader.Read

Dim iCount As Int64 = reader.GetSqlInt64(reader.GetOrdinal("MessageCount"))

Dim iDateTIme As DateTime = reader.GetSqlDateTime(reader.GetOrdinal("ServerDateTime"))

While iCount > iExp * 100

iExp *= 10

Console.Error.WriteLine("Server Date Time Waiting Messages (x" & iExp & ")")

Console.Error.WriteLine("================== ==========================")

End While

Console.WriteLine(iDateTIme.ToString(format:="yyyy-MM-dd hh:mm:ss") & " " & New String("*", iCount / iExp))

End While

reader.Close()

End Using

If Console.KeyAvailable AndAlso Console.ReadKey().KeyChar.ToString.ToUpper = "Q" Then

End

End If

System.Threading.Thread.Sleep(15000)

End While

End If

Connection.Close()

End Using

End Sub

Private Sub ShowUsage()

Console.Error.WriteLine("Usage Is:")

Console.Error.WriteLine("")

Console.Error.WriteLine("SBQM.exe /Server:<SQLServerInstanceName> /Database:<DatabaseName> /QueueSchema:<ServiceBrokerQueueSchema> /QueueName:<ServiceBrokerQueueName> [/PromptForLogin]")

Console.Error.WriteLine("")

Console.Error.WriteLine("ex: SBQM.exe /Server:SERVER\INSTANCE /Database:msdb /QueueSchema:dbo /QueueName:mail_queue")

Console.Error.WriteLine("")

Console.Error.WriteLine("[/PromptForLogin] is optional, and will ask for authentication details.")

Console.Error.WriteLine("")

End

End Sub

End Module

Output looks like this:

ServiceBrokerQueueMonitor 2020.1.31.1340 (c) 2020 Hannah Vernon

Server:     MY\SERVER
Database:   login_auditing
Queue Name: login_audit_queue

Hit Q to quit.

Enter Login:
blah
Enter Password:
Server Date Time    Waiting Messages (x10)
==================  ==========================
Server Date Time    Waiting Messages (x100)
==================  ==========================
Server Date Time    Waiting Messages (x1000)
==================  ==========================
2020-01-31 02:02:54 *************************
2020-01-31 02:03:09 ***********************
2020-01-31 02:03:24 **********************
2020-01-31 02:03:40 *********************
2020-01-31 02:03:55 ********************
2020-01-31 02:04:10 ******************
2020-01-31 02:04:25 *****************
2020-01-31 02:04:41 ***************
2020-01-31 02:04:56 *************
2020-01-31 02:05:11 ************
2020-01-31 02:05:26 ***********
2020-01-31 02:05:41 *********
2020-01-31 02:05:56 ********
2020-01-31 02:06:12 ******
2020-01-31 02:06:27 ***
2020-01-31 02:06:42 *
2020-01-31 02:06:57
2020-01-31 02:07:12
2020-01-31 02:07:27
2020-01-31 02:07:42
2020-01-31 02:07:57
2020-01-31 02:08:12
2020-01-31 02:08:27
2020-01-31 02:08:42

The output consists of the datestamp, plus a number of asterisks representing the approximate number of messages waiting in the queue. The number of asterisks is scaled automatically as needed. In the above example each asterisk indicates 1,000 messages are waiting. The output above is from a very busy SharePoint server, where the maximum number of messages waiting in the queue was around 25,000!

Let me know if you find this Service Broker Queue Monitor helpful. If you have a question about the code, or a suggestion, please post a comment below!

Tags: code, queues, service broker, tools, visual studio

Service Broker Queue Monitor

Related

One Response

Leave a ReplyCancel reply

Search

Categories

Pages

Meta