Rebuild indexes ONLINE = OFF, and update statistics.

Do you have a maintenanceplan that rebuilds the indexes during the weekend, but monday morning all clients complaints that the system is saying “Not responding”. When you look at the database you can see that the Rebuild indexes hasn’t finished because some process was blocking.

If you are using the “Execute T-SQL Statement Task” then you can setup the plan to send you an email if the job isn’t finished after x seconds.

The script below rebuild all indexes in all userdatabases and update statistics.


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','msdb','tempdb','model','distribution')
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
    IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
    BEGIN
      -- SQL 2005 or higher command
      SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = ON )'
      EXEC (@cmd)

      SET @cmd = 'UPDATE STATISTICS ' + @Table + ' WITH FULLSCAN'
      EXEC (@cmd)
    END
    ELSE
    BEGIN
     -- SQL 2000 command
     DBCC DBREINDEX(@Table,' ',@fillfactor)
    END

    FETCH NEXT FROM TableCursor INTO @Table
  END

  CLOSE TableCursor
  DEALLOCATE TableCursor

  FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

Dette indlæg blev udgivet i SQL Server - scripts. Bogmærk permalinket.