Index Defrag Script, v3.0

Similar posts

• Tidbits I Discovered Today… — I’ve figured out a couple of tidbits today that I w...
• BCP Script Generator — I’m currently working on the logic migration of dat...
• Are You Approaching Your Partition Ra... — In my post last week, How To Estimate Data Utilization, I...
• How To Estimate Data Utilization — Recently, on a conference call presenting data growth rat...
• Calculate Rows Inserted per Second fo... — Ever needed to calculate the number of rows inserted ever...

1. Michelle Ufford
   November 8, 2009 at 2:35 pm | Permalink

   @Matt the script will only defrag any index with 8 or more pages, which follows Microsoft’s best practices recommendation. I don’t have the AdventureWorksLT database handy, but I would guess that most of those indexes have less than 8 pages, which is probably why you’re not seeing any change after running the defrag script.

2. Michelle Ufford
   November 8, 2009 at 2:38 pm | Permalink

   @Martin You’re getting those errors because the tables already exist, which means some version of the script has already been executed once. In my script, I do not automatically drop the tables, just to err on the safe side (i.e. you want historical defrag information). To update to the latest version, just comment out the section that creates the tables that already exist (probably just dba_indexDefragLog, since dba_indexDefragExclusion is new in this version). After that, you can call the script with “Execute dba_indexDefrag_sp;” (uses all default parameters).

3. Martin
   November 9, 2009 at 2:12 am | Permalink

   Hi Michelle,

   Thanks for the answer Michelle.
   I’m running the script against several heavily fragmented databases. I took a a script from Brent Ozars Blog and got that result.
   Now I Imagined that the tables could be the cause so I deleted them but still the result is the same. I realized later be running the newly created stored procedure I could defragment the indexes.

   Isn’t it the intend of the scipt to run it right away or do you have to run the SP separately?

   Thanks

4. Jugal Shah
   November 18, 2009 at 4:30 am | Permalink

   Great Work buddy

5. Olek
   November 21, 2009 at 1:16 pm | Permalink

   I suggest script should skip disabled indexes.

6. Jarno
   November 23, 2009 at 2:25 am | Permalink

   Hi Michelle,

   Thanks for the script, it looks great! But I can’t seem to manage to run the script.

   Since the very first time I tried it, it only says this: (yes that includes the first time too)

   dba_indexDefragLog Table Created
   dba_indexDefragExclusion Table Created
   Procedure dba_indexDefrag_sp dropped

   After that it doesn’t do anything at all.

   I tried it on 2 different servers in SQL Server Management Studio:
   Windows Server 2008,
   64bit based,
   SQL Server 2008 SP 1
   Microsoft SQL Server Management Studio 10.0.2531.0
   Microsoft Analysis Services Client Tools 10.0.1600.22
   Microsoft Data Access Components (MDAC) 6.0.6002.18005
   Microsoft MSXML 3.0 4.0 5.0 6.0
   Microsoft .NET Framework 2.0.50727.4200

   Things I changed in the script are:

   @DATABASE VARCHAR(128) = ‘WSS_Content_5a0475f003cb474da06f105ad8877434′ — instead of NULL, only want to test it on this database
   @printCommands BIT = 1 — instead of 0
   @printFragmentation BIT = 1 — instead of 0
   @debugMode BIT = 1 — instead of 0

   Thank in advantage.

7. Jarno
   November 23, 2009 at 2:28 am | Permalink

   make that Windows Server 2008 SP2

8. Jim
   November 30, 2009 at 12:19 am | Permalink

   Hi Michelle, I am using your greate script on a couple databases and i works like a charm until I lunch on my main DB. The script runs through the indexes until this error: “Msg 8152, Level 16, State 13, Line 1
   String or binary data would be truncated”
   I have run it several times, also including debug and print statements, so I can see the last running statement. BUT I can´t find out which statement gives me the error or where it comes from.
   Can you help or does any one else have a clue ?

9. EstebanD
   December 12, 2009 at 10:54 pm | Permalink

   Hi Michelle,

   Thank you for sharing your script and experiences.

   My question is probably dumb…

   Why when running this script again after rebuilding/reindexing still proposes the same number of indexes and the same fragmentation?

   I even manually rebuilt all indexes in the DB and the executed this SP and again it didn’t change.

   Also some fragmentation numbers seem high after running all these. 53.84, 77.77

   Thank you!

10. Duncan McCormack
    December 13, 2009 at 3:44 am | Permalink

    Great stuff Michelle.

    I’m wondering if you’ve ever looked at the MS T-SQL that they have online for defraging WSUS databases.

    What I like about the code, is that it defrags based on fragmentation levels.

    Perhaps that could be of use to you in your SQL?

    Here’s the link:
    http://go.microsoft.com/fwlink/?LinkId=87027

    Whaddya think?

11. Duncan McCormack
    December 13, 2009 at 3:50 am | Permalink

    OK, now that I’ve actually *read* your code – I see ya doin that!

12. Michelle Ufford
    December 13, 2009 at 8:50 am | Permalink

    @Jarno you now want to open up a new query window and execute *only* this:

    Exec dbo.dba_indexDefrag_sp
    @DATABASE VARCHAR(128) = ‘WSS_Content_5a0475f003cb474da06f105ad8877434′
    , @printCommands BIT = 1
    , @printFragmentation BIT = 1
    , @debugMode BIT = 1

13. Michelle Ufford
    December 13, 2009 at 8:55 am | Permalink

    @Esteban This script won’t affect the number of indexes that exist. If the fragmentation numbers are not changing, look at the page count in your index. If it’s a low number, you’re not going to see fragmentation numbers change. Here’s what Microsoft has to say about it: “Rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding it.”

14. Michelle Ufford
    December 13, 2009 at 8:57 am | Permalink

    @Jim I’m wondering if it’s not an issue with the logging. Do you have any really long database, table, or index names?

15. EstebanD
    December 14, 2009 at 8:57 pm | Permalink

    Hi Michelle,

    Thank you for the detailed explanation.

16. Jarno
    December 15, 2009 at 5:31 am | Permalink

    Thanks for the reply Michelle.

    The code you gave didn’t work with copy/paste, but with some research in the database I made it working.

17. Chris Reeder
    December 22, 2009 at 8:27 am | Permalink

    Is there a version of this script that will work with SQL 2000?

    BTW: It works great on our other SQL servers!

18. mamata
    December 22, 2009 at 11:42 am | Permalink

    Is it safe to create this procedure in Master database as it creating couple of tables?

    thanks

19. rgauch
    December 23, 2009 at 8:18 am | Permalink

    Been running this for a while, but came across error on one of our servers stating reorganization failed because page-level locks are disallowed. Overcame by adding exists clause to reorganize criteria with a query to sys.sysindexes for the index and lockflags = 0. Indexes that disallow page level locks now get rebuilt instead of reorganized.

    Awesome script by the way.

20. Carol Noel
    December 23, 2009 at 7:30 pm | Permalink

    Hi Michelle:
    This is fantastic. I have been testing and will deploy. A few things –

    1) Noticed same minor problem posted above by Vic Prahu on Wed, 24th Jun 2009 2:01 pm. Seems to happen when a table has more that one index selected for defrag.

    2) can this script be used for databases on a SQL2K5 box with compatability level 80 ?

    3) I modified code a bit to exclude processing of an entire database. Modified the exclusion table a bit as well to handle this.

    Looking forward to next enhancements.

21. dhjackal
    January 7, 2010 at 3:59 am | Permalink

    Hi Michelle,

    Excellent procedure and thanks for sharing it with us.

    I’ve used it as the basis for all of my index defragmentation management across all of my production servers. I call it slightly differently though. I use a “wrapper” procedure (usp_ProcRunner) to call all of my procedures / functions and I pass a list of databases that I want the proc to run against into this which subsequently passes it down to the called proc.

    i.e.

    EXEC DBA_Development.dbo.usp_ProcRunner
    @Databases = ‘database1, database2′ — Can be USER, SYSTEM, ALL or a list of databases
    , @ProcCall = ‘EXECUTE DBA_Development.dbo.usp_IndexDefrag @minFragmentation = 5, @rebuildThreshold = 30, @maxDopRestriction = 2′
    , @DebugMode = ‘N’
    GO

    I too have opted to include the SORT_IN_TEMPDB option to the rebuild index statement.

    I have a couple of queries.

    1) Any reason why you don’t just update the statistics for the objects that are reorganised rather than all the objects in the db?

    2) You don’t include a FILL_FACTOR in your rebuild statement. Any particular reason for this?

22. Gmamata
    January 25, 2010 at 12:06 pm | Permalink

    Can we create these tables & procedures in Master database? Or do we need to create a separate Admin database & create the above tables & procedures?

    what is the best method

    thanks

23. Michelle Ufford
    January 26, 2010 at 9:42 am | Permalink

    @Gmamata yes, you can create the table and proc in the Master database, but I typically recommend the creation of a database for DBA-related administration. I typically call mine something like “DBA_Sandbox.”

24. Michelle Ufford
    January 26, 2010 at 9:47 am | Permalink

    @dhjackal sp_updatestats will only update the statistics that need it. So it’s just as easy and possibly more of a catch-all to run it for all objects in the database.

    The reason I don’t include a FILL FACTOR is I can’t think of an occasion where I would ever want to bulk-change the FILL FACTOR setting for all indexes. Right now, I consider the script fairly harmless, but I would consider that a potential accident waiting to happen if someone were not aware of what he/she was doing, set the value, and ran it for all databases and tables.

25. Michelle Ufford
    January 26, 2010 at 9:49 am | Permalink

    @Chris Sorry, this version only works with SQL Server 2005 and above because of its extensive use of DMV’s.

26. Michelle Ufford
    January 26, 2010 at 10:44 am | Permalink

    @Carol I haven’t been able to duplicate the issue you mention, although I’m not entirely sure I understand what’s happening, either. Any input would be appreciated.

    I don’t think it’ll work with a database compatibility level = 80.

    Thanks for your comments!

27. Gmamata
    January 30, 2010 at 1:38 pm | Permalink

    Hi Michelle,

    Is this latest version of the script that has addressed some of the issues by users (like Derick and SuperCoolMoss pointed about the stats rebuild feature in your defrag script will only complete for one database.)?

    Thank you

28. Dale Langham
    February 2, 2010 at 9:20 am | Permalink

    Hi,
    Not sure where I had this script from, but I have been using it for ages now, basically it defrags indexes over a period of 7 days to spread the load, but for some reason it doesn’t like multiple schemas, as it adds dbo to all tables found etc… any ideas to make this work for all schemas would be great:

    IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(‘dbo.DBMaint_Task’) AND xtype = ‘U’)
    BEGIN
    DROP TABLE dbo.DBMaint_Task
    END
    GO

    CREATE TABLE dbo.DBMaint_Task(
    MaintTaskId int NOT NULL IDENTITY(1, 1),
    Interval int NOT NULL,
    Command nvarchar(4000) NOT NULL,
    ItemWork numeric(10,2) NOT NULL,
    CompletedDatetime datetime NULL
    CONSTRAINT PK_DBMaint_Task PRIMARY KEY CLUSTERED(MaintTaskID)
    )
    GO

    IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(‘dbo.p_maint_ScheduleMaintTasks’) AND xtype = ‘P’)
    BEGIN
    DROP PROCEDURE dbo.p_maint_ScheduleMaintTasks
    END
    GO

    CREATE PROCEDURE dbo.p_maint_ScheduleMaintTasks
    @PeriodLength int, – Number of intervals in a period
    @IndexRebuildThreshold int = 10 – Maximum LogicalFragmentation value allowed before an index is rebuilt
    AS

    DECLARE @WorkPerIntervalGoal numeric(10,2)
    DECLARE @CurrentInterval int
    DECLARE @RowsUpdated int
    DECLARE @MainTaskId int

    SET NOCOUNT ON

    IF @PeriodLength = @IndexRebuildThreshold
    AND
    ObjectId OBJECT_ID(‘dbo.DBMaint_Task’)

    – If we rebuild a clustered index, we don’t need to rebuild
    – any nonclustered indexes on the same table
    DELETE FROM
    rc1
    FROM
    #ReindexCandidate rc1
    INNER JOIN
    #ReindexCandidate rc2
    ON(
    rc1.ObjectId = rc2.ObjectId
    )
    WHERE
    rc2.IndexID = 1 — IndexID = 1 means the clustered index
    AND
    rc1.IndexID 1

    TRUNCATE TABLE dbo.DBMaint_Task

    INSERT INTO dbo.DBMaint_Task(
    Interval,
    Command,
    ItemWork)
    SELECT
    -1 AS Interval,
    N’DBCC DBREINDEX(”’ + USER_NAME(OBJECTPROPERTY(rc1.ObjectId, ‘OwnerId’)) + N’.’ + rc1.ObjectName + N”” +
    CASE IndexId WHEN 1 THEN N” ELSE N’, ”’ + IndexName + N”” END + N’)’ AS Command,
    (SELECT SUM(dpages) FROM dbo.sysindexes si1 WHERE id = rc1.ObjectId) AS ItemWork
    FROM
    #ReindexCandidate rc1

    – Find all tables that we’re not doing a clustered index rebuild on
    INSERT INTO dbo.DBMaint_Task(
    Interval,
    Command,
    ItemWork)
    SELECT
    -1 AS Interval,
    N’UPDATE STATISTICS [' + USER_NAME(uid) + N'].[' + so1.name + N'] WITH FULLSCAN’ AS Command,
    (SELECT SUM(dpages) FROM dbo.sysindexes si1 WHERE id = so1.id) * 0.40 AS ItemWork
    FROM
    dbo.sysobjects so1
    LEFT OUTER JOIN
    #ReindexCandidate rc1
    ON
    (
    rc1.ObjectId = so1.id
    AND
    rc1.IndexId = 1
    )
    WHERE
    so1.xtype = ‘U’
    AND
    rc1.ObjectId IS NULL
    AND
    (SELECT SUM(dpages) FROM dbo.sysindexes si1 WHERE id = so1.id) 0
    AND
    so1.id OBJECT_ID(‘dbo.DBMaint_Task’)

    DELETE FROM
    dbo.DBMaint_Task
    WHERE
    ItemWork = 0

    – Break all ties by ItemWork
    – This algorithm is arbitrary, but the amount of estimated work it adds is trival
    SET @RowsUpdated = 1
    WHILE @RowsUpdated 0
    BEGIN
    UPDATE
    mt1
    SET
    ItemWork = ItemWork + 0.01
    FROM
    dbo.DBMaint_Task mt1
    INNER JOIN
    (
    SELECT
    MAX(MaintTaskId) AS MaintTaskId
    FROM
    dbo.DBMaint_Task
    GROUP BY
    ItemWork
    HAVING
    COUNT(*) > 1
    ) mt2
    ON
    (
    mt1.MaintTaskId = mt2.MaintTaskId
    )

    SET @RowsUpdated = @@ROWCOUNT
    END

    SET @CurrentInterval = 1
    WHILE @CurrentInterval = mt1.ItemWork
    AND
    mt2.Interval = -1
    ) <= @WorkPerIntervalGoal

    SET @RowsUpdated = @@ROWCOUNT

    IF @RowsUpdated = 0
    BEGIN
    UPDATE
    mt1
    SET
    Interval = @CurrentInterval
    FROM
    dbo.DBMaint_Task mt1
    WHERE
    mt1.ItemWork = (
    SELECT
    MAX(mt2.ItemWork)
    FROM
    dbo.DBMaint_Task mt2
    WHERE
    mt2.Interval = -1
    )
    END

    SET @CurrentInterval = @CurrentInterval + 1
    END

    SET NOCOUNT OFF

    GO

    IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('dbo.p_maint_ExecuteMaintTasks') AND xtype = 'P')
    BEGIN
    DROP PROCEDURE dbo.p_maint_ExecuteMaintTasks
    END
    GO

    CREATE PROCEDURE dbo.p_maint_ExecuteMaintTasks
    @PeriodLength int = 7, – Number of intervals in a period
    @IndexRebuildThreshold int = 10, – Maximum LogicalFragmentation value allowed before an index is rebuilt
    @ForceInterval int = NULL, – Manually set the current interval rather than calculating it from the date
    @ForceRebuildTaskList int = 0, – If non-zero, force a rebuild of the task list
    @RebuildTaskListInterval int = 1 – The interval in which to automatically rebuild the task list
    AS

    DECLARE @CurrentInterval int
    DECLARE @TaskCount int
    DECLARE @Command nvarchar(4000)
    DECLARE @MaintTaskId int

    SET NOCOUNT ON

    IF @PeriodLength < 1
    BEGIN
    RAISERROR(N'@PeriodLength must be greater than zero.', 16, 1)
    RETURN
    END

    IF @ForceInterval IS NOT NULL
    BEGIN
    IF @ForceInterval @PeriodLength
    BEGIN
    RAISERROR(N’If not null, @ForceInterval must be less than or equal to @PeriodLength.’, 16, 1)
    RETURN
    END
    END

    IF @RebuildTaskListInterval > @PeriodLength
    BEGIN
    RAISERROR(N’@RebuildTaskListInterval must be less than or equal to @PeriodLength.’, 16, 1)
    RETURN
    END

    IF NOT @IndexRebuildThreshold BETWEEN 0 AND 100
    BEGIN
    RAISERROR(N’@IndexRebuildThreshold must be between 0 and 100.’, 16, 1)
    RETURN
    END

    SELECT
    @TaskCount = COUNT(*)
    FROM
    dbo.DBMaint_Task
    WHERE
    CompletedDatetime IS NULL

    IF @ForceInterval IS NULL
    BEGIN
    SELECT
    @CurrentInterval = ((YEAR(GETDATE() – 59) * 365 + DATEPART(dy, GETDATE() – 59)) % @PeriodLength) + 1
    END
    ELSE
    BEGIN
    SELECT
    @CurrentInterval = @ForceInterval
    END

    IF @TaskCount = 0 OR @ForceRebuildTaskList 0 OR @CurrentInterval = @RebuildTaskListInterval
    BEGIN
    EXEC dbo.p_maint_ScheduleMaintTasks
    @PeriodLength = @PeriodLength,
    @IndexRebuildThreshold = @IndexRebuildThreshold
    END

    DECLARE csr_Task CURSOR FAST_FORWARD FOR
    SELECT
    MaintTaskId,
    Command
    FROM
    dbo.DBMaint_Task
    WHERE
    Interval = @CurrentInterval
    AND
    CompletedDatetime IS NULL

    OPEN csr_Task

    FETCH NEXT FROM csr_Task INTO @MaintTaskId, @Command
    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT CONVERT(nvarchar(30), GETDATE(), 121) + N’: Executing “‘ + @Command + N’”‘
    EXEC(@Command)
    PRINT ”

    UPDATE
    dbo.DBMaint_Task
    SET
    CompletedDatetime = GETDATE()
    WHERE
    MaintTaskId = @MaintTaskId

    FETCH NEXT FROM csr_Task INTO @MaintTaskId, @Command
    END

    CLOSE csr_Task
    DEALLOCATE csr_Task

    SET NOCOUNT OFF
    GO

