Take the 2-minute tour ×
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.

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?

share|improve this question

1 Answer 1

up vote 5 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
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 '12 at 13:46
1  
I agree! But without proper indexing, finding the right data can become too demanding ;) –  Petter Brodin Oct 23 '12 at 6:08

Your Answer

 
discard

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.