SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rebuild and Reorganize Indexes in SQL 2005


Rebuild and Reorganize Indexes in SQL 2005

Author
Message
El RoboCopo
El RoboCopo
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 451
Comments posted to this topic are about the item Rebuild and Reorganize Indexes in SQL 2005


Kindest Regards,

Lennart Gerdvall
payex.com
Ken Davis
Ken Davis
SSC Eights!
SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)

Group: General Forum Members
Points: 937 Visits: 491
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
-- ============================================================================================
Ken Davis
Ken Davis
SSC Eights!
SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)

Group: General Forum Members
Points: 937 Visits: 491
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.
Ken Davis
Ken Davis
SSC Eights!
SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)

Group: General Forum Members
Points: 937 Visits: 491
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!
d.raes
d.raes
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 147
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?
Anthony Yates
Anthony Yates
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 51
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search