How to store DBCC showcontig results to a table in 2000 and 2005

  • Can any one explain me like how can we store DBCC Showcontig results can be stored into a table for each tables in a database

  • Use the TABLERESULTS option of showcontig

    Something like

    INSERT INTO ContigCheckTable

    EXECUTE ('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES')

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • this proc stores results for all tables in targeted db

    /*=========================================================================================

    Processing Steps:

    create temp table #_fraglist

    fill with dbcc showcontig for all tables at a time in target DB (with tableresults, all_indexes,all_levels)

    Parameters:

    @dbname (sysname) name of database to get densities for

    Return Value:

    -1: error - invalid target DB; 0: OK

    Keeps history of defragged indexes in history table

    =========================================================================================*/

    CREATE procedure dbo.upo_exp_showcontig (

    @dbname sysname)

    as

    set nocount on

    declare @err int, @cmd varchar(1000), @tabname sysname, @int int, @cnt int, @indexname sysname, @end_time datetime,

    @start_time datetime

    -- Create temporary table to hold DBCC SHOWCONTIG output

    CREATE TABLE #_fraglist (

    ObjectName VARCHAR (255),

    ObjectId INT,

    IndexName VARCHAR (255),

    IndexId INT,

    Lvl INT,

    CountPages INT,

    CountRows INT,

    MinRecSize INT,

    MaxRecSize INT,

    AvgRecSize INT,

    ForRecCount INT,

    Extents INT,

    ExtentSwitches INT,

    AvgFreeBytes INT,

    AvgPageDensity INT,

    ScanDensity DECIMAL,

    BestCount INT,

    ActualCount INT,

    LogicalFrag DECIMAL,

    ExtentFrag DECIMAL)

    -- Update statistics first

    ------------------------------------------------set @cmd = 'use ' + @dbname + '; exec sp_updatestats'

    ---- exec(@cmd)

    if @dbname is not null

    begin

    -- Insert DBCC SHOWCONTIG output for all tables in database into #fraglist

    set @cmd = 'use ' + @dbname + '; exec sp_MSForEachTable @command1 = ''insert into #_fraglist exec(''''dbcc showcontig([?]) with tableresults, all_indexes'''')'''

    exec (@cmd)

    /*ERRTRAP*/ select @err = @@ERROR if @err <> 0 return @err

    end

    delete #_fraglist where indexid in (0, 255)

    set @start_time = getdate()

    set @end_time = getdate()

    insert admin..Fragmented_Indexes_History

    ( DBName, ObjectName,ObjectId, IndexName,

    IndexId, Lvl, CountPages, CountRows,

    MinRecSize, MaxRecSize, AvgRecSize,

    ForRecCount, Extents, ExtentSwitches,

    AvgFreeBytes, AvgPageDensity, ScanDensity,

    BestCount, ActualCount, LogicalFrag, ExtentFrag,

    start_time, end_time)

    select @dbname, ObjectName,ObjectId, IndexName,

    IndexId, Lvl, CountPages, CountRows,

    MinRecSize, MaxRecSize, AvgRecSize,

    ForRecCount, Extents, ExtentSwitches,

    AvgFreeBytes, AvgPageDensity, ScanDensity,

    BestCount, ActualCount, LogicalFrag, ExtentFrag,

    @start_time, @end_time

    from #_fraglist

    update r set r.reindex = 1, r.logical_frag = i.logicalfrag

    from admin..reindex r, admin..Fragmented_Indexes_History i

    where i.end_time between getdate() - 1 and getdate()

    and i.logicalfrag > 30 and i.CountPages > 50

    and i.dbname = r.dbname and i.objectname = r.table_name

    insert admin..reindex(dbname, table_name, reindex, logical_frag, extentfrag, sql)

    select i.dbname, i.objectname, 1, i.logicalfrag, i.extentfrag,

    'DBCC indexdefrag (' + rtrim(i.dbname) + ',' + rtrim(i.objectname) + ', ' + rtrim(i.indexname) + ' )WITH NO_INFOMSGS'

    from admin..Fragmented_Indexes_History i

    where i.end_time between getdate() - 1 and getdate()

    and (i.logicalfrag > 30 or i.extentfrag>30) and i.CountPages > 50

    and not exists(select 1 from reindex r

    where r.dbname = i.dbname and r.table_name = i.objectname)

    drop table #_fraglist

    return 0

    ---exec upo_exp_showcontig @dbname ='bx01p'

    GO

  • Thank You for the reply...:)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply