Automated index maintenance for all dbs on a serve

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Automated index maintenance for all dbs on a serve

Author

Topic

dcunningham
Starting Member

25 Posts

Posted - 2008-10-16 : 15:12:21

Hello all. I've built a system to help me manage all indexes on a sql 2005 server. All the code is written and tested - works great except for one piece I could use some help. Running this stored proc from a job local to the servers, from inside a user admin database call it DBAadmin. the DBCC index commands to be run can only see indexes within the db the job is running in. I need the sp to be able to execute a dbcc command in all other databases within this job. Any help is appreciated.

-- SET THESE SWITCHES FOR WHICH MAINT TO PERFORM. REINDEXING ALWAYS OVERRIDES DEFRAGMENTING

IF @NextReIndex <= GETDATE()

BEGIN

PRINT 'THIS INDEX HAS DATE QUALIFIED TO BE RE-INDEXED'

SET @ReIndex='USE ['+ @DatabaseName +'] ; DBCC DBREINDEX( '+@TableName+', '+@IndexName+', 95 )'

-- SELECT @REINDEX AS [REINDEX SCRIPT]

SET @REINDEXACTION = 1
PRINT 'EligibleForReIndex: ' + CAST( @EligibleForReIndex AS NVARCHAR )

PRINT 'REINDEX ACTION VARIABLE IS: ' + CAST( @REINDEXACTION AS NVARCHAR )

PRINT 'Eligible For DEFRAG: ' + CAST( @EligibleForDEFRAG AS NVARCHAR )

PRINT 'DEFRAG ACTION VARIABLE IS: ' + CAST( @DEFRAGACTION AS NVARCHAR )

END

ELSE

BEGIN

IF @NextDeFrag <= GETDATE()

BEGIN

PRINT 'THIS INDEX HAS DATE QUALIFIED TO BE DEFRAGMENTED'

SET @ReIndex = 'USE '+ @DatabaseName +' DBCC INDEXDEFRAG( ' + @DatabaseName + ' , ' + @TableName + ' , ' + @IndexName + ')'

-- DOESN'T WORK

-- SET @ReIndex = 'USE ['+ @DatabaseName +'] ; DBCC INDEXDEFRAG( ''' + @DatabaseName + ''',''' + @TableName + ''',''' + @IndexName + ''')'

-- SELECT @REINDEX AS [REINDEX SCRIPT]

SET @DEFRAGACTION = 1
PRINT 'EligibleForReIndex: ' + CAST( @EligibleForReIndex AS NVARCHAR )

PRINT 'REINDEX ACTION VARIABLE IS: ' + CAST( @REINDEXACTION AS NVARCHAR )

PRINT 'Eligible For DEFRAG: ' + CAST( @EligibleForDEFRAG AS NVARCHAR )

PRINT 'DEFRAG ACTION VARIABLE IS: ' + CAST( @DEFRAGACTION AS NVARCHAR )

END
END

-------------------------------------------------------------------------------------------

-------- END DETERMINE FREQUENCY - EVALUATE -------------------------------------------

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

-------- DETERMINE ELIGIBILITY -------------------------------------------------------

-------------------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------------------

-------- PROCESS REINDEX CANDIDATES ---------------------------------------------------

-------------------------------------------------------------------------------------------

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

IF @EligibleForReIndex > 0 AND @REINDEXACTION > 0

BEGIN

PRINT 'THIS INDEX IS ELIGIBLE FOR REDEXING'

SET @MAINTSTARTTIME = GETDATE()

PRINT 'TABLE NAME: '+ @TableName +' TABLE OBJECT ID: '+ CAST( @INDEXOBJID AS NVARCHAR)+' INDEX ID: '+ CAST( @INDEXID AS NVARCHAR )

EXEC SP_EXECUTESQL @REINDEX
SET @MAINTSTOPTIME = GETDATE()

PRINT 'UPDATING THE MAINTENANCE TABLE FOR THIS INDEX. IT HAS BEEN REINDEXED'

UPDATE dbo.DBADIndexMaintenance
SET

NextReIndexDate = DATEADD( MONTH , 1 , @MAINTSTARTTIME ),

NextDeFragDate = DATEADD( WEEK , 1 , @MAINTSTARTTIME ),

FragValueAfterMaint = @AVGFRAG,

LastMaintenanceDate = GETDATE() ,

MaintRunTime = DATEDIFF(MINUTE , @MAINTSTARTTIME, @MAINTSTOPTIME )

WHERE DBADINDEXMAINTID_PK = @INDEXMAINTIDPK

END -- @EligibleForReIndex > 0 AND @REINDEXACTION > 0

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-10-16 : 16:19:51

see if this works :
http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx

i know it works if used with master db.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-16 : 16:32:37

Use Tara's script instead for Reindexing or Reorganizing .

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-10-16 : 16:37:46

lol that's a good idea too

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

dcunningham
Starting Member

25 Posts

Posted - 2008-10-16 : 18:26:36

Thanks Guys. Using the 'Alter Index' command did the trick. Now my solution is working. - Version 2 may take into account the other items Tara has listed in here blog. Thanks for the input.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources