0

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?

Improve this question
4
  • 5
    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.
    – Shanky
    Commented Dec 28, 2017 at 9:17
  • @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
    Commented Dec 28, 2017 at 9:22
  • 1
    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.
    – Álvaro González
    Commented Dec 28, 2017 at 9:37
  • 6
    You should consider the task failing a good thing. Stop shrinking anything.
    – Aaron Bertrand
    Commented Dec 28, 2017 at 11:57

2 Answers 2

Reset to default
5

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.

Improve this answer
3
  • 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. Веселое рождество!
    – Álvaro González
    Commented Dec 28, 2017 at 9:47
  • 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.
    – Yaroslav
    Commented Dec 28, 2017 at 9:51
  • 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 ?
    – Kiquenet
    Commented Apr 25, 2019 at 14:23
1

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).

Improve this answer
2

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.