To find all rules in a Management Pack use the following query and substitute in the required Management Pack name: SELECT * FROM Rules This table has columns linking rules to classes and Management Packs. Where md.managementpackid = and rl.ruleid = md.parentidĪnd moduleconfiguration like ' %2% ' - Rules are stored in a table named Rules. Where mpName = ' Microsoft.SystemCenter.2007 ' select rl.rulename,rl.ruleid,md.modulename ORDER BY RulesPerMPPerCategory DESC - To find all rules per MP with a given alert severity: declare as varchar ( 50 ) ORDER BY RulesPerMP DESC - Rules per MP by category: SELECT mp.MPName, r.RuleCategory, COUNT ( * ) As RulesPerMPPerCategory INNER JOIN ManagementPack mp ON mp.ManagementPackID = r.ManagementPackID Where name = ' .Alert ' - Rules per MP: SELECT mp.MPName, COUNT ( * ) As RulesPerMP To find a common rule name given a Rule ID name: SELECT DisplayName from RuleView Monitors with the most instances of critical state (what workflows are causing the most unhealth-iness): Group by s.BasemanagedEntityId,bme.DisplayName,bme.Path,m.DisplayName,m.Name,mt.typename Where m.IsUnitMonitor = 1 - Scoped to specific Monitor (remove the "-" below): - AND m.MonitorName like ('%HealthService%') - Scoped to specific Computer (remove the "-" below): - AND bme.Path like ('%sql%') - Scoped to within last 7 days AND sce.TimeGenerated > dateadd (dd, - 7 ,getutcdate())
Join managedtype mt with (nolock) on m.TargetMonitoringClassId = mt.ManagedTypeId Join MonitorView m with (nolock) on s.MonitorId = m.Id Join BaseManagedEntity bme with (nolock) on s.BasemanagedEntityId = bme.BasemanagedEntityId Join state s with (nolock) on sce.StateId = s.StateId Noisiest Monitor in the database – PER Object/Computer in the last 7 days: select distinct top 50 count (sce.StateId) as NumStateChanges,
#TOTALSPACES VS. UPDATE#
WHERE TimeGenerated 0 )ĮND END UPDATE dbo.PartitionAndGroomingSettingsĭataGroomedMaxTime = WHERE ObjectName = ' StateChangeEvent ' SELECT =, = IF ( 0 ) WHERE ObjectName = ' StateChangeEvent ' EXEC dbo.p_ConvertLocalTimeToUTC, OUTĮND SET = 1 - This is to update the settings table - with the max groomed data SELECT = MAX (TimeGenerated)īEGIN - Delete StateChangeEvents that are older than - We are doing this in chunks in separate transactions on - purpose: to avoid the transaction log to grow too large. SELECT = dbo.fn_GroomingThreshold(DaysToKeep, getdate ()) USE GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO BEGIN SET NOCOUNT ON DECLARE int DECLARE int DECLARE tinyint DECLARE datetime DECLARE datetime DECLARE datetime DECLARE datetime DECLARE int SET = getutcdate() WHERE ObjectName = ' StateChangeEvent ' SELECT COUNT ( * ) as ' Total StateChanges ' ,Ĭount ( CASE WHEN sce.TimeGenerated > dateadd (dd, - ,getutcdate()) THEN sce.TimeGenerated ELSE NULL END ) as ' within grooming retention ' ,Ĭount ( CASE WHEN sce.TimeGenerated grooming retention ' ,Ĭount ( CASE WHEN sce.TimeGenerated 30 days ' ,Ĭount ( CASE WHEN sce.TimeGenerated 90 days ' ,Ĭount ( CASE WHEN sce.TimeGenerated 365 days ' from StateChangeEvent sceĬleanup old statechanges for disabled monitors: To find out how old your StateChange data is: declare INT SELECT = DaysToKeep from PartitionAndGroomingSettings
#TOTALSPACES VS. PLUS#
(People have a lot of confusion here – this will show the DB and log file size, plus the used/free space in each) INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)ĭatabase Size and used space. GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1. INNER JOIN sys.internal_tables it ON (it. LEFT OUTER JOIN ( SELECT it.parent_id, SUM (ps.reserved_page_count) AS reserved,
SUM ( CASE WHEN (ps.index_id < 2 ) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)ĮLSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, SUM (ps.reserved_page_count) AS reserved, object_id, SUM ( CASE WHEN (ps.index_id < 2 ) THEN row_count ELSE 0 END ) AS , (row_number() over ( order by (a1.reserved + ISNULL (a4.reserved, 0 )) desc )) % 2 as l1,Ī3.name AS ' Schema ' FROM ( SELECT ps. I am putting this at the top, because I use it so much to find out what is taking up so much space in the OpsDB or DW SELECT TOP 1000ĬAST ((a1.reserved + ISNULL (a4.reserved, 0 )) * 8 / 1024.0 AS DECIMAL ( 10, 0 )) AS ' TotalSpace(MB) ' ,ĬAST (a1.data * 8 / 1024.0 AS DECIMAL ( 10, 0 )) AS ' DataSize(MB) ' ,ĬAST (( CASE WHEN (a1.used + ISNULL (a4.used, 0 )) > a1.data THEN (a1.used + ISNULL (a4.used, 0 )) - a1.data ELSE 0 END ) * 8 / 1024.0 AS DECIMAL ( 10, 0 )) AS ' IndexSize(MB) ' ,ĬAST (( CASE WHEN (a1.reserved + ISNULL (a4.reserved, 0 )) > a1.used THEN (a1.reserved + ISNULL (a4.reserved, 0 )) - a1.used ELSE 0 END ) * 8 / 1024.0 AS DECIMAL ( 10, 0 )) AS ' Unused(MB) ' ,
0 kommentar(er)
