script to run dbcc indexdefrag for all databases in a server.

  • Hi all,
    I'm trying to create a script to run dbcc indexdefrag not only for one database, but for all of my databases in my server.
    I try the following:

    /*Perform a 'USE <database name>' to select the database in which to run the script.*/
    -- Declare variables
    SET NOCOUNT ON
    DECLARE @tablename VARCHAR (128)
    DECLARE @execstr VARCHAR (255)
    DECLARE @objectid INT
    DECLARE @indexid INT
    DECLARE @frag DECIMAL
    DECLARE @maxfrag DECIMAL
    DECLARE @name VARCHAR(50) -- database name
    declare @SQL varchar(1000)
    -- Decide on the maximum fragmentation to allow
    SELECT @maxfrag = 30.0

    DECLARE db_cursor CURSOR FOR
    SELECT name
    FROM master.dbo.sysdatabases
    WHERE name NOT IN ('master','model','msdb','tempdb', 'MFB_Forms', 'MFB_Objects','ODDevContent','KMASTER')
    AND name NOT LIKE 'D%'
    AND name NOT LIKE '%_Forms'
    and name not like '%_Objects'

    OPEN db_cursor 
    FETCH NEXT FROM db_cursor INTO @name 

    WHILE @@FETCH_STATUS = 0 
    BEGIN 
            set @SQL = 'USE ' + @name + ';'
            print @SQL
            exec (@SQL)    
            

                -- Declare cursor
                DECLARE tables CURSOR FOR
                SELECT TABLE_NAME
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_TYPE = 'BASE TABLE'
                -- Create the table
                AND TABLE_NAME NOT IN('ConnectionInformation','SessionContextNames','DefineVariables')
                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

            FETCH NEXT FROM db_cursor INTO @name 
            END 
            
    CLOSE db_cursor 
    DEALLOCATE db_cursor

    GO

    When I run this code on SSMS it runs perfect, but when I try to schedule on a job, it fails and only runs in the current database.
    Any advice on this?

    Thank you in advance.

  • Are you sure this is the exact same thing you ran in SSMS? It looks like it would run in just master if that's where it started.
    In this part:
    set @SQL = 'USE ' + @name + ';'
    print @SQL
    exec (@SQL)

    Once you execute with the exec(@SQL), the change in database context with the  USE statement is no longer in effect. Check the example in the documentation and the last bullet in for the section on Self-Contained batches.
    Using sp_executesql

    You can see this in your script - just add select db_name() after that execute. You could pull out the other code in your cursor and just use:
    set @SQL = 'USE ' + @name + ';'
    print @SQL
    exec (@SQL)
    print DB_NAME ()

    It will  just print print out master after each of the USE YourDatabase statements

    Sue

  • Sorry, forgot to post these. Just for reference and if you want to get an idea of how others handled this or if you want to try their scripts, check these links. The last one is an older version but should cover you on SQL Server 2008. It still gives you an idea nonetheless:

    Minionware Reindex
    Ola Hallengren Index and Statistics Maintenance
    SQLFool Index Defrag

    Sue

  • Hi Sue,
    Thank for your reply. Yes you are right. This procedure only runs in the master db. So the, objective is not covered with this script.
    I will take a look to Ola Hallegeren Index to see if I can implement on my db server.
    From the application point of view (I use Infor's SYTELINE ERP), the recommended procedure is to use dbcc INDEXDEFRAG for each table.

    Thank you.

  • mig28mx - Friday, September 1, 2017 8:46 AM

    Hi Sue,
    Thank for your reply. Yes you are right. This procedure only runs in the master db. So the, objective is not covered with this script.
    I will take a look to Ola Hallegeren Index to see if I can implement on my db server.
    From the application point of view (I use Infor's SYTELINE ERP), the recommended procedure is to use dbcc INDEXDEFRAG for each table.

    Thank you.

    Correct since the use statement is no longer applicable after the exec. I think most of the others have a table for databases and table for indexes that would need to be defragemented. You could use temp tables or static tables that you truncate before processing. Most of how it is set up is in the beginning of those other scripts.
    If the  vendor recommends something, it's usually best to follow their recommendations.

    Sue

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

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