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
-- ============================================================================================