Disable Auto Shrink of All Databases in SQL Server

Hello to everyone,

In this article, I will give information about disabling Auto Shrink of all databases in SQL Server.

In SQL Server, in some cases, you may want to disable Auto Shrink for all databases.

You can easily do this using the code below.

DECLARE @name VARCHAR(500);
DECLARE @sql VARCHAR(8000);
SET @sql = '';
DECLARE Database_Cursor CURSOR READ_ONLY FOR
SELECT name
FROM sysdatabases
WHERE dbid > 4;
OPEN Database_Cursor;
FETCH NEXT FROM Database_Cursor
INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = @sql + 'ALTER DATABASE [' + @name + '] SET AUTO_SHRINK OFF' + CHAR(10);

    FETCH NEXT FROM Database_Cursor
    INTO @name;
END;
CLOSE Database_Cursor;
DEALLOCATE Database_Cursor;

PRINT @sql;

--EXEC (@sql); --Use Carefully

When you run the above code, you will see a result similar to the one below.

As can be seen, the code to reduce the Auto Shrink state of all databases has been created.

Good luck to everyone in business and life.