Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 7 down vote accepted

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.

share|improve this answer
That worked like a charm, and it even had an example for what I was intending to accomplish. Thanks a lot! – Petter Brodin Oct 22 '12 at 9:12
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 '12 at 13:46
I agree! But without proper indexing, finding the right data can become too demanding ;) – Petter Brodin Oct 23 '12 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'')
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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