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.

Loading

Leave Your Comment