How to prevent database shrinking

permissionsshrinktrigger

We have a 3° party application, with an application user who needs to be db_owner on the database (not my choice) on SQL Server system. All stored procedures are encrypted in this database. At some point they do a shrink of the database files in this application, and they do it every day. I want to prevent this, and I was looking if there was a way to do this either with:

  • Deny permission somewhere
  • a server or database trigger
  • something else

Can someone help me

Best Answer

We have an application, with an application user needs to be db_owner on the database

If this application is written "in-house" then push back - hard - and get them to justify why this needs to be the case.
Running an application as a database owner, you might as well hand the database back to the Development team because they can do pretty much whatever they like, with or without your knowledge and/or agreement.
If they're going to break it, let them take the flak and fix it.

If this application is a Package from a third-party supplier (and this is remarkably common), then you're a bit stuck. They can code the application to do whatever they want and again, there's nothing much that you can do about it.

At some point they do a shrink of the database files in this application, and they do it every day. I want to prevent this ...

Why?

Is the shrinking operation causing an operational problem, i.e. causing difficulties for Users/the Business? If so, then that's a very strong argument for getting them to stop doing this.

Another, lesser, argument might be if the "shrunk" data files very quickly grow back to their previous size. IF so, then the shrinking is a waste of time and is probably causing performance issues while the data files grow back again, because growing a data file is always slower than just using free space that's already there.