Sql server 2000 - Reverse the fill factor values after DBCC Reindex

Question

Sql server 2000 - Reverse the fill factor values after DBCC Reindex

ajmomo

Valued Member

Points: 66
More actions
May 30, 2016 at 3:12 pm

#97474

Hi,
I have applied on a huge SQL server 2000 Database DBCC Reindex on all the tables in my Db with the value of fillfactor =100. The Database has a thousands of indexes with the values 85, 75 and 90. Now using the backup of that DB taken before I ran the script, I would like to back out my changes. I executed the script below on the Database with the correct fill factors values and run the result on the database online to fix the fill factor values. this is my script:
=========================
DECLARE @Index_Name VARCHAR(255)
DECLARE @Table_Name VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
DECLARE IndexCursor CURSOR FOR
Select O.name Table_name, I.name As Index_Name, OrigFillFactor
From sysindexes I inner join sysObjects O on I.Id=O.ID
where I.OrigFillFactor>0 and I.OrigFillFactor<100 and O.xtype='U'
order by Table_name, Index_Name
OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @Table_Name,@Index_Name,@fillfactor
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DBCC DBREINDEX( ' + @Table_Name + ',' + @Index_Name + ',' + CONVERT(VARCHAR(3),@fillfactor) + ')'
Print(@cmd)
FETCH NEXT FROM IndexCursor INTO @Table_Name,@Index_Name,@fillfactor
END
CLOSE IndexCursor
DEALLOCATE IndexCursor
---------------
======= TEST THE RESULT=============================
To test the results,
select OrigFillFactor, count(*) from sysindexes group by OrigFillFactor
i run the query above on both databases and compare the results. Unfortunately the results are different. Am I wrong somewhere?
Thanks for your help.
Abdiel.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

NM. I didn't see @Cmd getting executed but then see the results of the cursor were captured on the screen and those were executed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ajmomo Valued Member Points: 66 More actions · Answer 2

Thanks for your reply. Actually print(@cmd) generates the DBCC Reindex queries from the database where the fillfactor values are correct, and then I execute all those queries in a Query analyzer on the database where I want to fix the fillfactor values.

If you any idea, please share.

thanks.