Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rebuild and Reorganize Indexes in SQL 2005 Expand / Collapse
Author
Message
Posted Saturday, October 13, 2007 3:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 19, 2011 7:48 AM
Points: 22, Visits: 451
Comments posted to this topic are about the item Rebuild and Reorganize Indexes in SQL 2005


Kindest Regards,

Lennart Gerdvall
payex.com
Post #410395
Posted Wednesday, December 10, 2008 8:55 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:25 PM
Points: 139, Visits: 384
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
-- ============================================================================================
Post #617120
Posted Wednesday, December 10, 2008 12:17 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:25 PM
Points: 139, Visits: 384
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.
Post #617309
Posted Wednesday, March 25, 2009 11:26 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:25 PM
Points: 139, Visits: 384
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!
Post #683509
Posted Wednesday, July 14, 2010 7:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 11:11 PM
Points: 7, Visits: 94
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?
Post #952311
Posted Thursday, June 9, 2011 3:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 13, 2012 10:30 AM
Points: 1, 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.
Post #1122317
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse