SQL Server Data Dosyasını Shrink Etmek Mi?!
“SQL Server Log Dosyasını Shrink Etmek” isimli makalemizde konusu geçtiği için ve data dosyasını shrink etme işlemin bazı sakıncaları olduğu için bu makaleyi yazmak istedim.
Evet data dosyaları shrink edilebilir. Ama data dosyalarınızı shrink ederseniz bu dosyalardaki index fragmantation çok yüksek oranda artacaktır. Index deyip geçmeyin. Tablolarımızda clustered index’lerimizde buna dahil. “Clustered Index ve Non Clustered Index Farkları” isimli makalemi okumak isteyebilirsiniz.
Bütün bunların yanı sıra gözümle görmesem bile bir forumda data file’ı shrink etmeye çalışırken veritabanını suspect mode’a düşürmüş biri ile karşılaştım. Zaten hiç hoşuma gitmeyen bir işlemden bu şekilde iyice uzaklaşmış oldum.
Peki data file’ı shrink etmek zorunda kalırsak ne yapacağız?
Geçenlerde başıma gelen bir olayı anlatayım. Bir arkadaş production ortamda X veritabanında test amaçlı bir tablo oluşturmuş ve bu tabloya 10 TB veri insert etmiş. Evet 10 TeraByte. Böyle bir durumda yapılabilecek 2 şey var.
1)Test amaçlı oluşturduğu tablonun bulunduğu file group’taki bütün data file’ları shrink etmek. Yukarda anlattığım şeyler üzerine bunu kesinlikle yapmayacağımızı anlamış olmalısınız.
2)Yeni bir file group oluşturup shrink etmemiz gereken tablonun olduğu file group’taki bütün indexleri(Clustered-Non Clustered) yeni file group’a taşımak. Bu işlemi nasıl yapacağınızın detaylarını “Veritabanı file group yapısı ve büyük tablolarımızı başka bir file group’ta yeniden oluşturmak” isimli makalemde bulabilirsiniz. Hangi index hangi file group üzerinde tespit edebilmek için [sp_helpindex3] isminde çok faydalı bir stored procedure var. Bu stored procedure’ü aşağıdaki script yardımıyla ilgili veritabanında oluşturabilirsiniz.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Modified sp_helpindex SP to show all indexes for all tables -- this was modified to handle object owned by dbo and other users CREATE proc [dbo].[sp_helpindex3] --@objname nvarchar(776) -- the table to check for indexes as -- PRELIM 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 -- IF NO INDEX, QUIT --if @@fetch_status < 0 --begin --deallocate ms_crs_ind --raiserror(15472,-1,-1) --'Object does not have any indexes.' --return (0) --end -- Now check out each index, figure out its type and keys and -- save the info in a temporary table that we'll print out at the end. 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), -- 35 matches spt_values @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
Bu işlemi yaptıktan sonra shrink edeceğimiz filegroup’ta hiç veri kalmayacaktır. Dolayısıyla bu filegroup’u silebiliriz. Eğer shrink edilmesi gereken file group PRIMARY file group ise bu file group’ı silemeyiz. Ama artık içinde kullanıcı datası kalmadığı için shrink edebiliriz.
Önemli bir not olarak kesinlikle veritabanlarınızda auto shrink’i açmayın.
