Rebuild and Reorganize Indexes in SQL 2005

  • Comments posted to this topic are about the item Rebuild and Reorganize Indexes in SQL 2005


    Kindest Regards,

    Lennart Gerdvall
    payex.com

  • Hi Lennart,

    I am trying to use your script. The SPs create just fine but when I try to execute spX_RebuildIndexes_Main I am getting invalid column errors. I thought I would ask you if you have seen this before, before I spend too much time debugging. Thanks a lot. It looks like a great process.

    My exec command:

    EXEC master.dbo.spX_RebuildIndexes_Main

    @databasename ='Auth',

    @maxfrag = 10.0,

    @maxdensity = 75.0,

    @online = 1,

    @runrebuild = 1,

    @LogUsedThresholdGB = 6,

    @maxruntime = 3600,

    @disklimit = '0.19',

    @notdisk1 = 'C',

    @notdisk2 = '',

    @notdisk3 = '',

    @notdisk4 = '',

    @maxdop = 0

    The output:

    /*

    Current SQL Edition is Enterprise Edition, Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)

    Feb 9 2007 22:47:07

    Copyright (c) 1988-2005 Microsoft Corporation

    Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    */

    -- START OF INDEX DEFRAG FOR SERVER GA016VSQL04D\INST1 AT 2008-12-10 10:50:34

    -- ==========================================================================================

    -- MAX processing time allowed for all DBs is 3600 secs.

    -- Estimated time ready is no later than 2008-12-10 11:50:34.

    -- MAX disk space limit set for transaction logg on processed DB is 6 GB!

    -- MAXDOP used is 0

    -- START OF INDEX DEFRAG FOR DATABASE Auth AT 2008-12-10 10:50:34

    -- ============================================================================================

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 158

    Invalid column name 'TableID'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 158

    Invalid column name 'IndexType'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 158

    Invalid column name 'PartitionNumber'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 158

    Invalid column name 'CurrentDensity'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 158

    Invalid column name 'CurrentFragmentation'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 212

    Invalid column name 'TableName'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 212

    Invalid column name 'TableName'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 213

    Invalid column name 'IndexType'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 214

    Invalid column name 'IndexType'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 199

    Invalid column name 'TableID'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 200

    Invalid column name 'IndexType'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 200

    Invalid column name 'IndexName'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 201

    Invalid column name 'TableName'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 202

    Invalid column name 'SchemaName'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 203

    Invalid column name 'IndexType'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 204

    Invalid column name 'PartitionNumber'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 205

    Invalid column name 'PartitionCount'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 206

    Invalid column name 'CurrentDensity'.

    Msg 207, Level 16, State 1, Procedure spX_RebuildIndexes, Line 207

    Invalid column name 'CurrentFragmentation'.

    -- END OF INDEX DEFRAG FOR DATABASE Auth AT 2008-12-10 10:50:34

    -- ============================================================================================

    -- Processing time for database Auth was 0 seconds.

    -- Total passed processing time is 0 seconds.

    -- Total estimated remaining max processing time is 3600 seconds.

    -- Return code = 0

    -- Returned execution status for master.dbo.spX_RebuildIndexes after processing Auth on SQL Server GA016VSQL04D\INST1 is Index rebuild OK!

    -- Total duration of index defrag for server GA016VSQL04D\INST1 was 0 seconds.

    -- END OF INDEX DEFRAG FOR SERVER GA016VSQL04D\INST1 AT 2008-12-10 10:50:34

    -- ============================================================================================

  • Lennart, you can ignore the question I posted earlier today. Apparently I caused those errors. My DBs are mirrored and must stay in Full recovery model. So I attempted to comment out the part where you change the recovery model and apparently that caused the errors.

  • I have been using your script and it works great but I found one "opportunity". I found that the script would always execute a REORGANIZE and never a REBUILD. In the "IF @currentfrag >= 30" section, I replaced @myreorganizeoption with @myrebuildoption. That corrected the problem. Now the script will do a REBUILD if the other conditions are right for that.

    thanks for the great script!

  • HI,

    script looks greate but when i exec the script i get the the following:

    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.spX_SendDBMailOperator'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.spX_SendDBMailOperator'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.spX_RebuildIndexes'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.spX_SendDBMailOperator'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.spX_SendDBMailOperator'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.spX_SendDBMailOperator'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.spX_SendDBMailOperator'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.spX_SendDBMailOperator'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.spX_SendDBMailOperator'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.spX_check_activeconnections'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.spX_SendDBMailOperator'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.spX_SendDBMailOperator'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.spX_SendDBMailOperator'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

    What am i missing?

  • It's just because the order in which the script creates the four stored procedures.

    If you change it so that is creates them in the following order it will not produce those messages: -

    spX_check_activeconnections

    spX_SendDBMailOperator

    spX_RebuildIndexes

    spX_RebuildIndexes_Main

    Also add the change suggested by Ken Davis above so that rebuild works as intended.

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

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