-- Check your
partitions:
Use OperationsManagerAC
SELECT * FROM dtPartition Order By PartitionCloseTime
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.
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
===============================================
use operationsmanagerAC
go
dbcc shrinkfile (fiAuditData)
go
