Share via

SQL2014 Shrink Database

Julie S 1 Reputation point
2021-02-05T13:58:28.787+00:00

Hello - I have a database that is 460GB in size. We recently were able to delete data from the database and would like to be able to shrink the file size down now. The 'Currently allocated space' is 450650.75MB and Available free space is 40576.75MB(9%). What is the best course of action to shrink the actual size of the mdf file . Should I reorganize pages before releasing unused space and then release unused space? I've read that shrinking the file causes fragmentation so I don't want to make the situation worse. We also will be deleting about 50 million more records from this database. I don't know if by deleting all of these records, are we making the situation worse. If anyone has any suggestions that would be great. Thanks.

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

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.1K Reputation points
    2021-02-05T22:44:17.013+00:00

    I see little reason to shrink the database file at all when 91% of the space is used. With those relations, I would rather be thinking about pre-growing in a maintenance window so that an auto-grow does not happen during business hours.

    0 comments
  2. CathyJi-MSFT 21,131 Reputation points Microsoft Vendor
    2021-02-08T07:09:39.903+00:00

    Hi @Julie S ,

    A shrink operation is most effective after an operation that creates unused space, such as a truncate table or a drop table operation. Suggest you use DBCC SHRINKFILE not DBCC SHRINKDATABASE. To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking.

    Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.

    Please refer to the blog How to shrink a database in 4 easy steps to get more information.

    If the response is helpful, please click "Accept Answer", thank you.

    0 comments