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?