DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
IF NOT EXISTS (SELECT * FROM StandardDataset WHERE DatasetId = '$Config/DatasetId$')
BEGIN TRY
INSERT INTO StandardDataset (
DatasetId
,SchemaName
,DefaultAggregationIntervalCount
,RawInsertTableCount
,StagingProcessorStoredProcedureName
,BlockingMaintenanceDailyStartTime
,BlockingMaintenanceDurationMinutes
)
VALUES
(
'$Config/DatasetId$'
,'SCDPM'
,$Config/DefaultAggregationIntervalCount$
,$Config/RawInsertTableCount$
,'SCDPM.DiskUtilizationProcessStaging'
,'$Config/BlockingMaintenanceDailyStartTime$'
,$Config/BlockingMaintenanceDurationMinutes$
)
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971001, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
GO
--------------------------------------------------------------------------------------
-- Create the StandardDatasetAggregation entries
--------------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
IF NOT EXISTS (SELECT * FROM StandardDatasetAggregation WHERE DatasetId = '$Config/DatasetId$')
BEGIN TRY
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
INSERT StandardDatasetAggregation
(
DatasetId
,AggregationTypeId
,AggregationIntervalDurationMinutes
,AggregationStartDelayMinutes
,BuildAggregationStoredProcedureName
,DeleteAggregationStoredProcedureName
,GroomStoredProcedureName
,IndexOptimizationIntervalMinutes
,MaxDataAgeDays
,GroomingIntervalMinutes
,MaxRowsToGroom
,LastGroomingDateTime
,DataFileGroupName
,IndexFileGroupName
)
SELECT
'$Config/DatasetId$'
,CASE AggregationType
WHEN 'Raw' THEN 0
WHEN 'Hourly' THEN 20
WHEN 'Daily' THEN 30
END
,CASE AggregationType
WHEN 'Raw' THEN 0
WHEN 'Hourly' THEN 60
WHEN 'Daily' THEN 60*24
END
,CASE AggregationType
WHEN 'Raw' THEN 1
WHEN 'Hourly' THEN 60
WHEN 'Daily' THEN 60*24
END
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE 'SCDPM.DiskUtilizationAggregate'
END
,
CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE 'SCDPM.DiskUtilizationAggregationDelete'
END
,'SCDPM.DiskUtilizationGroom'
,IndexOptimizationIntervalMinutes
,MaxDataAgeDays
,GroomingIntervalMinutes
,MaxRowsToGroom
,GETUTCDATE()
,DataFileGroupName
,IndexFileGroupName
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname'
,MaxTableRowCount int
,MaxTableSizeKb int
,AggregationStartDelayMinutes int
,MaxDataAgeDays int
,GroomingIntervalMinutes int
,MaxRowsToGroom int
,IndexOptimizationIntervalMinutes int
,DataFileGroupName nvarchar(256)
,IndexFileGroupName nvarchar(256)
,AggregationIntervalDurationMinutes int
)
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
GO
--------------------------------------------------------------------------------------
-- Define the tables and views
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE [name] = 'SCDPM')
BEGIN
EXECUTE('CREATE SCHEMA SCDPM')
END
GO
-- change schema ownership to allow index rebuild and reorganize
ALTER AUTHORIZATION ON SCHEMA::SCDPM TO OpsMgrWriter
GO
-- change schema alter to allow domain table index rebuild
GRANT ALTER ON SCHEMA::dbo TO OpsMgrWriter
GO
GRANT ALTER ON SCHEMA::SCDPM TO OpsMgrWriter
GO
-- grant create table permissions
GRANT CREATE TABLE TO OpsMgrWriter
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
BEGIN TRY
-- Insert definition for SCDPM.DiskUtilizationStaging table into StandardDatasetStagingArea.
BEGIN TRAN
IF EXISTS (SELECT * FROM StandardDatasetStagingArea WHERE DatasetId = '$Config/DatasetId$')
BEGIN
DELETE StandardDatasetStagingArea
WHERE DatasetId = '$Config/DatasetId$';
END
INSERT INTO StandardDatasetStagingArea
(
DatasetId
,StagingTableColumnDefinition
,MaxRowsToProcessPerTransactionCount
,BatchedProcessingSupportedInd
)
VALUES
(
'$Config/DatasetId$'
,
-- The 'LastAccessed' column will be updated on each run of ProcessStaging sproc
-- to let the sproc to process different recordset on each run - a FIFO queue mode.
-- Without this, the sproc may each time process the same record set of previously skipped records
-- and thus never get to others until first ones a groomed.
'DiskUtilizationRowId int NOT NULL IDENTITY(1, 1)
,DPMServerName varchar(256)
,SMStatsID uniqueidentifier
,DatasourceId uniqueidentifier
,DatasourceName varchar(max)
,ProductionServer varchar(max)
,ProtectionGroupId uniqueidentifier
,ProtectionGroupName varchar(max)
,StartDateTime datetime
,EndDateTime datetime
,DiskSpaceAllocated bigint
,ShadowCopyAllocatedSize bigint
,ShadowCopyUsedSize bigint
,ReplicaUsedSize bigint
,DiskSpaceUsed bigint
,FreeSpaceAvailable bigint
,CollocationEnabled bit
'
-- This defines how many records does ProcessStaging sproc process per each execution (every 1 minute).
,10000 -- Value based on experience in Datacenter Production environment
,1
)
COMMIT
-- Create the SCDPM.DiskUtilizationStaging table.
BEGIN TRAN
-- Drop table if it exists.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCDPM].[DiskUtilizationStaging]') AND type in (N'U'))
BEGIN
DROP TABLE [SCDPM].[DiskUtilizationStaging];
END
SET @DPMServerNameIndexGuid = '845D6BE9-C4F1-4667-8A69-A76E0E54E8A5'
SET @SMStatsIDIndexGuid = '782D74E5-9370-430D-BFF8-4D321E48B9A5'
SET @DataSourceIdIndexGuid = 'D985B010-C1DB-4346-A451-7CEBE7FDD265'
--------------------------------------------------------------------------------------
-- Allocate standard storage for the dataset.
--------------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
BEGIN TRY
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
WHILE EXISTS (SELECT *
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname')
WHERE AggregationType > @AggregationType
)
BEGIN
SELECT TOP 1 @AggregationType = AggregationType
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname')
WHERE AggregationType > @AggregationType
ORDER BY AggregationType
SET @Statement = 'EXEC StandardDatasetAllocateStorage @DatasetId = ''$Config/DatasetId$'', @AggregationTypeId='
+ CASE @AggregationType
WHEN 'Raw' THEN '0'
WHEN 'Hourly' THEN '20'
WHEN 'Daily' THEN '30'
END
EXECUTE (@Statement)
END
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971005, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCDPM].[DiskUtilizationProcessStaging]') AND type in (N'P'))
BEGIN
EXECUTE ('CREATE PROCEDURE [SCDPM].[DiskUtilizationProcessStaging] AS RETURN 1')
END
GO
ALTER PROCEDURE [SCDPM].[DiskUtilizationProcessStaging]
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
DECLARE
-- Configuration variables.
-- Maximum age of data to keep in table DiskUtilizationStaging.
@MaxDataAgeDays int
-- Maximum number of rows to process per single run of this stored procedure.
,@MaxRowsToProcess int
-- Diagnostic variables.
,@DebugLevel int
,@DebugMessage nvarchar(4000)
,@OperationStartedDateTime datetime
,@OperationDurationMs bigint
,@RowsDeleted int
,@CountTotalProcessedRecords int
-- Number of records successfully processed and submitted into table.
,@CountSubmittedRecords int
-- Number of records for which there no match was found in Alert table.
,@CountSkippedRecords int
-- Number of records with invalid content in 'Context' column.
,@CountInvalidRecords int
BEGIN TRY
-- Get the debug level for dataset.
SELECT @DebugLevel = DebugLevel ,
@SchemaName = SchemaName
FROM dbo.StandardDataset WHERE DatasetId = @DatasetId;
EXEC dbo.StandardDatasetGetInsertTableGuid @DatasetId, 0, @TableGuid OUTPUT
SET @TableGuid = REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
SELECT @BaseTableName = BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
SET @InsertTableName = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@BaseTableName + '_' + @TableGuid )
--
-- Groom the table DiskUtilizationStaging.
--
-- Get the grooming settings for table.
SELECT @MaxDataAgeDays = MaxDataAgeDays
FROM dbo.StandardDatasetAggregation WHERE DatasetId = @DatasetId AND AggregationTypeId = 0;
-- Delete the outdated records using the same grooming settings as for DiskUtilizationStaging table.
DELETE FROM SCDPM.DiskUtilizationStaging WHERE [StartDateTime] < DATEADD(day, - @MaxDataAgeDays, GETUTCDATE());
SET @RowsDeleted = @@ROWCOUNT;
IF((@DebugLevel > 2) AND (@RowsDeleted < 0))
-- Log a debug message.
BEGIN
SET @DebugMessage = 'Groomed table SCDPM.DiskUtilizationStaging by ' + CAST(@RowsDeleted as varchar(16)) + ' records.';
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
,@OperationDurationMs = @OperationDurationMs;
END
-- Log a debug message.
IF (@DebugLevel > 2)
BEGIN
SET @OperationStartedDateTime = GETDATE();
SET @OperationDurationMs = 0;
SET @DebugMessage = 'Started stage processing of SCDPM.DiskUtilizationStaging table.';
--Garbage collect
DELETE FROM [SCDPM].DiskUtilizationStaging WHERE StartDateTime < DATEADD(DAY, -30, GETUTCDATE())
--
-- Perform stage processing.
--
-- Get the configuration value for stage processing.
SELECT @MaxRowsToProcess = MaxRowsToProcessPerTransactionCount
FROM dbo.StandardDatasetStagingArea
WHERE DatasetId = @DatasetId;
if object_id('tempdb..#DiskUtilizationStagingBatch') is not null drop table #DiskUtilizationStagingBatch
-- Select only the earliest @MaxRowsToProcess records from DiskUtilizationStaging for processing.
-- This way the processing is balanced in order not to let this stored procedure to time out.
INSERT INTO #DiskUtilizationStagingBatch(
DiskUtilizationRowId
,DPMServerName
,SMStatsID
,DatasourceId
,DatasourceName
,ProductionServer
,ProtectionGroupID
,ProtectionGroupName
,StartDateTime
,EndDateTime
,DiskSpaceAllocated
,ShadowCopyAllocatedSize
,ShadowCopyUsedSize
,ReplicaUsedSize
,DiskSpaceUsed
,FreeSpaceAvailable
,CollocationEnabled
)
SELECT TOP (@MaxRowsToProcess)
DiskUtilizationRowId
,DPMServerName
,SMStatsID
,DatasourceId
,DatasourceName
,ProductionServer
,ProtectionGroupID
,ProtectionGroupName
,StartDateTime
,EndDateTime
,DiskSpaceAllocated
,ShadowCopyAllocatedSize
,ShadowCopyUsedSize
,ReplicaUsedSize
,DiskSpaceUsed
,FreeSpaceAvailable
,CollocationEnabled
FROM SCDPM.DiskUtilizationStaging AS A
ORDER BY DiskUtilizationRowId;
SET @Statement = 'INSERT INTO [SCDPM].DiskUtilization_' + CAST(@TableGuid AS varchar(300)) +
'(
DPMServerName
,SMStatsID
,DatasourceId
,DatasourceName
,ProductionServer
,ProtectionGroupID
,ProtectionGroupName
,StartDateTime
,EndDateTime
,DiskSpaceAllocated
,ShadowCopyAllocatedSize
,ShadowCopyUsedSize
,ReplicaUsedSize
,DiskSpaceUsed
,FreeSpaceAvailable
,CollocationEnabled
)
SELECT DPMServerName
,SMStatsID
,DatasourceId
,DatasourceName
,ProductionServer
,ProtectionGroupID
,ProtectionGroupName
,StartDateTime
,EndDateTime
,DiskSpaceAllocated
,ShadowCopyAllocatedSize
,ShadowCopyUsedSize
,ReplicaUsedSize
,DiskSpaceUsed
,FreeSpaceAvailable
,CollocationEnabled
FROM #DiskUtilizationStagingBatch B
WHERE NOT EXISTS (SELECT * FROM [SCDPM].vDiskUtilization WHERE SMStatsID = B.SMStatsID)'
EXECUTE (@Statement)
DELETE FROM [SCDPM].DiskUtilizationStaging
FROM [SCDPM].DiskUtilizationStaging AS staging -- Second FROM clause is used for filtering the records to be deleted.
INNER JOIN #DiskUtilizationStagingBatch AS batch ON staging.DiskUtilizationRowId = batch.DiskUtilizationRowId
COMMIT TRAN
-- Drop the temporary table.
DROP TABLE #DiskUtilizationStagingBatch;
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
GRANT EXECUTE ON [SCDPM].[DiskUtilizationProcessStaging] TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCDPM].[DiskUtilizationAggregate]') AND type in (N'P'))
BEGIN
EXECUTE ('CREATE PROCEDURE [SCDPM].[DiskUtilizationAggregate] AS RETURN 1')
END
GO
ALTER PROCEDURE SCDPM.DiskUtilizationAggregate
@DatasetId uniqueidentifier
,@AggregationTypeId int -- aggregration type: e.g. 20 = hourly
,@InsertTableGuid varchar(256) -- GUID of the aggregation table
,@IntervalStartDateTime datetime
,@IntervalEndDateTime datetime
AS
BEGIN
SET NOCOUNT ON
return 1
GRANT EXECUTE ON [SCDPM].[DiskUtilizationAggregate] TO OpsMgrWriter
END
GO
GRANT EXECUTE ON [SCDPM].[DiskUtilizationAggregate] TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCDPM].[DiskUtilizationAggregationDelete]') AND type in (N'P'))
BEGIN
EXECUTE ('CREATE PROCEDURE [SCDPM].[DiskUtilizationAggregationDelete] AS RETURN 1')
END
GO
ALTER PROCEDURE SCDPM.DiskUtilizationAggregationDelete
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@AggregationDateTime datetime
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
return 1
RETURN @RowsDeleted
END
GO
GRANT EXECUTE ON [SCDPM].[DiskUtilizationAggregationDelete] TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCDPM].[DiskUtilizationGroom]') AND type in (N'P'))
BEGIN
EXECUTE ('CREATE PROCEDURE [SCDPM].[DiskUtilizationGroom] AS RETURN 1')
END
GO
ALTER PROCEDURE SCDPM.DiskUtilizationGroom
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@CutoffDateTime datetime
,@MaxRowsToGroom int
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
SELECT @TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
SET @Statement = 'DELETE TOP (' + CAST(@MaxRowsToGroom AS varchar(15)) + ')'
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
+ ' WHERE (StartDateTime < ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''')'
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT
GRANT EXECUTE ON [SCDPM].[DiskUtilizationGroom] TO OpsMgrWriter
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to groom data for DiskUtilization data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
GRANT EXECUTE ON [SCDPM].[DiskUtilizationGroom] TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCDPM].[DiskUtilizationInsert]') AND type in (N'P'))
BEGIN
EXECUTE ('CREATE PROCEDURE [SCDPM].[DiskUtilizationInsert] AS RETURN 1')
END
GO
ALTER PROCEDURE SCDPM.DiskUtilizationInsert
@ManagementGroupId uniqueidentifier
,@DatasetId uniqueidentifier
,@DataXml nvarchar(max)
AS
BEGIN
SET NOCOUNT ON
DECLARE @CheckExecResult int
,@ManagementGroupRowId int
-- get the management group
SELECT @ManagementGroupRowId = ManagementGroupRowId
FROM vManagementGroup
WHERE (ManagementGroupGuid = @ManagementGroupId) AND (WriterLoginName = SUSER_Name())
DECLARE
@CurentTime datetime
,@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
,@ResultingState int
SET @ErrorInd = 0
SET @ResultingState = 1
DECLARE
@ExecResult int
,@XmlDocHandle int
BEGIN TRY
--Garbage collect
DELETE FROM [SCDPM].DiskUtilizationStaging WHERE StartDateTime < DATEADD(DAY, -30, GETUTCDATE())
-- Add it to SCDPM.DiskUtilizationStaging
-- Delete duplicates
DELETE [SCDPM].DiskUtilizationStaging FROM [SCDPM].DiskUtilizationStaging A WHERE DiskUtilizationRowId NOT IN (
SELECT MIN(DiskUtilizationRowId) FROM [SCDPM].DiskUtilizationStaging B
WHERE A.SMStatsID = B.SMStatsID
)
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971009, @AdjustedErrorSeverity, @ResultingState
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON SCDPM.DiskUtilizationInsert TO OpsMgrWriter
GO
-- Create custom views over ManagedEntity
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCDPM].[vDatasource]') AND type in (N'V'))
BEGIN
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'create view SCDPM.vDatasource As
select
ME.DisplayName as DatasourceName
,Prop.PropertyXml.value(''(/Root/Property[@Guid[. = sql:column("p1.PropertyGuid")]]/text())[1]'',''nvarchar(256)'') as DatasourceType
,Prop.PropertyXml.value(''(/Root/Property[@Guid[. = sql:column("p2.PropertyGuid")]]/text())[1]'',''nvarchar(256)'') as DatasourceId
,Prop.PropertyXml.value(''(/Root/Property[@Guid[. = sql:column("p3.PropertyGuid")]]/text())[1]'',''nvarchar(256)'') as DPMServerName
,Prop.PropertyXml.value(''(/Root/Property[@Guid[. = sql:column("p4.PropertyGuid")]]/text())[1]'',''nvarchar(256)'') as IsSecondaryProtected
,Prop.PropertyXml.value(''(/Root/Property[@Guid[. = sql:column("p5.PropertyGuid")]]/text())[1]'',''nvarchar(256)'') as ProtectionGroupName
from vManagedEntity ME with (nolock)
join vManagedEntityType MET with (nolock) on ME.ManagedEntityTypeRowId = MET.ManagedEntityTypeRowId
join vManagedEntityProperty Prop with (nolock) on Prop.ManagedEntityRowId = ME.ManagedEntityRowId
join vManagedEntityTypeProperty p1 with (nolock) on ME.ManagedEntityTypeRowId = p1.ManagedEntityTypeRowId and p1.PropertySystemName = ''DatasourceType''
join vManagedEntityTypeProperty p2 with (nolock) on ME.ManagedEntityTypeRowId = p2.ManagedEntityTypeRowId and p2.PropertySystemName = ''DPMObjectID''
join vManagedEntityTypeProperty p3 with (nolock) on ME.ManagedEntityTypeRowId = p3.ManagedEntityTypeRowId and p3.PropertySystemName = ''DPMServerName''
join vManagedEntityTypeProperty p4 with (nolock) on ME.ManagedEntityTypeRowId = p4.ManagedEntityTypeRowId and p4.PropertySystemName = ''IsSecondaryProtected''
join vManagedEntityTypeProperty p5 with (nolock) on ME.ManagedEntityTypeRowId = p5.ManagedEntityTypeRowId and p5.PropertySystemName = ''PGName''
where ManagedEntityTypeSystemName = ''Microsoft.SystemCenter.DataProtectionManager.2011.Library.DataSource''
and Prop.ToDateTime is NULL'
EXEC sp_executesql @sql;
END
GO
GRANT SELECT ON SCDPM.vDatasource TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCDPM].[vProtectionGroup]') AND type in (N'V'))
BEGIN
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'create View SCDPM.vProtectionGroup As
select
ME.DisplayName as ProtectionGroupName
,Prop.PropertyXml.value(''(/Root/Property[@Guid[. = sql:column("p1.PropertyGuid")]]/text())[1]'',''nvarchar(256)'') as ProtectionGroupId
,Prop.PropertyXml.value(''(/Root/Property[@Guid[. = sql:column("p2.PropertyGuid")]]/text())[1]'',''nvarchar(256)'') as DPMServerName
,Prop.PropertyXml.value(''(/Root/Property[@Guid[. = sql:column("p3.PropertyGuid")]]/text())[1]'',''nvarchar(256)'') as IsCollocated
,Prop.PropertyXml.value(''(/Root/Property[@Guid[. = sql:column("p4.PropertyGuid")]]/text())[1]'',''nvarchar(256)'') as ProtectionType
from vManagedEntity ME with (nolock)
join vManagedEntityType MET with (nolock) on ME.ManagedEntityTypeRowId = MET.ManagedEntityTypeRowId
join vManagedEntityProperty Prop with (nolock) on Prop.ManagedEntityRowId = ME.ManagedEntityRowId
join vManagedEntityTypeProperty p1 with (nolock) on ME.ManagedEntityTypeRowId = p1.ManagedEntityTypeRowId and p1.PropertySystemName = ''DPMObjectID''
join vManagedEntityTypeProperty p2 with (nolock) on ME.ManagedEntityTypeRowId = p2.ManagedEntityTypeRowId and p2.PropertySystemName = ''DPMServerName''
join vManagedEntityTypeProperty p3 with (nolock) on ME.ManagedEntityTypeRowId = p3.ManagedEntityTypeRowId and p3.PropertySystemName = ''IsCollocated''
join vManagedEntityTypeProperty p4 with (nolock) on ME.ManagedEntityTypeRowId = p4.ManagedEntityTypeRowId and p4.PropertySystemName = ''ProtectionType''
where ManagedEntityTypeSystemName = ''Microsoft.SystemCenter.DataProtectionManager.2011.Library.ProtectionGroup''
and Prop.ToDateTime is NULL'
EXEC sp_executesql @sql;
END
GO
GRANT SELECT ON SCDPM.vProtectionGroup TO OpsMgrReader
GO</Install> <Uninstall> -- drop SPs
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCDPM].[DiskUtilizationAggregate]') AND type in (N'P'))
BEGIN
EXECUTE ('DROP PROCEDURE [SCDPM].[DiskUtilizationAggregate]')
END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCDPM].[DiskUtilizationAggregationDelete]') AND type in (N'P'))
BEGIN
EXECUTE ('DROP PROCEDURE [SCDPM].[DiskUtilizationAggregationDelete]')
END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCDPM].[DiskUtilizationGroom]') AND type in (N'P'))
BEGIN
EXECUTE ('DROP PROCEDURE [SCDPM].[DiskUtilizationGroom]')
END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCDPM].[DiskUtilizationInsert]') AND type in (N'P'))
BEGIN
EXECUTE ('DROP PROCEDURE [SCDPM].[DiskUtilizationInsert]')
END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCDPM].[DiskUtilizationProcessStaging]') AND type in (N'P'))
BEGIN
EXECUTE ('DROP PROCEDURE [SCDPM].[DiskUtilizationProcessStaging]')
END
-- delete std dataset related data
EXEC StandardDatasetDelete '$Config/DatasetId$'
-- Delete custom views over ManagedEntity
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCDPM].[vDatasource]') AND type in (N'V'))
BEGIN
EXECUTE ('drop view [SCDPM].[vDatasource]')
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCDPM].[vProtectionGroup]') AND type in (N'V'))
BEGIN
EXECUTE ('drop view [SCDPM].[vProtectionGroup]')
END
GO</Uninstall> <Upgrade>
SET NOCOUNT ON
GO
ALTER PROCEDURE [SCDPM].[DiskUtilizationProcessStaging]
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
DECLARE
-- Configuration variables.
-- Maximum age of data to keep in table DiskUtilizationStaging.
@MaxDataAgeDays int
-- Maximum number of rows to process per single run of this stored procedure.
,@MaxRowsToProcess int
-- Diagnostic variables.
,@DebugLevel int
,@DebugMessage nvarchar(4000)
,@OperationStartedDateTime datetime
,@OperationDurationMs bigint
,@RowsDeleted int
,@CountTotalProcessedRecords int
-- Number of records successfully processed and submitted into table.
,@CountSubmittedRecords int
-- Number of records for which there no match was found in Alert table.
,@CountSkippedRecords int
-- Number of records with invalid content in 'Context' column.
,@CountInvalidRecords int
BEGIN TRY
-- Get the debug level for dataset.
SELECT @DebugLevel = DebugLevel ,
@SchemaName = SchemaName
FROM dbo.StandardDataset WHERE DatasetId = @DatasetId;
EXEC dbo.StandardDatasetGetInsertTableGuid @DatasetId, 0, @TableGuid OUTPUT
SET @TableGuid = REPLACE(CAST(@TableGuid AS varchar(50)), '-', '')
SELECT @BaseTableName = BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
SET @InsertTableName = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@BaseTableName + '_' + @TableGuid )
--
-- Groom the table DiskUtilizationStaging.
--
-- Get the grooming settings for table.
SELECT @MaxDataAgeDays = MaxDataAgeDays
FROM dbo.StandardDatasetAggregation WHERE DatasetId = @DatasetId AND AggregationTypeId = 0;
-- Delete the outdated records using the same grooming settings as for DiskUtilizationStaging table.
DELETE FROM SCDPM.DiskUtilizationStaging WHERE [StartDateTime] < DATEADD(day, - @MaxDataAgeDays, GETUTCDATE());
SET @RowsDeleted = @@ROWCOUNT;
IF((@DebugLevel > 2) AND (@RowsDeleted < 0))
-- Log a debug message.
BEGIN
SET @DebugMessage = 'Groomed table SCDPM.DiskUtilizationStaging by ' + CAST(@RowsDeleted as varchar(16)) + ' records.';
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = 3
,@MessageText = @DebugMessage
,@OperationDurationMs = @OperationDurationMs;
END
-- Log a debug message.
IF (@DebugLevel > 2)
BEGIN
SET @OperationStartedDateTime = GETDATE();
SET @OperationDurationMs = 0;
SET @DebugMessage = 'Started stage processing of SCDPM.DiskUtilizationStaging table.';
--Garbage collect
DELETE FROM [SCDPM].DiskUtilizationStaging WHERE StartDateTime < DATEADD(DAY, -30, GETUTCDATE())
--
-- Perform stage processing.
--
-- Get the configuration value for stage processing.
SELECT @MaxRowsToProcess = MaxRowsToProcessPerTransactionCount
FROM dbo.StandardDatasetStagingArea
WHERE DatasetId = @DatasetId;
if object_id('tempdb..#DiskUtilizationStagingBatch') is not null drop table #DiskUtilizationStagingBatch
-- Select only the earliest @MaxRowsToProcess records from DiskUtilizationStaging for processing.
-- This way the processing is balanced in order not to let this stored procedure to time out.
INSERT INTO #DiskUtilizationStagingBatch(
DiskUtilizationRowId
,DPMServerName
,SMStatsID
,DatasourceId
,DatasourceName
,ProductionServer
,ProtectionGroupID
,ProtectionGroupName
,StartDateTime
,EndDateTime
,DiskSpaceAllocated
,ShadowCopyAllocatedSize
,ShadowCopyUsedSize
,ReplicaUsedSize
,DiskSpaceUsed
,FreeSpaceAvailable
,CollocationEnabled
)
SELECT TOP (@MaxRowsToProcess)
DiskUtilizationRowId
,DPMServerName
,SMStatsID
,DatasourceId
,DatasourceName
,ProductionServer
,ProtectionGroupID
,ProtectionGroupName
,StartDateTime
,EndDateTime
,DiskSpaceAllocated
,ShadowCopyAllocatedSize
,ShadowCopyUsedSize
,ReplicaUsedSize
,DiskSpaceUsed
,FreeSpaceAvailable
,CollocationEnabled
FROM SCDPM.DiskUtilizationStaging AS A
ORDER BY DiskUtilizationRowId;
SET @Statement = 'INSERT INTO [SCDPM].DiskUtilization_' + CAST(@TableGuid AS varchar(300)) +
'(
DPMServerName
,SMStatsID
,DatasourceId
,DatasourceName
,ProductionServer
,ProtectionGroupID
,ProtectionGroupName
,StartDateTime
,EndDateTime
,DiskSpaceAllocated
,ShadowCopyAllocatedSize
,ShadowCopyUsedSize
,ReplicaUsedSize
,DiskSpaceUsed
,FreeSpaceAvailable
,CollocationEnabled
)
SELECT DPMServerName
,SMStatsID
,DatasourceId
,DatasourceName
,ProductionServer
,ProtectionGroupID
,ProtectionGroupName
,StartDateTime
,EndDateTime
,DiskSpaceAllocated
,ShadowCopyAllocatedSize
,ShadowCopyUsedSize
,ReplicaUsedSize
,DiskSpaceUsed
,FreeSpaceAvailable
,CollocationEnabled
FROM #DiskUtilizationStagingBatch B
WHERE NOT EXISTS (SELECT * FROM [SCDPM].vDiskUtilization WHERE SMStatsID = B.SMStatsID)'
EXECUTE (@Statement)
DELETE FROM [SCDPM].DiskUtilizationStaging
FROM [SCDPM].DiskUtilizationStaging AS staging -- Second FROM clause is used for filtering the records to be deleted.
INNER JOIN #DiskUtilizationStagingBatch AS batch ON staging.DiskUtilizationRowId = batch.DiskUtilizationRowId
COMMIT TRAN
-- Drop the temporary table.
DROP TABLE #DiskUtilizationStagingBatch;
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
ALTER PROCEDURE SCDPM.DiskUtilizationInsert
@ManagementGroupId uniqueidentifier
,@DatasetId uniqueidentifier
,@DataXml nvarchar(max)
AS
BEGIN
SET NOCOUNT ON
DECLARE @CheckExecResult int
,@ManagementGroupRowId int
-- get the management group
SELECT @ManagementGroupRowId = ManagementGroupRowId
FROM vManagementGroup
WHERE (ManagementGroupGuid = @ManagementGroupId) AND (WriterLoginName = SUSER_Name())
DECLARE
@CurentTime datetime
,@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
,@ResultingState int
SET @ErrorInd = 0
SET @ResultingState = 1
DECLARE
@ExecResult int
,@XmlDocHandle int
BEGIN TRY
--Garbage collect
DELETE FROM [SCDPM].DiskUtilizationStaging WHERE StartDateTime < DATEADD(DAY, -30, GETUTCDATE())
-- Add it to SCDPM.DiskUtilizationStaging
-- Delete duplicates
DELETE [SCDPM].DiskUtilizationStaging FROM [SCDPM].DiskUtilizationStaging A WHERE DiskUtilizationRowId NOT IN (
SELECT MIN(DiskUtilizationRowId) FROM [SCDPM].DiskUtilizationStaging B
WHERE A.SMStatsID = B.SMStatsID
)
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
Home