Unattended Reindexing of all user tables

,

I see scripts all the time on how to reindex all the tables in a db but they are usually too generic and may cause issues.  For instance they typically cause the log file to grow very large and may even fill the hard drive.  This is intended to be run off hours when db activity is low or nonexistant.  I usually discourage accessing system tables directly but seeing as this is a maint script it should not pose a problem.  The user may choose to do a full backup before and or after if the db is not already in simple recovery mode.

SET NOCOUNT ON

/*  Find out what recover mode it is currently in and act accordingly  */
DECLARE @Simple VARCHAR(10), @DBname NVARCHAR(40)

SELECT @DBName = DB_NAME(dbid) FROM master..Sysprocesses WHERE SPID = @@SPID

IF OBJECT_ID('tempdb.dbo.#Simple') IS NOT NULL
    DROP TABLE #Simple

CREATE TABLE #Simple (OptionName VARCHAR(30),CurrentSetting VARCHAR(10))
INSERT INTO #Simple (OptionName, CurrentSetting) EXEC sp_dboption @DBName, N'trunc. log'

SELECT @Simple = CurrentSetting FROM #Simple

IF @Simple = 'off'
BEGIN
    /*  Set it to simple mode */
    EXEC sp_dboption @DBName, N'trunc. log', N'true'
END

DECLARE @TableName VARCHAR(32), @Count INT, @SQL VARCHAR(128)
SET @Count = 1

DECLARE curIndexes CURSOR STATIC
FOR
SELECT CONVERT(VARCHAR(32), OBJECT_NAME([id])) AS MYTABLES
      FROM sysindexes
        WHERE [indid] Between 1 And 254 AND [id] > 1000 AND [Name] NOT LIKE '[_]WA%'
      GROUP BY [ID] ORDER BY [MYTABLES]

/* Open the cursor */
OPEN curIndexes

/*Get the First Record*/
FETCH NEXT FROM curIndexes INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

    DBCC DBREINDEX(@TableName) WITH NO_INFOMSGS

    /*  Truncate the log after every 3 tables indexed  */
    IF @Count % 3 = 0
    BEGIN
        BACKUP LOG @DBName WITH TRUNCATE_ONLY
    END

    SET @Count = @Count + 1

FETCH NEXT FROM curIndexes INTO @TableName

END

CLOSE curIndexes
DEALLOCATE curIndexes

DBCC UPDATEUSAGE(0) WITH NO_INFOMSGS

/*   Set the recovery mode back the way it was if it was changed   */
IF @Simple = 'off'
BEGIN
    EXEC sp_dboption @DBName, N'trunc. log', 'false'
END

GO

Rate

Share

Share

Rate