Overview of the Situation
While accessing data files in SQL Server, many issues may occur that may lead to frustrating situation for users. One such situation occurs when user are running DBCC SHRINKFILE. There are many issues with running DBCC SHRINKFILE on your SQL Server data file. In the information described below, user will get to know the causes of this issue and relevant solutions to overcome from the situation.
Reason for Occurrence of Issue
The fragmentation plays a major role in the occurrence of an issue with running DBCC SHRINKFILE on your SQL Server data file. It occurs in two ways as mentioned below:
- First is the fragmentation of file. As by default 1 MB of file size is used, commonly the file must grow. Though file fragmentation is not directly linked with the fragmentation that is within SQL table, it grounds extra IO operations.
- Another form of fragmentation is index fragmentation. It arises when the SQL Server splits a page to permit for insertion of new row. The fragmentation of index can be restrained with
sys.dm_db_index_physical_stats
Workaround with the Situation
DBCC SHRINKFILE helps to shrink the size of .mdf file whether to a specific size or to a small size. It moves the pages of file from back to first free pages in front of file along through the importance the pages in reverse order ends up.
User can remove the fragmentation by rebuilding the index by using DBCC DBREINDEX or user can user ALTER TABLE with REBUILD option. User can use mentioned script to rebuild:
PK_Consume_Space
It helps to remove the fragmentation and after that, the user can measure both the space used by database and fragmentation.
Sometimes, the user notice that the size of database has grown from 18 to 42 megabytes. This is because the data is copied to new space in the file and there are two copies of data on disk. If the script is run to add extra space to defragmented index then, the space is allotted for the insertion of new rows without splitting. It helps to boost the performance at time of insertion, which helps to eliminate the fragmentation. User can note down the percentage, which signifies that the physical order rows in the table that are within the file helps to match the logical order of rows. This ordering of data helps in efficient reading the data.
There are the effective solutions with which user can solve the problem as mentioned:
- User must have extra space in file. If the database keeps on increasing or very dynamic, the extra space is important to avoid the growth of file at the production time.
- Another option is to rebuild a complete file. It requires CREATE INDEX with DROP_EXISTING statement for all the indexes that are present in file and can be difficult. The easy solution to use ALTER INDEX REORGANIZE or DBCC INDEXDEFRAG. It helps in rebuilding to remove defragmentation.
Conclusion
In the above discussion, some reasons of issues are discussed by running DBCC SHRIKFILE on SQL Server data file. Even along with this, a solution is also discussed which helps users to remove the issues with running DBCC SHRINKFILE on your SQL Server data files.
Good article, but don't forget if you have large heaps where data has been deleted you might want to rebuild the heaps to free up space that the indexing statements won't touch. I'm always amazed how many large transactional tables are still being created as a heap and how often they significant page that could be freed by rebuilding the heaps or better yet avoid creating heap tables in the first place.
ReplyDelete