IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbl_DPM_BackupEvents_Stage' AND TABLE_SCHEMA = 'DPMBackup')
BEGIN
CREATE TABLE DPMBackup.tbl_DPM_BackupEvents_Stage
(
DatasetId uniqueidentifier NOT NULL
,ManagementGroupGuid uniqueidentifier NOT NULL
,DatasourceId uniqueidentifier NOT NULL
,ReferentialDatasourceId uniqueidentifier NULL
,TaskId uniqueidentifier NOT NULL
,BackupTime datetime NOT NULL
,BackupResult nvarchar(30) NOT NULL
,BackupType nvarchar(30) NOT NULL
,FailureErrorCode int NULL
,ManagedEntityRowId int NULL
,InsertReadyInd AS (ISNULL(ManagedEntityRowId, 0))
,DWCreatedDateTime datetime NOT NULL DEFAULT(GETUTCDATE())
)
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON DPMBackup.tbl_DPM_BackupEvents_Stage TO OpsMgrWriter
GO
-- create [DateTime] field indexes for all aggregation levels
DECLARE
@AggregationTypeId int
,@Statement nvarchar(max)
,@IndeGuid uniqueidentifier
SET @AggregationTypeId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregation
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
)
BEGIN
SELECT @IndeGuid = NEWID()
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DPMBackupEventProcessStaging')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DPMBackupEventProcessStaging AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DPMBackupEventProcessStaging
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- groom records that are > month old from staging.
DELETE DPMBackup.tbl_DPM_BackupEvents_Stage WITH (TABLOCK)
WHERE (DWCreatedDateTime < DATEADD(month, -1, GETUTCDATE()))
-- process events in staging setting ME id if it is not yet determined
UPDATE DPMBackupStage
SET DPMBackupStage.ManagedEntityRowId = me.ManagedEntityRowId
FROM DPMBackup.tbl_DPM_BackupEvents_Stage DPMBackupStage
JOIN vManagementGroup mg ON (DPMBackupStage.ManagementGroupGuid = mg.ManagementGroupGuid)
JOIN vManagedEntity me ON ((me.Name = CAST(DPMBackupStage.DatasourceId AS nvarchar(70))) AND (mg.ManagementGroupRowId = me.ManagementGroupRowId))
WHERE (DPMBackupStage.DatasetId = @DatasetId)
AND (DPMBackupStage.InsertReadyInd = 0)
BEGIN TRAN
-- lock table map to ensure we insert into table opened for insertion
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + '_TableMap'
-- remove rows from staging
DELETE DPMBackup.tbl_DPM_BackupEvents_Stage WITH (TABLOCK)
WHERE (DatasetId = @DatasetId)
AND (InsertReadyInd > 0)
IF (@DebugLevel > 3)
BEGIN
SET @MessageText = 'Finished inserting DPM backup events into ' + @InsertTableName
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N'Failed to process data in the state data set staging area. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DPMBackupGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DPMBackupGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DPMBackupGroom
@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
-- there are no dependent tables in state data set
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 (DWCreatedDateTime < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT
-- groom staging of data for rejected MGs
DELETE DPMBackup.tbl_DPM_BackupEvents_Stage
WHERE ManagementGroupGuid NOT IN (SELECT ManagementGroupGuid
FROM ManagementGroup mg
WHERE (ConnectConfirmedDateTime IS NOT NULL)
AND (ConnectRejectedInd = 0)
)
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 state data set. Error ' + CAST(@ErrorNumber AS varchar(15))
+ ', Procedure ' + @ErrorProcedure
+ ', Line ' + CAST(@ErrorLine AS varchar(15))
+ ', Message: '+ @ErrorMessageText
Home
PTG