29. Dale Langham
    February 2, 2010 at 9:22 am | Permalink

    After running the script, you should run the following command to analyze and re-index the entire database:
    EXEC dbo.p_maint_ExecuteMaintTasks @PeriodLength = 1
    By passing @PeriodLength = 1, the procedure will perform all maintenance tasks in a single execution rather than dividing them up across multiple executions.
    To set up recurring maintenance, you need a SQL Agent job that runs p_maint_ExecuteMaintTasks on a regular basis (if using the default settings, daily). No parameters are necessary to run this stored procedure with default settings. Using default settings, the procedure will divide all index maintenance tasks over a 7-day period with a near equal as possible load each day.
    The stored procedure breaks the maintenance into two main steps: scheduling and execution. The scheduling phase runs DBCC SHOWCONTIG WITH FAST to determine which indexes need to be defragmented and approximately how much work this will be. It applies several layers of logic including a fragmentation threshold (default 10% logical fragmentation) and eliminating duplicate defrag operations due to non-clustered/clustered indexes on the same table. For indexes where defragmentation isn’t needed, it schedules an update statistics operation.
    The scheduling phase also estimates the approximate relative cost of each operation and attempts to assign each operation to a specific interval within the maintenance period. By default, the maintenance period is 7 days long, and each period is one day. The scheduling phase runs once per period while the execution phase runs once per interval. (So by default, scheduling occurs weekly while maintenance execution occurs daily.) The results of the scheduling phase are stored in the DBMaint_Task table.

30. Aron Riktor
    February 8, 2010 at 7:15 am | Permalink

    The script is wonderful and I use it for server maintenance everywhere. However, I recently introduced some new indexes in a database. I then found that the index name was too long (or allocated variable too small). Debug output (removed identifiable names):

    Alter Index [_dta_index_CustomerOrderLine_22_608721221__K13_K69_4_34_146_150_152_154_165] On [...].[...].[CustomerOrderLine] Rebuild With (Online = On)
    Updating our index defrag status…
    Picking an index to beat into shape…
    Looking up the specifics for our index…
    Msg 8152, Level 16, State 13, Line 1
    String or binary data would be truncated.

    The cause is a long index name implemented on strength of the Database Engine Tuning Advisor’s advice. I have briefly tried to find the exact assignment that fails, but will probably use a shorter index name anyway.

31. liliek
    February 11, 2010 at 12:41 am | Permalink

    HI Michelle,

    Great script, but how about SQL Server 2000 version,
    do you have script like this or reference for SQL Server 2000??
    please advice.

    thanks
    Liliek

32. Stuart
    March 10, 2010 at 3:07 pm | Permalink

    A question: is it a good idea to defrag clustered index first, before defragging non-clustered indexes (on the same table)?

    I added this modification to pick clustered indexes first…

    /* Grab the most fragmented index first to defrag */
    SELECT TOP 1
    @objectID = objectID
    …
    FROM #indexDefragList
    WHERE defragStatus = 0 — not defragged yet
    — Choose clustered indexes first
    ORDER BY case index_type_desc
    when ‘CLUSTERED INDEX’ then 1 else 0 end desc,
    fragmentation DESC;

33. Julio D
    March 13, 2010 at 9:42 am | Permalink

    Hi,
    I am reading and learning about Index Defrag, and I have a question about what issues should I encounter if I schedule this script on a 24/7 enviroment, will I encounter any locks, how often should I schedule to run. The application is on a system that don’t have any DBA onsite and no one will be checking the db, but it is one that has a lots of inserts and it is used for reporting. Application has been running for 3 years database size 2.2GB . It is used on an application that logs manufacturing production and process information.

    I am trying to create maintenance plans but i want to be careful about affecting the operation but again at the same time it needs a maintenance plan and the fact no one monitor the dbs.

    Thanks

    Julio D

34. Portrman
    March 15, 2010 at 3:13 pm | Permalink

    Noticed a problem(not sure why it would be by design) – If a table has any LOB columns, none of the indexes will be rebuilt. They will only be reorganized.

    For example:
    CREATE TABLE [dbo].[Event](
    [EventKey] [bigint] NOT NULL,
    [MessageID] [varchar](70) NULL,
    CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED
    (
    [EventKey] ASC
    )WITH () ON [PRIMARY]
    ) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [IX_Event_MessageID] ON [dbo].[Event]
    (
    [MessageID] ASC
    )WITH (<taken out) ON [PRIMARY]

    The clustered index on EventKey should be reorganized. The non-clustered on MessageID should be rebuilt online, but will NOT be.

    This is because the script checks the TABLE for any LOBs and not the specific Index about to be worked on.

    I have not figured out exactly how to change this LOB check query

35. Portrman
    March 15, 2010 at 3:50 pm | Permalink

    Oh, not sure if it matters, but on the Event table above, there should also be a column of type XML called …. EventXML

36. gmamata7
    March 20, 2010 at 9:46 pm | Permalink

    Michelle Ufford,

    Sorry to ask you again!! but I want to make sure that “can we use this Index defrag script for SHARE POINT databases”

    thank you

    Mamata

37. gmamata7
    March 21, 2010 at 12:57 am | Permalink

    Michelle Ufford,

    how about defragging indexes that have page_count>1000 . Because I read in several forums that it’s best practice to defrag indexes that have page_count>1000

    defragging indexes that have page_count<1000, will actually have no effect as I understood

    Please advice

38. garyreeds
    March 21, 2010 at 1:48 am | Permalink

    Hi Michelle,

    Using this script will it be possible to index defrag for selected Tables in database

    Instead of all tables in database & instead of just one table in a database?

    I want to index defrag for 10 main large table on Saturday & the remaing small tables on Sunday?

    Can we pass multiple tables name instead of one table?

    Thanks

39. BlueCollarCritic
    March 26, 2010 at 9:20 am | Permalink

    Michelle,

    I’ve looked at this before (many months ago) and I had problems them and gave up but now I’m revisiting this and am again unable to get it to work . I have tested it both on a fully compliant SQL Server 2005 DB and one that is set to a compatability mode of 2000 and while the Onject_ID() calls a failure on the DB with teh compatability mode of 2000, when I replace that with the Table ID the thing runs to completon but does not actual work and I have confirmed that the Table in the DB has fragmentation that meet the minimums liste in the script.

    Are there any requirements perhaps not explicitly listed like you have to be using Enterprise edition or something????

    Thanks

40. Paul White NZ
    April 1, 2010 at 10:53 am | Permalink

    Hey Michelle,

    Do you have any plans to update this to incorporate heap defragmentation using the new SQL Server 2008 syntax ALTER TABLE dbo.Heap REBUILD; …?

    Great script, I never tire of recommending it.

    Paul

41. gary
    April 8, 2010 at 9:23 pm | Permalink

    Can we use this Index defrag stored procedure for Share point databases?

    We have a very large Content database of size 150 GB

    Thanks do much

42. Steve
    April 14, 2010 at 7:24 am | Permalink

    I just wanted to thank you for sharing this.

    -Steve

43. evil lady Dia
    April 16, 2010 at 2:58 pm | Permalink

    Michelle,

    Thank you very much for the script. Just what I needed to get a small DB tuning to work just right.

    Dia

44. Michelle Ufford
    April 22, 2010 at 11:19 am | Permalink

    Hi everyone. Sorry for accidentally spamming you earlier when I approved a bunch of comments.

    I’ve just posted a significant update to my index defrag script. You can find the latest version, along with a list of the changes, here: http://sqlfool.com/2010/04/index-defrag-script-v4-0/

45. pjdiller
    May 18, 2010 at 2:25 pm | Permalink

    Love it. Having trouble with it on SQl 2005 Ent. 64-bit SP3, in SQL_Latin1_General_CP850_BIN2 collation (don’t know if that’s my problem),

    I fixed the case-sensitivity problems as described already, but for whatever reason, it skips the database I’m most interested in defragmenting. I haven’t seen an error message, and there is absolutely work to do in the DB. It’s name is simple, 3 letters, that’s it.

    Any ideas?

46. Donna
    October 20, 2010 at 1:26 pm | Permalink

    Thanks for the script. It is very clever. I did have one database with a hyphen in the name, so I modified line 477 to put brackets around the name.

    From [' + DB_Name(@databaseID) + '].sys.partitions

47. Steven
    November 19, 2010 at 7:07 am | Permalink

    Hi everyone, I am an IT Admin at a small office. I have little to no dba experience and I found this script to help defrag and rebuild index if necessary. Thank you Michelle for sharing such a wonderful script. However, when I paste this script and tried to run it, I get these errors.

    Caution: Changing any part of an object name could break scripts and stored procedures.
    Caution: Changing any part of an object name could break scripts and stored procedures.
    Caution: Changing any part of an object name could break scripts and stored procedures.
    dba_indexDefragLog Table Created
    dba_indexDefragExclusion Table Created
    Msg 2714, Level 16, State 4, Line 43
    There is already an object named ‘PK_indexDefragStatus_v40′ in the database.
    Msg 1750, Level 16, State 0, Line 43
    Could not create constraint. See previous errors.
    Msg 2714, Level 16, State 3, Procedure dba_indexDefrag_sp, Line 834
    There is already an object named ‘dba_indexDefrag_sp’ in the database.

    It always runs the first time creating the tables only, then reports that it has been completed. However, I know there are a lot of fragmentation on my database. Any help is greatly appreciated. I need to get this to run asap for everyone in the office is complaining that it is slow.

48. mike
    September 28, 2011 at 2:39 pm | Permalink

    Many, many thanks. I’m what some would call in involuntary DBA, so jewels like this are worth more than gold!

    My God bless you for your efforts!

49. Brad Griffin
    March 12, 2012 at 4:19 pm | Permalink

    Hi, thanks for this script. Been looking for something like this for ages. Up u ntil now have been running manual routines.

    Again, thanks

    Brad

2 Trackbacks

1. Aaron Bertrand on June 24, 2009 at 4:36 pm
2. Microsoft and DiscountASP.NET news on June 24, 2009 at 7:14 pm
3. Internet Marketing Email » Blog Archive » Index Defrag Script, v3.0 : SQL Fool on June 25, 2009 at 5:35 am
4. SQL Server Index Defrag Script: Update | John Sansom - SQL Server DBA in the UK on June 25, 2009 at 9:46 am
5. Index Defrag Script Update : SQL Fool on July 9, 2009 at 9:45 am
6. 5 tâches courantes d’administration de SQL Server 2005 | Admin Réseau - Blog on July 10, 2009 at 8:36 am
7. links for 2009-10-20 - sashidhar.com on October 20, 2009 at 2:10 am
8. SQL 2008 Express: SQL Agent « My life as a DBA. on November 2, 2009 at 1:49 pm
9. SQL 2008 Express: SQL Agent | Darryl Marshall on November 8, 2009 at 1:46 pm
10. How do -you- install SQL Server? (Part 3) | SQL Server Blog - StraightPath Solutions on December 13, 2009 at 8:36 pm
11. links for 2010-02-03 | Jonathan Gardner on February 3, 2010 at 6:32 pm
12. New Index Defrag Script « Sergessqlnotes's Blog on February 22, 2010 at 2:06 pm
13. rusanu.com » Dealing with Large Queues on March 9, 2010 at 1:11 am
14. Free T-SQL Database Maintenance Scripts | SQL Aloha on October 22, 2010 at 6:31 pm
15. The Kinetic Serendipity of the Written Word « NULLgarity on November 14, 2010 at 8:02 pm

Leave a Reply Cancel reply