Friday , March 29 2024

How To Shrink SQL Server Data File

I wanted to write this article because it has been mentioned in the topic “How To Shrink SQL Server Transaction Log” and there are some drawbacks in shrinking the data file.

Can We Shrink Data File in SQL Server

Yes you can shrink your data files. But you should not!

What happens if we shrink data file in SQL Server?

If you shrink your data files, the index fragmantation in these files will grow very high. Not only non clustered index. This includes our clustered indexes in our tables.

You may want to read the article titled “Difference Between Clustered Index and Non Clustered Index

Instead of Shrinking Data File

Let me tell you something that happened recently. A friend created a table for testing in the X database in production environment and inserted 10 TB of data into the table. Yes 10 TeraByte. There are two things that can be done in such a situation.

Option 1) Shrink all the data files in the filegroup where the table is created for testing purposes.

As you guess, we didn’t do this.

Option 2) Create a new file group and move all indexes (Clustered-Non Clustered) in the file group to the new file group where we need to shrink.

You can find the details of how to perform this in the article “What is Database File Group And How To Recreate Large Tables In Another File Group“.

There is a very useful stored procedure called sp_helpindex3 to be able to determine which index on which file group.

You can create this stored procedure in the related database using the following script.

After doing this, there will be no data in the filegroup that we will shrink. So we can delete this filegroup.

If the file group to be shrinked is a PRIMARY file group, we can not delete this file group.

But now we can shrink because there is no user data in it.

As an important note, certainly do not open auto shrink in your databases.

Can we shrink MDF file?

Yes we can shrink. But first follow the steps I mentioned above. Move all user objects to another file group.

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories