The database maintenance on our data mart SQL server is causing problems.The re-index process causes too much file growth and the processes are unable to complete due to disk space constraints.I want to write a process to re-index the database, but handle this a single file group at a time and then shrink the file group before going to the next one. I'm looking to write a maintenance routine (stored procedure) that will get all of the tables from a file group and re-index them. Any general ideas would be greatly appreciated.
I think I've found a reindexing solution to this problem I posted. I'm posting an answer here and if anyone wants to comment thats fine but I am doing it to help out other developers out there. If you scroll down farther you can see that I only reindexed clustered indexes in this Database and that is because when you reindex clustered indexes, tables with non-clustered indexes are automatically reindexed as well I believe.
-- cursor to loop through File Groups
DECLARE FG CURSOR FOR
SELECT DISTINCT groupid
FROM sysfilegroups
OPEN FG
FETCH NEXT FROM FG INTO @.fgroupid
WHILE @.@.Fetch_status=0
BEGIN
-- Cursor to loop through filenames
DECLARE FILENAME CURSOR FOR
SELECT DISTINCT f.name
FROM sysfiles f
INNER JOIN sysfilegroups fg ON f.groupid = @.fgroupid
OPEN FILENAME
FETCH NEXT FROM FILENAME INTO @.fname
WHILE @.@.Fetch_status=0
BEGIN
PRINT 'Current Filename = ' + @.fname
--Table Name Cursor
DECLARE TBL CURSOR FOR
--This query selects tables with clustered indexes only
SELECT DISTINCT 'DBCC DBREINDEX (' + i.TABLE_NAME + ')'
FROM INFORMATION_SCHEMA.TABLES i
INNER JOIN sysindexes si ON i.TABLE_NAME = object_name(si.id)
INNER JOIN sysfiles sf ON sf.groupid = si.groupid
WHERE objectProperty(object_id(i.TABLE_NAME), 'IsUserTable') = 1
AND objectProperty(object_id(i.TABLE_NAME), 'TableHasClustIndex')=1
AND sf.filename=@.fname
OPEN TBL
FETCH NEXT FROM TBL INTO @.tname
WHILE @.@.Fetch_status=0
BEGIN
PRINT @.SQLA
EXEC (@.SQLA)
FETCH NEXT FROM TBL INTO @.SQLA
END
CLOSE TBL
DEALLOCATE TBL
FETCH NEXT FROM FILENAME INTO @.fname
END
SELECT @.SQLB = 'DBCC SHRINKFILE ('+ @.fname+ ')'
EXEC (@.SQLB)
CLOSE FILENAME
DEALLOCATE FILENAME
FETCH NEXT FROM FG INTO @.fgroupid
