Sql-server – Turning off auto shrink on all SQL Server databases. Why doesn’t this work

sql serversql-server-2005sql-server-2008

I figured I could use sp_MSforeachdb to solve this problem, but I get an error message.

sp_MSforeachdb '
BEGIN
USE [?]
DECLARE @dbid INT
SET @dbid = DB_ID()
IF(@dbid > 4)
BEGIN
    --PRINT ''[?]'' + CONVERT(VARCHAR, @dbid)
    --ALTER DATABASE [?] SET AUTO_SHRINK OFF
END
END;
'

If I run the above query with the PRINT line uncommented, I get a list of all databases except the system databases. However, when I uncomment the ALTER DATABASE line, I get these two error messages:

Msg 5058, Level 16, State 2, Line 9
Option 'AUTO_SHRINK' cannot be set in database 'master'.
Msg 5058, Level 16, State 1, Line 9
Option 'AUTO_SHRINK' cannot be set in database 'tempdb'.

This seems to break the operation at some point, so that only some of the databases get auto shrink disabled.

Any idea how I can disable auto shrink on all databases? Bonus question: why doesn't my approach work?

Best Answer

Unfortunately, the procedure sp_MSforeachdb is and will always be unsupported and thus, kind of unreliable.

There's an article on CodeProject that shows how to filter databases when using this DB. But I have the same problem as yours on my local 2008 R2 installation. Filtering doesn't work.

Our own friend, Aaron, wrote a nice article some time ago, about writing a different and better version of sp_MSforeachdb. See his article here. Use that SP and the filtering parameters will do what you need.

Related Question