SQL MP Debug Logging – On/Off with a Simple Agent Task

Tyson Paul0 commentsAuthoringManagement PacksTroubleshooting


SQL Debugging MP
Version: 1.0.0.15
Download Now!
10 Downloads



Sometimes you need to activate SQL Management Pack debugging. The SCOM product group makes it relatively easy; by creating a registry key to instantly begin writing debug information to the Operations Manager event log. 

'HKLM:\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\SQL Management Packs\EnableEvtLogDebugOutput\SQL Server MP'


To make things even easier, here are two tasks:

  • SQL EnableEvtLogDebugOutput: Enable
  • SQL EnableEvtLogDebugOutput: Disable


Enable:


Disable



Logging should begin instantly upon completion of the Enable task.



Logging Data Examples:


Example 1

Management Group: "SCOMLAB22"

Module Name: Microsoft.SQLServer.Windows.Module.Discovery.Discoveries.DatabaseDiscovery
Module Version: 7.2.0.0
Number of Items: 1


Number of Instances: 8
Number of Relationships: 0

Instance: 1
Type: 0122e31e-8872-ec01-8993-63a4b2eb3d01
Property: MachineName Type: System.String Value: SQL21.CONTOSO.COM
Property: InstanceName Type: System.String Value: INST1
Property: DisplayName Type: System.String Value: TEST3
Property: DatabaseName Type: System.String Value: TEST3
Property: Collation Type: System.String Value: SQL_Latin1_General_CP1_CI_AS
Property: RecoveryModel Type: System.String Value: FULL
Property: Updateability Type: System.String Value: READ_WRITE
Property: UserAccess Type: System.String Value: MULTI_USER
Property: DatabaseAutogrow Type: System.String Value: True
Property: LogAutogrow Type: System.String Value: True
Property: Owner Type: System.String Value: CONTOSO\tpaul

Instance: 2
Type: 0122e31e-8872-ec01-8993-63a4b2eb3d01
Property: MachineName Type: System.String Value: SQL21.CONTOSO.COM
Property: InstanceName Type: System.String Value: INST1
Property: DisplayName Type: System.String Value: TEST2
Property: DatabaseName Type: System.String Value: TEST2
Property: Collation Type: System.String Value: SQL_Latin1_General_CP1_CI_AS
Property: RecoveryModel Type: System.String Value: FULL
Property: Updateability Type: System.String Value: READ_WRITE
Property: UserAccess Type: System.String Value: MULTI_USER
Property: DatabaseAutogrow Type: System.String Value: True
Property: LogAutogrow Type: System.String Value: True
Property: Owner Type: System.String Value: CONTOSO\tpaul

Instance: 3
Type: 0122e31e-8872-ec01-8993-63a4b2eb3d01
Property: MachineName Type: System.String Value: SQL21.CONTOSO.COM
Property: InstanceName Type: System.String Value: INST1
Property: DisplayName Type: System.String Value: OperationsManager
Property: DatabaseName Type: System.String Value: OperationsManager
Property: Collation Type: System.String Value: SQL_Latin1_General_CP1_CI_AS
Property: RecoveryModel Type: System.String Value: FULL
Property: Updateability Type: System.String Value: READ_WRITE
Property: UserAccess Type: System.String Value: MULTI_USER
Property: DatabaseAutogrow Type: System.String Value: False
Property: LogAutogrow Type: System.String Value: False
Property: Owner Type: System.String Value: sa

Instance: 4
Type: 0122e31e-8872-ec01-8993-63a4b2eb3d01
Property: MachineName Type: System.String Value: SQL21.CONTOSO.COM
Property: InstanceName Type: System.String Value: INST1
Property: DisplayName Type: System.String Value: TEST1
Property: DatabaseName Type: System.String Value: TEST1
Property: Collation Type: System.String Value: SQL_Latin1_General_CP1_CI_AS
Property: RecoveryModel Type: System.String Value: FULL
Property: Updateability Type: System.String Value: READ_WRITE
Property: UserAccess Type: System.String Value: MULTI_USER
Property: DatabaseAutogrow Type: System.String Value: True
Property: LogAutogrow Type: System.String Value: True
Property: Owner Type: System.String Value: sa

Instance: 5
Type: 0122e31e-8872-ec01-8993-63a4b2eb3d01
Property: MachineName Type: System.String Value: SQL21.CONTOSO.COM
Property: InstanceName Type: System.String Value: INST1
Property: DisplayName Type: System.String Value: msdb
Property: DatabaseName Type: System.String Value: msdb
Property: Collation Type: System.String Value: SQL_Latin1_General_CP1_CI_AS
Property: RecoveryModel Type: System.String Value: SIMPLE
Property: Updateability Type: System.String Value: READ_WRITE
Property: UserAccess Type: System.String Value: MULTI_USER
Property: DatabaseAutogrow Type: System.String Value: True
Property: LogAutogrow Type: System.String Value: True
Property: Owner Type: System.String Value: sa

Instance: 6
Type: 0122e31e-8872-ec01-8993-63a4b2eb3d01
Property: MachineName Type: System.String Value: SQL21.CONTOSO.COM
Property: InstanceName Type: System.String Value: INST1
Property: DisplayName Type: System.String Value: model
Property: DatabaseName Type: System.String Value: model
Property: Collation Type: System.String Value: SQL_Latin1_General_CP1_CI_AS
Property: RecoveryModel Type: System.String Value: FULL
Property: Updateability Type: System.String Value: READ_WRITE
Property: UserAccess Type: System.String Value: MULTI_USER
Property: DatabaseAutogrow Type: System.String Value: True
Property: LogAutogrow Type: System.String Value: True
Property: Owner Type: System.String Value: sa

