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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc [dbo].[sp_helpindex3] --@objname nvarchar(776) -- the table to check for indexes as set nocount on declare @objname nvarchar(776), @objid int, -- the object id of the table @indid smallint, -- the index id of an index @groupid smallint, -- the filegroup id of an index @indname sysname, @groupname sysname, @status int, @keys nvarchar(2126), --Length (16*max_identifierLength)+(15*2)+(16*3) @dbname sysname, @usrname sysname -- Check to see that the object names are local to the current database. select @dbname = parsename(@objname,3) if @dbname is not null and @dbname <> db_name() begin raiserror(15250,-1,-1) return (1) end -- create temp table create table #spindtab ( usr_name sysname, table_name sysname, index_name sysname collate database_default, stats int, groupname sysname collate database_default, index_keys nvarchar(2126) collate database_default -- see @keys above for length descr ) -- OPEN CURSOR OVER TABLES (skip stats: bug shiloh_51196) declare ms_crs_tab cursor local static for select sysobjects.id, sysobjects.name, sysusers.name from sysobjects inner join sysusers on sysobjects.uid = sysusers.uid where type = 'U' open ms_crs_tab fetch ms_crs_tab into @objid, @objname, @usrname while @@fetch_status >= 0 begin -- Check to see the the table exists and initialize @objid. /* select @objid = object_id(@objname) if @objid is NULL begin select @dbname=db_name() raiserror(15009,-1,-1,@objname,@dbname) return (1) end */ -- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196) declare ms_crs_ind cursor local static for select indid, groupid, name, status from sysindexes where id = @objid and indid > 0 and indid < 255 and (status & 64)=0 order by indid open ms_crs_ind fetch ms_crs_ind into @indid, @groupid, @indname, @status while @@fetch_status >= 0 begin -- First we'll figure out what the keys are. declare @i int, @thiskey nvarchar(131) -- 128+3 select @keys = index_col(@usrname + '.' + @objname, @indid, 1), @i = 2 if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1) select @keys = @keys + '(-)' select @thiskey = index_col(@usrname + '.' + @objname, @indid, @i) if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1)) select @thiskey = @thiskey + '(-)' while (@thiskey is not null ) begin select @keys = @keys + ', ' + @thiskey, @i = @i + 1 select @thiskey = index_col(@usrname + '.' + @objname, @indid, @i) if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1)) select @thiskey = @thiskey + '(-)' end select @groupname = groupname from sysfilegroups where groupid = @groupid -- INSERT ROW FOR INDEX insert into #spindtab values (@usrname, @objname, @indname, @status, @groupname, @keys) -- Next index fetch ms_crs_ind into @indid, @groupid, @indname, @status end deallocate ms_crs_ind fetch ms_crs_tab into @objid, @objname, @usrname end deallocate ms_crs_tab -- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY declare @empty varchar(1) select @empty = '' declare @des1 varchar(35), @des2 varchar(35), @des4 varchar(35), @des32 varchar(35), @des64 varchar(35), @des2048 varchar(35), @des4096 varchar(35), @des8388608 varchar(35), @des16777216 varchar(35) select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1 select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2 select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4 select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32 select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64 select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048 select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096 select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608 select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216 -- DISPLAY THE RESULTS select 'usr_name'=usr_name, 'table_name'=table_name, 'index_name' = index_name, 'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end + case when (stats & 1)<>0 then ', '+@des1 else @empty end + case when (stats & 2)<>0 then ', '+@des2 else @empty end + case when (stats & 4)<>0 then ', '+@des4 else @empty end + case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end + case when (stats & 2048)<>0 then ', '+@des2048 else @empty end + case when (stats & 4096)<>0 then ', '+@des4096 else @empty end + case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end + case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end + ' located on ' + groupname), 'index_keys' = index_keys from #spindtab order by table_name, index_name return (0) -- sp_helpindex GO |
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.
