Share via

SQL Database Shrink

Glenn 1 Reputation point
2022-01-17T11:38:34.563+00:00

Hi all,

Quick question, I have a database which due to a one-time mass photo import has grown to 600GB with 54% of it now unallocated. The photos were uploaded to the DB, which grew to accommodate them, and were then later moved off to the filestore permanently.

This isn't going to happen again due to safeguards I have put in place, so looking into ways of releasing the unused storage back into the filestore. I've read about doing a shrink task, but have read conflicting views about this not being best practice. Also worth mentioning that the database in question is in a high availability setup, so unsure on how any shrink tasks would effect the transaction log.

We are a small organisation and I'm the SQL 'expert' but my knowledge still has it's gaps, this being one of them!

Does anyone have any pointers on how to proceed without causing any problems after?

Thank you!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,869 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2022-01-17T12:54:01.727+00:00

    Shrinking is not something you want to do regularly.

    But in your case where you deleted mass data and want the space back, it is perfectly reasonable to shrink the database down and recover that disks space.

    0 comments
  2. Glenn 1 Reputation point
    2022-01-17T14:57:40.133+00:00

    Is there a best way of doing this to a server in a high availability environment?

  3. Erland Sommarskog 102.2K Reputation points
    2022-01-17T23:14:31.08+00:00

    If you are really nervous, restore a backup of the database in a test environment to run shrink there to see what happens.

    Shrink works with small transactions, so it should not be a big issue. You can also stop it and resume it later.

    One caveat, though, if you have LOB columns, shrink can be very sloooooow. The photos were obviously LOBs, but they are gone, so hopefully they are not an issue.

    Keep in mind that shrink introduces fragmentation, so you need to follow up with a reindexing job.

    0 comments
  4. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2022-01-18T06:02:21.35+00:00

    Hi Glenn-8322,

    Is there a best way of doing this to a server in a high availability environment?

    In addition, you can use the following query to determine whether to shrink the database directly in primary node: 

    select log_reuse_wait_desc from sys.database where name='databasename'

    If it returns LOG_BACKUP, please backup the log first.
    Please check this similar thread which might be helpful.

    Best Regards,
    Amelia

    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.

    0 comments