July 17, 2009 at 12:47 pm
Hi,
I am trying to use the following script to set the FillFactor on a group of tables and indexes. When I run the script on SQL 2005, it runs but does not change the fillfactor value. Does anyone have an idea?
--SQL to set fill factor and Pad Index on all indexes in database to ?
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 75
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'' and table_name like ''ACTIONS'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
-- SQL 2000 command
--DBCC DBREINDEX(@Table,' ',@fillfactor)
-- SQL 2005 command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ', pad_index = on)'
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
July 17, 2009 at 1:24 pm
Do u have 'ACTIONS' table in any of your database(s)?
July 17, 2009 at 1:36 pm
mmmhhh... wouldn't help to include % signs in "like" predicate option?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 17, 2009 at 1:51 pm
Yes, I do have an ACTIONS table and I have used % in the 'like' as well as table_name = ''ACTIONS'''. I orignally ran this script in 2008 Managment Studio against a sql 2005 database and it shows the new Fillfactor settings. Although, when I use 2005 Managment studio to confirm nothing had changed which led me to run this in 2005.
July 17, 2009 at 1:53 pm
I ran ur scipt on my test database and it changed fillfactor fine for my Indexes on table.
Just include Print and see if your alter for your particular table is being run or not.
[Code]
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ', pad_index = on)'
print ' '
print'Alter Index command'
print @cmd
EXEC (@cmd)
[/Code]
July 17, 2009 at 2:19 pm
Thanks for your insight. I ran it with print and it generated the appropriate alter statments:
Alter Index command
ALTER INDEX ALL ON Ottr.dbo.ZZ$ACTIONS REBUILD WITH (FILLFACTOR = 95, pad_index = on)
Alter Index command
ALTER INDEX ALL ON Ottr.dbo.ENCOUNTER_ACTIONS REBUILD WITH (FILLFACTOR = 95, pad_index = on)
Alter Index command
ALTER INDEX ALL ON Ottr.dbo.ZZ$ENCOUNTER_ACTIONS REBUILD WITH (FILLFACTOR = 95, pad_index = on)
Alter Index command
ALTER INDEX ALL ON Ottr.dbo.PROG_ACTIONS REBUILD WITH (FILLFACTOR = 95, pad_index = on)
Alter Index command
ALTER INDEX ALL ON Ottr.dbo.ZZ$INVOICE_ACTIONS REBUILD WITH (FILLFACTOR = 95, pad_index = on)
I have even ran these separately, and it doesn't change it. This is quite odd.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply