SQL Debugging MP
Version: 1.0.0.15
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