Rebuilding Indexes in batches.

  • Hello,

    Do to space issues on our server, I'm trying to build a SQL statement that would rebuild indexes on tables within are large db. Has anyone done this before, or is this possible to do? I would like to try rebuilding the indexes in batches of 10 tables at a time, this to reduce the large transaction log.

    Thanks in advance.

  • Following script help u to rebuild all indexes of all tables for all databases.

    DECLARE @Database VARCHAR(255)

    DECLARE @Table VARCHAR(255)

    DECLARE @cmd NVARCHAR(500)

    DECLARE @fillfactor INT

    SET @fillfactor = 90

    DECLARE DatabaseCursor CURSOR FOR

    SELECT name FROM master.dbo.sysdatabases

    WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')

    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

    -- SQL 2000 command

    --DBCC DBREINDEX(@Table,' ',@fillfactor)

    -- SQL 2005 command

    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

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • And also find below script which index reorgnize or rebuild depends on fragmentation ratio automatically for perticular database :

    SET NOCOUNT ON;

    DECLARE @objectid int;

    DECLARE @indexid int;

    DECLARE @partitioncount bigint;

    DECLARE @schemaname nvarchar(130);

    DECLARE @objectname nvarchar(130);

    DECLARE @indexname nvarchar(130);

    DECLARE @partitionnum bigint;

    DECLARE @partitions bigint;

    DECLARE @frag float;

    DECLARE @command nvarchar(4000);

    -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

    -- and convert object and index IDs to names.

    SELECT

    object_id AS objectid,

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS frag

    INTO #work_to_do

    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

    -- Declare the cursor for the list of partitions to be processed.

    DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

    -- Open the cursor.

    OPEN partitions;

    -- Loop through the partitions.

    WHILE (1=1)

    BEGIN;

    FETCH NEXT

    FROM partitions

    INTO @objectid, @indexid, @partitionnum, @frag;

    IF @@FETCH_STATUS < 0 BREAK;

    SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

    FROM sys.objects AS o

    JOIN sys.schemas as s ON s.schema_id = o.schema_id

    WHERE o.object_id = @objectid;

    SELECT @indexname = QUOTENAME(name)

    FROM sys.indexes

    WHERE object_id = @objectid AND index_id = @indexid;

    SELECT @partitioncount = count (*)

    FROM sys.partitions

    WHERE object_id = @objectid AND index_id = @indexid;

    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

    IF @frag < 30.0

    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

    IF @frag >= 30.0

    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

    IF @partitioncount > 1

    SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

    EXEC (@command);

    PRINT N'Executed: ' + @command;

    END;

    -- Close and deallocate the cursor.

    CLOSE partitions;

    DEALLOCATE partitions;

    -- Drop the temporary table.

    DROP TABLE #work_to_do;

    GO

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

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

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