ShrinkDatabase of SQL Server may cause fragmentation and performance issues

In SQL Server you can use the DBCC SHRINKDATABASE command to reduce the space occupied by the data files and log of a database.

ShrinkDatabase SQL Server

The problem with SHRINKDATABASE is that, contrary to what may seem, if not used carefully, the result can be a significant increase in the fragmentation of the indexes, and tables, which leads to a worsening of performance of our SQL Server, data and even an increased footprint when affected by fragmentation indexes are rebuilt.

What does the DBCC command ShrinkDatabase

The SHRINK command releases the free space left in the archives of the database, causing it to 'return' to the operating system rather than being reserved for the growth of data. Space reduction occurs, but you have to think about the growth of the database. If contiguous free space that uses each table is not left, the new data will be created in different physical locations, fragmentation will increase, and indexes and sorts will be complicated.

Include SHRINKDATABASE between daily maintenance of SQLServer BD, for example, may cause a major problem of fragmentation, since the data is inserted may be stored in a different physical location for each new day after implementation SHRINK.

When and how to use SQL Server Shrink

But then, SHRINK DATABASE useful? Clear serving in certain cases. If a database SQL Server are deleted or tables, which greatly reduce the volume occupied by the data, and there is no provision to reoccupy the space are truncated, may be interested in using it to better adjust the relationship between space occupied and reserved space thus reducing the size of the database, but it is always advisable to leave a percentage of free space reserved for both the daily operations of the database and for data growth with minimal fragmentation.

In case of using the Shrink SQLServer, to ensure that reserved space and that growth does not create problems of fragmentation and performance, it is important to review the arguments that support the command as if the second argument is reported with a number reduction leaving space will take that number as a percentage of unused space that is not returned to the operating system.

Command Syntax

DBCC SHRINKDATABASE
(Database_name | database_id | 0
         [, Target_percent]
         [, {NOTRUNCATE | TRUNCATEONLY}]
)
[WITH NO_INFOMSGS]

Example of using Shrink

For example, if we just make cleaning operations (delete schemes unused, temporary or simply unnecessary tables) in our database that we know have reduced the space occupied by the data by 50%, and we want to reduce the space occupied by files in the database, but leaving aside 15% free space for daily operations, and growth that we anticipate in the database for the current year, we could run this command:

DBCC SHRINKDATABASE (Mi_base_de_datos, 15);

 

Thus the space occupied by the data files and log database would be reduced by approximately 35%. If we did not include the parameter of percentage would reduce more space, but the next day the new data inserts and begin to require increases file size obviously not be in the same physical space existing data, and increase the data fragmentation.

Recommendation to reduce fragmentation

 

And speaking of fragmentation, so it is advisable to include in the tasks of regular maintenance is a reconstruction of the indices with REBUILD, which reduce fragmentation and improve the performance of the DB, though it may increase a little more space used:

ALTER INDEX REBUILD idx_mitabla_ID ON MyTable;

References

Finally, I link reference some blog posts from other sites that explain why make a Shrink Database is usually not a good idea provide reasons for not using a ShrinkDatabase , or making a practical demonstration of what happens to the space and fragmentation make a ShrinkDatabase

Do you know any more in which case whether or not advisable to make a Shrink? Have you found a problem on occasion or performance fragmentation caused by an unfortunate use of ShrinkDatabase?