automated-index-defrag-script

Question

Post reply

automated-index-defrag-script

Minnu

SSCertifiable

Points: 6451
More actions
March 27, 2014 at 5:25 am

#287040

Hi Team,
Am looking for rebuild / reorganize of indexes using automated process, and i found the code in below link.
http://sqlfool.com/2009/03/automated-index-defrag-script/
can u please help to how to use that code,

Viewing 6 posts - 1 through 6 (of 6 total)

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

dbalmf SSCrazy Points: 2063 More actions · Answer 1

Hi there,

Michelle has an updated version of this script:

http://sqlfool.com/2011/06/index-defrag-script-v4-1/

Running the script creates all the necessary functions and stored procs, a little way down the script is a suggested method of running and a list of which parameters do what. It's a very good script

You may also want to look at Ola Hallengren's script

http://ola.hallengren.com/

============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Minnu SSCertifiable Points: 6451 More actions · Answer 2

Thank You David,

i got more detail and better information from below link

http://ola.hallengren.com/

Thank u once again...:-):-):-)

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 3

You execute a script like this either directly as a SQL Server Agent job or you can create a Maintenance Plan and run the SQL within it. You would still need to create an Agent job.

Once you have the Agent Job you would then schedule it to run. I typically run maintenance jobs during the early morning hours when I know there isn't any user activity or ETL processing taking place.

Hope this helps.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Minnu SSCertifiable Points: 6451 More actions · Answer 4

Hi Team,

Executed below stored procedures from link "http://ola.hallengren.com"

IndexOptimize.sql

CommandExecute.sql

but while executing stored procedures using below parameters, only statistics are udpated, but indexes are not rebuiling / reorganizing.

EXECUTE dbo.IndexOptimize

@Databases = 'TEST_DB',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@UpdateStatistics = 'ALL',

@OnlyModifiedStatistics = 'Y'

Please suggest how to fragment indexes.

tim_harkin SSCrazy Points: 2399 More actions · Answer 5

What is the current fragmentation level, and how large are your indexes?