Why is SHRINKFILE a very bad thing and what should I do?

From: http://www.sqlservercentral.com/articles/SHRINKFILE/71414/

Joe. TJ is used only for the purpose of disseminating information. Note: reference information in the original text and a stored procedure for re-indexing are not included in the translation during the translation process.

Problem:

Avoid shrinking database files (using SSMS or T-SQL) whenever possible ). In an ideal environment, it is best to determine the file size from the very beginning and then increase it as needed.

Of course, in the real world, there is always a space in which files exceed the allocated size and data expansion, or we need to recycle the files because the data is deleted or compressed.

In this case, we only use the SHRINKFILE command. SQLServer is not very concerned about moving the allocated page at the end of the file to the position where the file header is not divided, which leads to two problems:

1. Data becomes fragmented and may reach a fragmentation rate of 100%, which is a killer of database performance.
2. This operation is slow. The pointer to all moved pages or rows must be corrected. SHRINKFILE is a single-threaded operation, so it will be slow. The single-thread nature of SHRINKFILE will not be changed quickly.

SHRINKDATABASE vs SHRINKFILE vs AutoShrink:

The SHRINKDATABASE Command actually executes the SHRINKFILE command for each data file in the database in sequence. Therefore, the following content of this article only references SHRINKFILE, but it is also applicable to SHRINKDATABASE.

See books online to learn the differences and usage between the two. AutoShrink is not an alternative to SHRINKFILE or SHRINKDATABASE. It actually runs the SHRINKDATABASE command.

 

Recommended Practices(1 ):TryTRUNCATONLYOption

First, try to use dbcc shrinkfile truncatonly: It only removes the free space at the end of the file and does not reorganize the used data pages.

This may not produce satisfactory results, that is, it will not release enough space. But it is worth trying, especially because it is much faster than dbcc shrinkfile when the TRUNCATEONLY option is not used.

 

Recommended Practices(2 ):InSHRINKFILEThen re-organize or re-create the index

If you need to execute SHRINKFILE, allocate enough waiting time for this operation to complete. However, you can stop this operation at any time without causing rollback.

All the operations that have been done will be recorded, but any operations that have been completed before the cancellation point will take effect, so you can continue to execute later and the time spent in the past is not a waste of time.

The disadvantage is that you have made a lot of data fragments.

Once the SHRINKFILE is completed, you need to determine the index to be rebuilt and restructured and execute it. The most tricky thing here is to know how long it takes. This time not only includes the completion of file shrinking,

There is also the time required to re-build or restructure the index after the contraction is completed. This also has the negative impact of the re-Growth of files, but we hope that it will not increase as much as we contract.

 

Recommended Practices(3 ):Move user data to a new file

Add a new file group containing one or more files to the database, and import the data to the new file group when re-indexing. And then contract the original file. This method has several advantages:

1. This avoids the fragmentation of the original SHRINKFILE file because no user data needs to be moved or moved.
2. Executing SHRINKFILE on the original file is much faster, because no user data needs to be moved.
3. Because you do not need to re-index the table in the original file, it will not increase its size again.
4. You can perform contraction in a smaller, easier-to-manage block without affecting the data that remains in the original file.

Of course, you need to set the new file group as the default file group. Otherwise, you will add new tables and objects to the old file group.

For many reasons (there is not enough disk space for the new file group, there is no index on the heap table, and it is not easy to manage), this may not be possible.

However, this is the best practice for separating user data from system data in the database.

 

Conclusion

This article does not mean that it is a detailed description of SHRINKFILE. It just reminds you that it is very bad to execute SHRINKFILE, SHRINKDATABASE, and AutoShrink on the database.

If you have to do this again, you can choose some of the practices.