Sql-server – Database Shrink task fails

sql serversql-server-2016ssms

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?

Best Answer

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.

Related Question