Instance: 7
Type: 0122e31e-8872-ec01-8993-63a4b2eb3d01
Property: MachineName Type: System.String Value: SQL21.CONTOSO.COM
Property: InstanceName Type: System.String Value: INST1
Property: DisplayName Type: System.String Value: tempdb
Property: DatabaseName Type: System.String Value: tempdb
Property: Collation Type: System.String Value: SQL_Latin1_General_CP1_CI_AS
Property: RecoveryModel Type: System.String Value: SIMPLE
Property: Updateability Type: System.String Value: READ_WRITE
Property: UserAccess Type: System.String Value: MULTI_USER
Property: DatabaseAutogrow Type: System.String Value: True
Property: LogAutogrow Type: System.String Value: True
Property: Owner Type: System.String Value: sa

Instance: 8
Type: 0122e31e-8872-ec01-8993-63a4b2eb3d01
Property: MachineName Type: System.String Value: SQL21.CONTOSO.COM
Property: InstanceName Type: System.String Value: INST1
Property: DisplayName Type: System.String Value: master
Property: DatabaseName Type: System.String Value: master
Property: Collation Type: System.String Value: SQL_Latin1_General_CP1_CI_AS
Property: RecoveryModel Type: System.String Value: SIMPLE
Property: Updateability Type: System.String Value: READ_WRITE
Property: UserAccess Type: System.String Value: MULTI_USER
Property: DatabaseAutogrow Type: System.String Value: True
Property: LogAutogrow Type: System.String Value: True
Property: Owner Type: System.String Value: sa

The configuration properties are: 
ManagementGroupName = SCOMLAB22
Publisher = SQLDiscoveryWindows
ConnectionString = SQL21.CONTOSO.COM\INST1
InstanceEdition = Enterprise Edition
InstanceName = INST1
InstanceVersion = 15.0.4298.1
MachineName = SQL21.CONTOSO.COM
MonitoringType = Local
NetbiosComputerName = SQL21
Login = 
ConnectionString = SQL21.CONTOSO.COM\INST1
DatabaseClassId = 722594ba-bc11-45d5-81a2-a6059ada4682
DatabaseReferencesUserResourcePoolClassId = 505d3154-32c8-4242-48e1-c0531ce8dcae
DefaultPoolClassId = 0fca59ad-48dc-6118-5665-3fdddc183bbc
DiscoverySourceManagedEntityId = 4935d405-05b2-073b-c47e-31bf49914c2f
DiscoverySourceObjectId = f8651819-6311-a818-3e20-86dc7fe0817f
ExcludeList = 
InstanceName = INST1
LocalDatabaseClassId = 0122e31e-8872-ec01-8993-63a4b2eb3d01
MachineName = SQL21.CONTOSO.COM
SqlExecTimeoutSeconds = 60
SqlTimeoutSeconds = 15
TimeoutSeconds = 300
UserDefinedResourcePoolClassId = c5d88380-0c94-93ce-5379-885862735019
Password = ********


Example 2

Management Group: "SCOMLAB22"

Module Name: Microsoft.SQLServer.Windows.Module.Monitoring.Monitors.ServiceStatus
Module Version: 7.2.0.0
Number of Items: 1


Item: 1
Key: ServiceState Type: System.String Value: Running

The configuration properties are: 
ManagementGroupName = SCOMLAB22
Publisher = SQLMonitoringWindows
CheckStartupType = True
PrincipalName = SQL21.CONTOSO.COM
ServiceName = MSSQL$INST1
TimeoutSeconds = 300
UnavailableTime = 900


Example 3

Management Group: "SCOMLAB22"

Module Name: Microsoft.SQLServer.Windows.Module.Monitoring.Performance.DBActiveSessionsCountAction
Module Version: 7.2.0.0
Number of Items: 8


Item: 1
Key: DatabaseName Type: System.String Value: master
Key: NSessions Type: System.Int32 Value: 71
Item: 2
Key: DatabaseName Type: System.String Value: tempdb
Key: NSessions Type: System.Int32 Value: 0
Item: 3
Key: DatabaseName Type: System.String Value: model
Key: NSessions Type: System.Int32 Value: 0
Item: 4
Key: DatabaseName Type: System.String Value: msdb
Key: NSessions Type: System.Int32 Value: 5
Item: 5
Key: DatabaseName Type: System.String Value: TEST1
Key: NSessions Type: System.Int32 Value: 0
Item: 6
Key: DatabaseName Type: System.String Value: OperationsManager
Key: NSessions Type: System.Int32 Value: 68
Item: 7
Key: DatabaseName Type: System.String Value: TEST2
Key: NSessions Type: System.Int32 Value: 0
Item: 8
Key: DatabaseName Type: System.String Value: TEST3
Key: NSessions Type: System.Int32 Value: 0

The configuration properties are: 
ManagementGroupName = SCOMLAB22
Publisher = SQLMonitoringWindows
ConnectionString = SQL21.CONTOSO.COM\INST1
InstanceEdition = Enterprise Edition
InstanceName = INST1
InstanceVersion = 15.0.4298.1
MachineName = SQL21.CONTOSO.COM
MonitoringType = Local
NetbiosComputerName = SQL21
Login = 
SqlExecTimeoutSeconds = 60
SqlTimeoutSeconds = 15
TimeoutSeconds = 300
Password = ********

Version History

  •    2024.07.08 – Improved error handling and logging; logic to identify if debugging is enabled or not for mgmt group.
  •    2023.06.02 – V1
← →

Leave a Reply

Your email address will not be published. Required fields are marked *