October 13, 2007 at 3:14 pm
Comments posted to this topic are about the item Rebuild and Reorganize Indexes in SQL 2005
Lennart Gerdvall
payex.com
December 10, 2008 at 8:55 am
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
-- ============================================================================================
December 10, 2008 at 12:17 pm
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.
March 25, 2009 at 11:26 am
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!
July 14, 2010 at 7:18 am
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?
June 9, 2011 at 3:40 am
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply