Database Shrink task fails

Question

I created a maintenance plan a week ago and it's been executing fine every night. Now the Database Shrink task has stopped working and throws this error:

Property Size is not available for Database '[foo]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

Yesterday I installed a couple of SQL Server hotfixes offered by Windows Update (4411 and 4457, if that matters). It's the only change I've possibly made.

My task looks like this:

• Connection: Local server connection (sa)
• Database(s): (削除) All databases (削除ここまで) All user databases
• Shrink database when it grows beyond: 50 MB
• Amount of free space to remain after shrink (10%)
• Return freed space to operating system

Some of my findings:

• If I generate the T-SQL code and execute it manually it runs flawlessly.
• If I create a new maintenance plan with an identical Database Shrink task it runs flawlessly.

What can the problem be?

• Shanky: Who on earth shrinks master database ?. Creating maintenance plan for shrinking is horrible there are loads of blogs online which would suggest you NOT to shrink. As for your issue which login is owner of master database it should be SA.
• Álvaro González: @Shanky Owner of master is sa. Who does so? Someone who's not a DBA at all (this server was meant to be managed, but it finally wasn't and guess who got responsible for it). I've changed to "All user databases". Now I get Property Size is not available for Database '[foo]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. where foo is a user database.
• Álvaro González: Is the Shrink Database task important at all? If not, I could just get rid of. This server should be just a temporary machine until the customer migrates to his own infrastructure in some months.
• Aaron Bertrand: You should consider the task failing a good thing. Stop shrinking anything.

Answer 1

As Shanky already commented, running scheduled shrinks is wrong. There is ton of info online about it. Find the root cause of that growth and solve it. I would get rid of it immediately.

As for the error, seems related to db ownership. If you run a search with the error text your will find several related questions here, here and more.

As a recommendation, I would suggest switching to Ola Hallengren maintenance solution for administrative tasks as DBCC, index maintenance, backups, etc.

• Álvaro González: sa owns all databases (both system and user databases). In any case, the only part of the maintenance part I really care about is backup copies. If scheduled shrinking doesn't even make sense, I'll remove it and problem solved. Веселое рождество!
• Yaroslav: Yeap, removing the shrink is the right thing to do. Te recomiendo que cambies al set de scripts que te pongo en la respuesta, mucho más cómodo de usar, trabajar y tunear con montón de opciones. Felices fiestas desde Mallorca.
• Kiquenet: property Size is not available for Database '[foo]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights solution ?

Answer 2

Why are you doing that shrink operation ?

If you run it everyday, that mean that your database growth everyday and consume the "freed" space.

In that case, you are creating fragmentation in the database every night. Plus, you force the database to "auto-growth" which takes time (specially if you don't have the instant file initialization enable.

When you say "return freed space to the OS", does it mean your database files are on the OS drive (C:) ?

If so, I would recommand that you move those database files to a different drive. That way, if a file fills up the drive, it won't make Windows crash. It will also let SQL use all the IO of the drive (instead of sharing it with the OS).

• Álvaro González: "Return freed space to operating system" is just the label used in the task dialog in SQL Server Management Studio.
• Kiquenet: stop-shrinking ? brentozar.com/archive/2009/08/…