job for index defrag

  • i have a job which is failing

    my lead wants me to avoid the cursors in that job and modify it

    any suggestion would be of great help please

    --

    DECLARE @Database VARCHAR(255)

    DECLARE @Table VARCHAR(255)

    DECLARE @cmd NVARCHAR(500)

    DECLARE @fillfactor INT

    SET @fillfactor = 90

    DECLARE DatabaseCursor CURSOR FOR

    SELECT database_name FROM WHERE status='y'

    AND database_name NOT IN ('master','model','msdb','tempdb')

    ORDER BY 1

    OPEN DatabaseCursor

    FETCH NEXT FROM DatabaseCursor INTO @Database

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName

    FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''

    -- create table cursor

    EXEC (@cmd)

    OPEN TableCursor

    FETCH NEXT FROM TableCursor INTO @Table

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

    EXEC (@cmd)

    FETCH NEXT FROM TableCursor INTO @Table

    END

    CLOSE TableCursor

    DEALLOCATE TableCursor

    FETCH NEXT FROM DatabaseCursor INTO @Database

    END

    CLOSE DatabaseCursor

    DEALLOCATE DatabaseCursor

    thanks

  • All that I have in my script library is a cursor-based script, which believe came from the SQL Server 2000 Books Online (BOL).

    --

    --Begin Script--

    /*Perform a 'USE ' to select the database in which to run the script.*/

    -- Declare variables

    USE CG

    SET NOCOUNT ON

    DECLARE @tablename VARCHAR (128)

    DECLARE @execstr VARCHAR (255)

    DECLARE @objectid INT

    DECLARE @indexid INT

    DECLARE @frag DECIMAL

    DECLARE @maxfrag DECIMAL

    -- Decide on the maximum fragmentation to allow

    SELECT @maxfrag = 5.0

    -- Declare cursor

    DECLARE tables CURSOR FOR

    SELECT TABLE_NAME

    FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_TYPE = 'BASE TABLE'

    -- Create the table

    CREATE TABLE #fraglist (

    ObjectName CHAR (255),

    ObjectId INT,

    IndexName CHAR (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)

    -- Open the cursor

    OPEN tables

    -- Loop through all the tables in the database

    FETCH NEXT

    FROM tables

    INTO @tablename

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Do the showcontig of all indexes of the table

    INSERT INTO #fraglist

    EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')

    WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

    FETCH NEXT

    FROM tables

    INTO @tablename

    END

    -- Close and deallocate the cursor

    CLOSE tables

    DEALLOCATE tables

    -- Declare cursor for list of indexes to be defragged

    DECLARE indexes CURSOR FOR

    SELECT ObjectName, ObjectId, IndexId, LogicalFrag

    FROM #fraglist

    WHERE LogicalFrag >= @maxfrag

    AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

    -- Open the cursor

    OPEN indexes

    -- loop through the indexes

    FETCH NEXT

    FROM indexes

    INTO @tablename, @objectid, @indexid, @frag

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',

    ' + RTRIM(@indexid) + ') - fragmentation currently '

    + RTRIM(CONVERT(varchar(15),@frag)) + '%'

    SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',

    ' + RTRIM(@indexid) + ')'

    EXEC (@execstr)

    FETCH NEXT

    FROM indexes

    INTO @tablename, @objectid, @indexid, @frag

    END

    -- Close and deallocate the cursor

    CLOSE indexes

    DEALLOCATE indexes

    -- Delete the temporary table

    DROP TABLE #fraglist

    GO

    --End Script--

    Happy T-SQLing

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • Hey Damon

    Thanks for your reply and i appreciate your time and i did liked your code anyways my lead wants me to remove all the cursors use something else instead of cursor's so i dont want any cursor's

    Thanks

    if you can provide a solution without any cursor would be a great help

  • You could do something like:

    create table #tables (table_name varchar(80), completed datetime)

    insert #tables

    SELECT TABLE_NAME, null

    FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_TYPE = 'BASE TABLE'

    while exists (select table_name from #tables where completed is null)

    begin

    select top 1 @table = table_name from #tables where completed is null

    ... exec code

    update #tables

    set completed = getdate()

    where table_name = @table

    end

    Use this to drive your system. Not sure it's much better than a cursor, but if you change #tables to some permanent table, you can track when things are happening, even restart if the job fails with a new table.

  • We use acursor driven proc but just altering it to be #temptbl oriented, you might handle it like this:

    DECLARE @SQLStatement nvarchar(4000)

    --DECLARE TableList CURSOR LOCAL FAST_FORWARD READ_ONLY FOR

    Declare @tmpReindexList table (SeqNo int identity(1,1) not null, SQLStatement nvarchar(4000) not null)

    insert into @tmpReindexList (SQLStatement)

    SELECT

    N'DBCC DBREINDEX (''['+ db_name() + '].' +

    QUOTENAME(TABLE_SCHEMA) +

    N'.' +

    QUOTENAME(TABLE_NAME)

    + ''')' as SQLStatement

    FROM

    INFORMATION_SCHEMA.TABLES

    WHERE EXISTS

    (

    SELECT *

    FROM sysindexes

    WHERE id =

    OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +

    N'.' +

    QUOTENAME(TABLE_NAME))

    AND

    indid IN(0,1)

    )

    ORDER BY 1

    -- OPEN TableList

    declare @SeqNo int

    WHILE 1 = 1

    BEGIN

    --FETCH NEXT FROM TableList INTO @TableName

    Select top 1 @SeqNo = SeqNo, @SQLStatement=SQLStatement

    from @tmpReindexList

    if @@rowcount = 0 break

    print @SQLStatement

    EXEC(@SQLStatement)

    delete from @tmpReindexList where SeqNo = @SeqNo

    END

    -- CLOSE TableList

    -- DEALLOCATE TableList

    exec sp_updatestats

    dbcc updateusage(0) with count_rows

    btw I've kept the commented lines in the code so you can easily see the difference :hehe:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

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

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