Summary : We will know why it is not preferred to shrink SQL server data files and what is the best way to do it. When we may fail doing so.
Let’s understand what is Shrink feature and how it is used. From time to time DBA find themselves in a situation when database becomes too large and they need to shrunk to free up the space.
Shrinking of database is a process where the unallocated space by deleted data in the middle of allocated data pages can be freed up or allocated them in the front of the database files. It is actually a process in which unused space which was previously used is being removed from the database. SQL Server performs process by moving them from the end of database file to the front of database file, therefor pages in the end are freed up.
We have many times heard from most of DBAs not to Shrink SQL Server Data Files. Sometimes they may get angry too. Nevertheless, there are critical situations where it is considered only the valid solution.
What happens when you shrink a database ?
When you shrink a database, you are removing the unused space from your database’s file. This operation can result into Index fragmentation and lowers the performance of the database. As said before, it goes to the end of the database file and picks up pages there and moves them to the first unallocated blocks of space in database file. This actually creates a big fragmentation.
If you have growing database and you think it is going to happen again and again, then do not delete the data in bulk. Avoid using truncate command on large table. Even when you run delete command to remove big amount of data, that does not release the space. Instead it is always advised to remove unnecessary data time to time. This will not cause fragmentation and you will have unallocated space at the end of database file.
We can perform operations to find out which table or which part of the database is consuming more size. There are many ways to do so. You can check out this article for more information
When you can do Shrink on SQL Server Database?
Any situation when a big amount of data has been removed or big amount of space freed on database by changing data types of columns or some index operations. Here are few situations out of them.
- Dropping large amount of Tables
- Substantial changes in table’s data type which cause freeing the data.
- Deleting large amount data/ rows
- Truncate operation on large data table
See Also :