May, 2011

  • musc@> $daniele.work.ToString()

    Improved ACS Partitions Query

    • 0 Comments

    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:

    1. 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
    2. 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 Smile

     

    --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
Page 1 of 1 (1 items)