Shrinking the database files and logs on a regular maintenance window.

Question

I firmly believe that the database should be shrink at a regular maintence windows wednesday and sunday.
These will prevent me from waking up at night due to receiving alert that the DB is out of space.
without the shrink procedure the db size will catch up with whatever HD space we put in.

Staging and import tables for etl usully allocate space and these tables are purge on a daily basis.
SQL server does not reuse these space.

I dont know where the theory that shrinking the database is a bad practice comes from. Please advice
What other maintenance job should I add so that shrinking database does not affect the db performance?

Answer 1

Hi @jose de jesus ,

Please refer to this MS document: Shrink a database Consider the following information when you plan to shrink a database:

• A shrink operation is most effective after an operation that creates a large amount of unused storage space, such as a large DELETE statement, truncate table, or a drop table operation.
• Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the free space is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation. Autogrow events necessary to grow the database file(s) hinder performance.
• A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
• Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.

If shrinkage makes fragmentation increase, you need to rebuild the index to reduce fragmentation. Sometimes shrinkage doesn't solve the underlying problem, and you may face the problem of shrinking and then growing. Another thing is that if you are in full mode, you need to take regular backups to truncate the logs and prevent them from growing and running out of space.

Best regards, Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I firmly believe that the database should be shrink at a regular maintence windows wednesday and sunday.

No!, no!, no!, never do this, it cause index fragmentation so so may performance issues
https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

These will prevent me from waking up at night due to receiving alert that the DB is out of space.

Then you should find & fix the cause instead.

SQL server does not reuse these space.

It do, released space is used for new data.

Answer 3

I firmly believe that the database should be shrink at a regular maintence windows wednesday and sunday.
These will prevent me from waking up at night due to receiving alert that the DB is out of space.

You are pulling our legs, aren't you?

If your HD fits 500 GB, and there is 100 GB of data in the database, and someone pours 350 GB of new data, you will run out of disk space, no matter the size of the data file was 400 GB or 100 GB when you started.

What can save your sleep is to have good purging operations in place, so that tables in import and staging schemas are automatically emptied, preferably with TRUNCATE TABLE.

That increases the amount of free space inside the database. But the size of the data file does not really matter.

And moreover what's the point of shrinking something that will grow again?

Answer 4

Drive Space is cheap!.. Not true maybe on my laptop not on the server :) cheers

i'll go for logs of 10 to 20 percent on the data
10 to 20 percent free space on the datafiles

beyond that ill shrink it weekly

Answer 5

i dont know how microsft folks manage 300,000 sql server with 20 database ineach sql server in the global operation center without ever shrinking the logs.

The backup will not fit in the cloud they should invent the moon or mars for storage.