May 12, 2008 at 2:07 pm
Hi,
We recently migrated from SQL 2000 Enterprise to SQL 2005 Enterprise. In 2000 we used a weekly script that rebuilt the indexes, updated usage and then updated statistics. I basically used the same script using the DBCC DBREINDEX. I found over time that the tables that SQL server still was recommending that the tables be rebuilt. I thought that the deprecated reindex command didn't work well in 2005, so I temporarily setup a maintenance plan using the Rebuild Database task using an 80% fill factor.
This ran over the past weekend and now all my databases' .mdf files increased in size by 3 times. After reading some of the posts, the maintenance plan is really too generic and doesn't give you much flexibility in hindsight.
So my biggest problem is the data file sizes since this is causing large backup and log backup files to be created. Performing a shrink on the databases did not decrease the data file size. I was under the impression that just the transaction log increases in size not the data file when indexes are rebuilt. So, now I'm wondering if I can get my databases back to their original size by dropping, then recreating each index? Any suggestions would be helpful for this newbie.:D
May 12, 2008 at 3:45 pm
most commands still work in 2005 but many do not in 2008. Putting a 80% fill factor will make your database about 25% bigger.
this proc will do a full rebuild of all indexes in sql 2005 with a 100% fill factor. You put the proc in master and call from the db you want to re-index. ( you must make it a system proc to work )
or just run the script.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_IndexRebuild100] Script Date: 10/15/2007 13:01:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
create procedure [dbo].[sp_IndexRebuild100]
-- ============================================================================
-- Stored Procedure: sp_IndexRebuild100
-- Written by:Colin Leversuch-Roberts
--Kelem Consulting Limited
--http://www.kelemconsulting.co.uk
--http://www.grumpyolddba.co.uk
--(c) june 2004
--
-- Purpose: To be called from a maint task to enable the rebuilding of indexes on user tables
--This version forces a fill factor of 100%
--
-- System:Master Database
--
-- Input Paramatersnone
--
-- Output Parameters: None
-- Return Status: None
--
-- Usage:Exec dbo.sp_IndexRebuild100
--
-- Calls:alter index
--
-- Notes:By placing this procedure in master it may be called from any database
--The procedure will rebuild the user tables in the database the proc is CALLED FROM
--this proc must be marked as a system proc to work correctly
--
-- VERSION HISTORY
-- Version NoDateDescription
-- 112th Nov 2003Initial Release
-- version of proc sp_IndexRebuild100 with 100% fill factor 8th June 2004 clr
-- 2 sql 2005 version may 2007
--=======================================================================================
as
set nocount on;
declare @table1 table (Numkey int identity(1,1),Tname nvarchar(256));
declare @count int, @cmdstring nvarchar(1000);
set @count=1;
--
-- not a cursor to be seen !! it's a cursor in while clothing!
--
insert into @table1(Tname)
select quotename(s.name)+'.'+quotename(t.[name]) from sys.tables t with (nolock) join sys.schemas s with (nolock)
on t.schema_id = s.schema_id where [type] = 'U';
--
while @count<=(select max(Numkey) from @table1)
BEGIN
select @cmdstring= 'ALTER INDEX ALL ON '+Tname+'
REBUILD WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);' from @table1 where numkey=@count;
print @cmdstring;
exec dbo.sp_executesql @cmdstring;
set @count=@count+1;
END
--endwhile
--endproc
go
exec dbo.sp_ms_marksystemobject sp_IndexRebuild100
go
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 12, 2008 at 3:55 pm
So I'll be able to reduce the size of the indexes using the 100% fill factor? It sure seems counter-intuitive, but I will give this a try. Thank you.
Tom
May 12, 2008 at 4:11 pm
> ... so I temporarily setup a maintenance plan using the Rebuild Database task using an 80% fill factor.
I'm thinking about how you have configured this. In the Maintenance Plan Rebuild Index Task you don't set a fillfactor, you set a 'Change free space per page percentage to'. If you want a 80% fillfactor it should be set to 20%. What did you set it to?
Ola Hallengren
May 12, 2008 at 4:17 pm
I think that rebuiling the indexes with a 100% fillfactor (or 0% free space if you use the Maintenance Plans), backing up the log and then shrinking the database, will reduce the size of the data and log files.
Ola Hallengren
May 12, 2008 at 4:23 pm
Ola, you're right. I've never used the maintenance plans before only scripts and misinterpreted what this percentage represented in the wizard. Serves me right to rush through things. Thank you for the post.
May 12, 2008 at 4:36 pm
>After reading some of the posts, the maintenance plan is really too generic and doesn't give you much flexibility in hindsight.
I have a stored procedure that maybe could be useful for you. It is using the DMV sys.dm_db_index_physical_stats to dynamically rebuild / reorganize indexes based on the fragmentation levels.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Ola Hallengren
May 12, 2008 at 5:12 pm
Thank you. Very cool. Will this work even on databases that are still in Compatibility Level 80?
May 12, 2008 at 5:19 pm
Yes, this will work fine.
It is only the database that you are creating the objects in, that has to be in compatibility level 90. All other databases can be in any compatibility level.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Ola Hallengren
May 12, 2008 at 5:42 pm
Ola, Thank you very much for the script. I will contact your blog if I have any questions.:)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply