Script to Move Indexes off of the Primary Filegrou

,

This is a stored proc that will move your indexes to two new file groups. A Cindex file group is for the clustered indexes and NCindex file group is for non clustered indexes. The parameters for this procedure are

@CPATH - the path to the clustered index datafiles
@NCPATH - the path to the nonclustered index datafiles
@numcfiles - how many files in the Cindex file group
@numncfiles - how many files in the NCindex file group
@startsize - the size the data files start at
@maxsize - the maximum size of the datafiles
@growthsize - how many megabytes the file grows in.

After the indexes have been moved it removes all extra files in the primary filegroup except for the default file and one extra.

create procedure moveindexes   
 @CPATH varchar(100) = 'D:\SQLCINDEX\',   
 @NCPATH varchar(100) = 'D:\SQLINDEX\',   
 @numcfiles int = 1,  
 @numncfiles int = 1,  
 @startsize int = 1000,  
 @maxsize int = 10000,  
 @growthsize int = 1000  
as  
  
 declare @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  ,  
   @objname nvarchar(776),  
   @FILLFACT TINYINT,  
   @ISQL VARCHAR(5000),  
   @kill int,  
   @counter int  
     
set @counter = 1  
set @dbname = (select name from master..sysdatabases d, master..sysprocesses p where spid = @@spid and d.dbid = p.dbid )  
  
declare kill_cursor cursor for select spid from master..sysprocesses  where spid > 49 and spid <>  @@spid and dbid = (select dbid from master..sysdatabases where name = @dbname)  
open kill_cursor  
fetch next from kill_cursor into @kill  
while @@fetch_status = 0  
begin  
    set @isql = 'kill '+convert(varchar(5),@kill)  
    exec (@isql)  
    fetch next from kill_cursor into @kill  
end  
close kill_cursor  
deallocate kill_cursor  
exec master..sp_dboption @dbname, 'Single User', 'TRUE'  
print 'Database '+@dbname+' Has been set to Single User Only'  
select rtrim(name)as name into ##dbfiles from sysfiles where substring(fn_replinttobitstring(status),12,1) = 1 and fileid <> (select min(fileid) from sysfiles where groupid = 1)  
  
set @isql = 'ALTER DATABASE '+@dbname+ ' ADD FILEGROUP CINDEX'  
exec (@isql)  
print 'File Group CINDEX Has been Created'  
exec (@isql)  
set @isql = 'ALTER DATABASE '+@dbname+ ' ADD FILEGROUP NCINDEX'  
exec (@isql)  
print 'File Group NCINDEX Has been Created'  
exec (@isql)  
  
  
while @counter <= @numcfiles  
begin  
   set @isql = 'ALTER DATABASE '+@dbname+ ' ADD File (Name = CINDEXData'+convert(varchar(2), @counter)+',   FILENAME = ' + '"' + @CPATH +'CINDEXData'+convert(varchar(2), @counter)+'.ndf'+'"'+',  SIZE = '+convert(varchar(5),@startsize) + 'MB, MAXSIZE = '+convert(varchar(5),@maxsize)+'MB, FILEGROWTH = '+convert(varchar(5),@growthsize)+'MB) TO FILEGROUP CINDEX'  
   exec (@isql)  
   print 'File CINDEXDATA'+convert(varchar(2), @counter)+' has been created'  
   set @counter = @counter + 1  
end  
set @counter = 1  
while @counter <= @numncfiles  
begin  
   set @isql = 'ALTER DATABASE '+@dbname+ ' ADD File (Name = NCINDEXData'+convert(varchar(2), @counter)+',   FILENAME = ' + '"' + @NCPATH +'NCINDEXData'+convert(varchar(2), @counter)+'.ndf'+'"'+',  SIZE = '+convert(varchar(5),@startsize) + 'MB, MAXSIZE = '+convert(varchar(5),@maxsize)+'MB, FILEGROWTH = '+convert(varchar(5),@growthsize)+'MB) TO FILEGROUP NCINDEX'  
   exec (@isql)  
   print 'File NCINDEXDATA'+convert(varchar(2), @counter)+' has been created'  
   set @counter = @counter + 1  
