SCOM 2007 R2,ACS grooming not working in production

-- Check your partitions:
Use OperationsManagerAC
SELECT * FROM dtPartition Order By PartitionCloseTime

Solution

===========
1. There is a a DBDeletePartition.sql in the C:\windows\system32\security\Adtserver folder on the Collection server.

Replace the !g! with the GUID of the partition you want to delete, and run this on the sql server.

NOTE: Do not modify the copy in the adtserver folder directly. It is used by ACS only.

 

+ renamed !g! with partition name which needs to groomed manually.

===============================================

/************************************************************

*

* Partition !g!

*

************************************************************/

/************************************************************

*

* Call user defined stored procedure.

*

************************************************************/

if exists (select * from dbo.sysobjects

            where id = object_id(N'Delete') and

            objectproperty(id, N'IsProcedure') = 1)

    execute spOnDeletePartition N'!g!'

go

/************************************************************

*

* Drop partitioned tables.

*

************************************************************/

if exists (select * from dbo.sysobjects

            where id = object_id(N'dvAll_!g!') and

            objectproperty(id, N'IsView') = 1)

    drop view dvAll_!g!

go

if exists (select * from dbo.sysobjects

            where id = object_id(N'dvAll5_!g!') and

            objectproperty(id, N'IsView') = 1)

    drop view dvAll5_!g!

go

if exists (select * from dbo.sysobjects

            where id = object_id(N'dvHeader_!g!') and

            objectproperty(id, N'IsView') = 1)

    drop view dvHeader_!g!

go

if exists (select * from dbo.sysobjects

            where id = object_id(N'dtEventData_!g!') and

            objectproperty(id, N'IsUserTable') = 1)

    drop table dtEventData_!g!

go

if exists (select * from dbo.sysobjects

            where id = object_id(N'dtEvent_!g!') and

            objectproperty(id, N'IsUserTable') = 1)

    drop table dtEvent_!g!

go

if exists (select * from dbo.sysobjects

            where id = object_id(N'dtPrincipal_!g!') and

            objectproperty(id, N'IsUserTable') = 1)

    drop table dtPrincipal_!g!

go

if exists (select * from dbo.sysobjects

            where id = object_id(N'dtString_!g!') and

            objectproperty(id, N'IsUserTable') = 1)

    drop table dtString_!g!

go

/************************************************************

*

* Delete entry from dtPartition for partition !g!

*

************************************************************/

delete from dtPartition where PartitionId = N'!g!'

go

/************************************************************

*

* Create or update dvAll, the view across all partition views

*

************************************************************/

declare @iIsFirst int

declare @vchStmt nvarchar(max)

declare @vchPartitionId nchar(36)

declare cPartition cursor for

select top 42 PartitionId from dtPartition order by PartitionCloseTime desc

set @iIsFirst = 1

set @vchStmt = N'create view [AdtServer].dvAll as'

open cPartition

fetch next from cPartition into @vchPartitionId

while @@fetch_status = 0

begin

    if @iIsFirst = 0

        set @vchStmt =  @vchStmt + N' union all'

    set @vchStmt =  @vchStmt + N' select * from dvAll_' + @vchPartitionId

    set @iIsFirst = 0

    fetch next from cPartition into @vchPartitionId

end

close cPartition

deallocate cPartition

if exists (

    select * from dbo.sysobjects where

        id = object_id(N'[AdtServer].dvAll') and

        objectproperty(id, N'IsView') = 1)

    drop view [AdtServer].dvAll

exec (@vchStmt)

go

/************************************************************

*

* Create or update dvAll5, the view across all partition views limited to the first 5 strings

*

************************************************************/

declare @iIsFirst int

declare @vchStmt nvarchar(max)

declare @vchPartitionId nchar(36)

declare cPartition cursor for

select top 42 PartitionId from dtPartition order by PartitionCloseTime desc

set @iIsFirst = 1

set @vchStmt = N'create view [AdtServer].dvAll5 as'

open cPartition

fetch next from cPartition into @vchPartitionId

while @@fetch_status = 0

begin

    if @iIsFirst = 0

        set @vchStmt =  @vchStmt + N' union all'

    set @vchStmt =  @vchStmt + N' select * from dvAll5_' + @vchPartitionId

    set @iIsFirst = 0

    fetch next from cPartition into @vchPartitionId

end

close cPartition

deallocate cPartition

if exists (

    select * from dbo.sysobjects where

        id = object_id(N'[AdtServer].dvAll5') and

        objectproperty(id, N'IsView') = 1)

    drop view [AdtServer].dvAll5

exec (@vchStmt)

go

/************************************************************

*

* Create or update dvHeader, the view across all partition views with no dtstring joins

*

************************************************************/

declare @iIsFirst int

declare @vchStmt nvarchar(max)

declare @vchPartitionId nchar(36)

declare cPartition cursor for

select top 42 PartitionId from dtPartition order by PartitionCloseTime desc

set @iIsFirst = 1

set @vchStmt = N'create view [AdtServer].dvHeader as'

open cPartition

fetch next from cPartition into @vchPartitionId

while @@fetch_status = 0

begin

    if @iIsFirst = 0

        set @vchStmt =  @vchStmt + N' union all'

    set @vchStmt =  @vchStmt + N' select * from dvHeader_' + @vchPartitionId

    set @iIsFirst = 0

    fetch next from cPartition into @vchPartitionId

end

close cPartition

deallocate cPartition

if exists (

    select * from dbo.sysobjects where

        id = object_id(N'[AdtServer].dvHeader') and

        objectproperty(id, N'IsView') = 1)

    drop view [AdtServer].dvHeader

exec (@vchStmt)

go

===============================================

2. Shrink DB ,Please run the following command:
 
use operationsmanagerAC
go
dbcc shrinkfile (fiAuditData)
go