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