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.
SQL2014 Shrink Database
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.
2 answers
Sort by: Most helpful
-
-
CathyJi-MSFT 22,321 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.