end  
  
  
exec master..sp_dboption @dbname, 'trunc. log on chkpt.', 'TRUE'  
print ' Database ' +@dbname + ' Has been set to simple Recovery Mode'  
DECLARE TCURSOR CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE TYPE = 'U' ORDER BY NAME ASC  
OPEN TCURSOR  
FETCH NEXT FROM TCURSOR INTO @OBJNAME  
WHILE @@FETCH_STATUS = 0  
BEGIN  
  
   
   select @objid = object_id(@objname)    
   create table #spindtab    
     (    
     index_name   sysname collate database_default NOT NULL,    
     stats    int,    
     groupname   sysname collate database_default NOT NULL,    
     index_keys   nvarchar(2126) collate database_default NOT NULL, -- see @keys above for length descr    
     FILLFACT TINYINT  
     )    
  
  declare ms_crs_ind cursor local static for    
  select indid, groupid, name, status, ORIGFILLFACTOR 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, @FILLFACT  
    
 while @@fetch_status >= 0    
 begin    
  declare @i int, @thiskey nvarchar(131) -- 128+3    
    
  select @keys = index_col(@objname, @indid, 1), @i = 2    
  if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)    
   select @keys = @keys  + '(-)'    
    
  select @thiskey = index_col(@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(@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 into #spindtab values (@indname, @status, @groupname, @keys, @FILLFACT)    
    
  -- Next index    
  fetch ms_crs_ind into @indid, @groupid, @indname, @status, @FILLFACT    
 end    
 deallocate ms_crs_ind    
    
 -- 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    
declare @tcount int  
set @tcount = (select count(*) from sysobjects where type = 'U' and name > @objname)  
print 'Recreating Indexes for Table ' + @objname + '.  ' + convert(varchar(5),@tcount) + ' Tables Remaining!'  
DECLARE @INAME VARCHAR(100)  
DECLARE ICURSOR CURSOR FOR SELECT INDEX_NAME FROM #SPINDTAB ORDER BY INDEX_NAME   
OPEN ICURSOR  
FETCH NEXT FROM ICURSOR INTO @INAME  
WHILE @@FETCH_STATUS = 0   
BEGIN  
 SET @ISQL = ( select  'Create  '  + case when (stats & 2)<>0 then @des2 + '  ' else @empty end +   
 case when (stats & 16)<>0 then 'CLUSTERED  INDEX ' else ' INDEX ' end +  
 index_name + ' ON ' + @objname + ' ('+index_keys +') WITH '+  
   CASE WHEN FILLFACT = 0 THEN ' DROP_EXISTING' ELSE ' FILLFACTOR = '+CONVERT(CHAR(2),FILLFACT)+', DROP_EXISTING' END +  
 ' ON ' + case when (stats & 16)<>0 then ' [CINDEX]' else ' [NCINDEX]' end  
 from #spindtab  WHERE INDEX_NAME = @INAME)  
  
exec (@ISQL)  
FETCH NEXT FROM ICURSOR INTO @INAME  
END  
CLOSE ICURSOR  
DEALLOCATE ICURSOR  
  
drop table #spindtab  
FETCH NEXT FROM TCURSOR INTO @OBJNAME  
END  
CLOSE TCURSOR  
DEALLOCATE TCURSOR  
  
  
exec master..sp_dboption @dbname, 'trunc. log on chkpt.', 'FALSE'    
print 'Database ' +@dbname + ' Has been set to full Recovery Mode'  
  
  
exec master..sp_dboption @dbname, 'Single User', 'False'  
print 'Database '+@dbname+' Has been set back to Multi User'  
  
select getdate()  
  
  
  
declare @fname varchar(50)  
declare @sql varchar(1000)  
-- declare @dbname varchar(50)  
declare file_cursor cursor for select name from ##dbfiles  
set @dbname = (select name from master..sysdatabases d, master..sysprocesses p where spid = @@spid and d.dbid = p.dbid )  
open file_cursor  
fetch next from file_cursor into @fname  
while @@fetch_status = 0  
begin  
set @sql = 'dbcc shrinkfile ('+@fname+', emptyfile)'  
exec (@sql)  
print @fname + ' Has been emptied'  
set @sql = 'ALTER DATABASE '+@dbname +' REMOVE FILE '+@fname  
exec(@sql)  
print @fname + ' Has been removed'  
fetch next from file_cursor into @fname  
end  
close file_cursor  
deallocate file_cursor

Rate

1 (1)

Share

Share

Rate

1 (1)