Sunday, 16 May 2010

How to turn off auto-shrink on all your databases

Shrinking a database is bad. Why? Well its because it causes massive index fragmentation. (You can read about it here).


DECLARE @Base SYSNAME
 
DECLARE database_cursor CURSOR FOR
    SELECT  QUOTENAME(name) AS Base
    FROM    master..sysdatabases
    WHERE   DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
            AND DATABASEPROPERTYEX(name, 'IsAutoShrink') = 1
 
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @Base
 
WHILE @@FETCH_STATUS = 0 
BEGIN
    EXEC ('ALTER DATABASE ' + @BASE + ' SET AUTO_SHRINK OFF')
    FETCH NEXT FROM database_cursor INTO @Base
END
CLOSE database_cursor
DEALLOCATE database_cursor