This has been sitting on my hard drive for a long time. Long story short, the report I posted at Permanent Link to Audit Collection Services Database Partitions Size Report had a couple of bugs:
- it did not consider the size of the dtString_XXX tables but only the size of dtEvent_XXX tables – this would still give you an idea of the trends, but it could lead to quite different SIZE calculations
- the query was failing on some instances that have been installed with the wrong (unsupported) Collation settings.
I fixed both bugs, but I don’t have a machine with SQL 2005 and Visual Studio 2005 anymore… so I can’t rebuild my report – but I don’t want to distribute one that only works on SQL 2008 because I know that SQL2005 is still out there. This is partially the reason that held this post back.
Without waiting so much longer, therefore, I decided I’ll just give you the fixed query. Enjoy 
--Query to get the Partition Table
--for each partition we launch the sp_spaceused stored procedure to determine the size and other info
--partition list
select PartitionId,Status,PartitionStartTime,PartitionCloseTime
into #t1
from dbo.dtPartition with (nolock)
order by PartitionStartTime Desc
--sp_spaceused holder table for dtEvent
create table #t2 (
PartitionId nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
rows nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
reserved nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
data nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
index_size nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
unused nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS
)
--sp_spaceused holder table for dtString
create table #t3 (
PartitionId nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
rows nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
reserved nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
data nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
index_size nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
unused nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS
)
set nocount on
--vars used for building Partition GUID and main table name
declare @partGUID nvarchar(MAX)
declare @tblName nvarchar(MAX)
declare @tblNameComplete nvarchar(MAX)
declare @schema nvarchar(MAX)
DECLARE @vQuery NVARCHAR(MAX)
--cursor
declare c cursor for
select PartitionID from #t1
open c
fetch next from c into @partGUID
--start cursor usage
while @@FETCH_STATUS = 0
begin
--tblName - first usage for dtEvent
set @tblName = 'dtEvent_' + @partGUID
--retrieve the schema name
SET @vQuery = 'SELECT @dbschema = TABLE_SCHEMA from INFORMATION_SCHEMA.tables where TABLE_NAME = ''' + @tblName + ''''
EXEC sp_executesql @vQuery,N'@dbschema nvarchar(max) out, @dbtblName nvarchar(max)',@schema out, @tblname
--tblNameComplete
set @tblNameComplete = @schema + '.' + @tblName
INSERT #t2
EXEC sp_spaceused @tblNameComplete
--tblName - second usage for dtString
set @tblName = 'dtString_' + @partGUID
--retrieve the schema name
SET @vQuery = 'SELECT @dbschema = TABLE_SCHEMA from INFORMATION_SCHEMA.tables where TABLE_NAME = ''' + @tblName + ''''
EXEC sp_executesql @vQuery,N'@dbschema nvarchar(max) out, @dbtblName nvarchar(max)',@schema out, @tblname
--tblNameComplete
set @tblNameComplete = @schema + '.' + @tblName
INSERT #t3
EXEC sp_spaceused @tblNameComplete
fetch next from c into @partGUID
end
close c
deallocate c
--select * from #t2
--select * from #t3
--results
select #t1.PartitionId,
#t1.Status,
#t1.PartitionStartTime,
#t1.PartitionCloseTime,
#t2.rows,
(CAST(LEFT(#t2.reserved,LEN(#t2.reserved)-3) AS NUMERIC(18,0)) + CAST(LEFT(#t2.reserved,LEN(#t2.reserved)-3) AS NUMERIC(18,0))) as 'reservedKB',
(CAST(LEFT(#t2.data,LEN(#t2.data)-3) AS NUMERIC(18,0)) + CAST(LEFT(#t3.data,LEN(#t3.data)-3) AS NUMERIC(18,0)))as 'dataKB',
(CAST(LEFT(#t2.index_size,LEN(#t2.index_size)-3) AS NUMERIC(18,0)) + CAST(LEFT(#t3.index_size,LEN(#t3.index_size)-3) AS NUMERIC(18,0))) as 'indexKB',
(CAST(LEFT(#t2.unused,LEN(#t2.unused)-3) AS NUMERIC(18,0)) + CAST(LEFT(#t3.unused,LEN(#t3.unused)-3) AS NUMERIC(18,0))) as 'unusedKB'
from #t1
join #t2
on #t2.PartitionId = ('dtEvent_' + #t1.PartitionId)
join #t3
on #t3.PartitionId = ('dtString_' + #t1.PartitionId)
order by PartitionStartTime desc
--cleanup
drop table #t1
drop table #t2
drop table #t3
