Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



dbo.sp_sysutility_ucp_calculate_dac_health

  No additional text.


Syntax
CREATE PROCEDURE dbo.sp_sysutility_ucp_calculate_dac_health
   @new_set_number INT
WITH EXECUTE AS OWNER
AS
BEGIN
    -- Compute dac filegroup/log files health state
    EXEC msdb.dbo.sp_sysutility_ucp_calculate_dac_file_space_health @new_set_number;
    -- Compute dac health state
	
    -- Insert new records
    SELECT dd.dac_server_instance_name
        , dd.dac_name
        , SUM(CASE WHEN hs.health_state = 2 THEN 1 ELSE 0 END) AS under_utilized_count
        , SUM(CASE WHEN hs.health_state = 3 THEN 1 ELSE 0 END) AS over_utilized_count
    INTO #dac_volume_file_space_utilization
    FROM msdb.dbo.sysutility_ucp_deployed_dacs AS dd
    INNER JOIN msdb.dbo.sysutility_ucp_mi_volume_space_health_internal AS hs
        ON hs.server_instance_name = dd.dac_server_instance_name
    INNER JOIN (
        SELECT server_instance_name, database_name, volume_device_id FROM sysutility_ucp_datafiles
        UNION ALL
        SELECT server_instance_name, database_name, volume_device_id FROM sysutility_ucp_logfiles
    ) AS df
        ON df.volume_device_id = hs.volume_device_id
        AND dd.dac_server_instance_name = df.server_instance_name
        AND dd.dac_name = df.database_name
    WHERE hs.set_number = @new_set_number
    GROUP BY dd.dac_server_instance_name, dd.dac_name;
			
    SELECT dd.dac_server_instance_name
        , dd.dac_name
        , SUM(CASE WHEN hs.health_state = 2 THEN 1 ELSE 0 END) AS under_utilized_count
        , SUM(CASE WHEN hs.health_state = 3 THEN 1 ELSE 0 END) AS over_utilized_count
    INTO #dac_computer_cpu_utilization
    FROM msdb.dbo.sysutility_ucp_computer_cpu_health_internal AS hs
    INNER JOIN msdb.dbo.sysutility_ucp_deployed_dacs AS dd
        ON hs.physical_server_name = dd.dac_physical_server_name
    WHERE hs.set_number = @new_set_number
    GROUP BY dd.dac_server_instance_name, dd.dac_name;
    SELECT hs.dac_server_instance_name
        , hs.dac_name
        , SUM(CASE WHEN health_state.val = 2 THEN 1 ELSE 0 END) AS under_utilized_count
        , SUM(CASE WHEN health_state.val = 3 THEN 1 ELSE 0 END) AS over_utilized_count
    INTO #dac_file_space_utilization
    FROM msdb.dbo.sysutility_ucp_dac_file_space_health_internal hs
    CROSS APPLY dbo.fn_sysutility_ucp_get_aggregated_health(hs.over_utilized_count, hs.under_utilized_count) health_state
    WHERE hs.set_number = @new_set_number
    GROUP BY hs.dac_server_instance_name, hs.dac_name;
    INSERT INTO msdb.dbo.sysutility_ucp_dac_health_internal(dac_name, dac_server_instance_name, set_number
	       , processing_time
	       , is_volume_space_over_utilized
	       , is_volume_space_under_utilized
	       , is_computer_processor_over_utilized
	       , is_computer_processor_under_utilized
	       , is_file_space_over_utilized
	       , is_file_space_under_utilized
	       , is_dac_processor_over_utilized
	       , is_dac_processor_under_utilized
	       , is_policy_overridden)
    SELECT dd.dac_name
        , dd.dac_server_instance_name
        , @new_set_number
	    , dd.dac_processing_time
	    , vu.over_utilized_count AS dac_volume_space_over_utilized_count
	    , vu.under_utilized_count AS dac_volume_space_under_utilized_count
	    , cu.over_utilized_count AS dac_computer_cpu_over_utilized_count
	    , cu.under_utilized_count AS dac_computer_cpu_under_utilized_count
        , su.over_utilized_count AS dac_file_space_over_utilized_count
        , su.under_utilized_count AS dac_file_space_under_utilized_count
	    , ISNULL(du.over_utilized_count ,0) AS dac_cpu_over_utilized_count
	    , ISNULL(du.under_utilized_count ,0) AS dac_cpu_under_utilized_count
	    , pt.is_policy_overridden
    FROM msdb.dbo.sysutility_ucp_deployed_dacs dd
    LEFT JOIN msdb.dbo.sysutility_ucp_dac_cpu_utilization du
        ON dd.dac_name = du.dac_name AND dd.dac_server_instance_name = du.dac_server_instance_name
    INNER JOIN #dac_volume_file_space_utilization AS vu
        ON dd.dac_name = vu.dac_name AND dd.dac_server_instance_name = vu.dac_server_instance_name
    INNER JOIN #dac_computer_cpu_utilization AS cu
        ON dd.dac_name = cu.dac_name AND dd.dac_server_instance_name = cu.dac_server_instance_name
    INNER JOIN #dac_file_space_utilization AS su
        ON dd.dac_name = su.dac_name AND dd.dac_server_instance_name = su.dac_server_instance_name
    INNER JOIN msdb.dbo.sysutility_ucp_dac_policy_type pt
        ON dd.dac_name = pt.dac_name AND dd.dac_server_instance_name = pt.dac_server_instance_name;
END
 
Last revision
See also

  sp_sysutility_ucp_calculate_health (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash