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

sp_MSforeachdb '
USE [?]
SET @dbid = DB_ID()
IF(@dbid > 4)
    --PRINT ''[?]'' + CONVERT(VARCHAR, @dbid)

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?

2 Answers 2


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.

  • That worked like a charm, and it even had an example for what I was intending to accomplish. Thanks a lot! Oct 22, 2012 at 9:12
  • 3
    We'd better thank Aaron :-). He made the script, I just made a quick search to it. Let's say in this case I'm an NC index pointing to the clustered index leaf data, and he's the clustered index itself :-).
    – Marian
    Oct 22, 2012 at 13:46
  • 1
    I agree! But without proper indexing, finding the right data can become too demanding ;) Oct 23, 2012 at 6:08

although this is answered I just though it would be nice to drop this little line of code that does exactly the filtering most people want and sets AUTO_SHRINK OFF:

EXECUTE sp_MSforeachdb 'IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model'')) EXECUTE (''ALTER database ? SET AUTO_SHRINK OFF'')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.