Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

DBCC DBREINDEX stored procedure

EDIT: This stored procedure has been updated.

The below stored procedure runs DBCC DBREINDEX for each of the indexes in the user database that is inputted into the sproc.  I recommend putting it into an Admin database (hey just name it Admin!) rather than in master.  Then just schedule isp_DBCC_DBREINDEX for each of the databases you want defragged.  Only run this routine when there is very little activity on your system as DBCC DBREINDEX will block queries from running. 

 

CREATE    PROC isp_DBCC_DBREINDEX
(@dbName SYSNAME)
AS
SET NOCOUNT ON

DECLARE @objID INT
DECLARE @objName SYSNAME
DECLARE @idxName SYSNAME
DECLARE @SQL NVARCHAR(4000)

SET @objID = 0

SET @SQL = ''
SET @SQL = @SQL + 'SELECT i.id, i.name, o.name AS ObjectName '
SET @SQL = @SQL + 'INTO ##Indexes '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i '
SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o '
SET @SQL = @SQL + 'ON i.id = o.id '
SET @SQL = @SQL + 'WHERE indid > 0 AND indid < 255 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND '
SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0'

EXEC sp_executesql @statement = @SQL

WHILE @objID < (SELECT MAX(id) FROM ##Indexes)
BEGIN

 SELECT TOP 1 @objID = id, @idxName = name, @objName = ObjectName
 FROM ##Indexes
 WHERE id > @objID
 ORDER BY id

 SET @SQL = 'DBCC DBREINDEX(''' + @dbName + '.dbo.' + @objName + ''', ' + @idxName + ', 0) WITH NO_INFOMSGS'

 EXEC sp_executesql @statement = @SQL

END

DROP TABLE ##Indexes

RETURN 0

 

 

GO

Print | posted on Friday, July 02, 2004 9:15 AM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# re: DBCC DBREINDEX stored procedure

Please be aware that the table will be Offline when DBCC REINDEX is working on it.
7/2/2004 9:46 AM | Satish Balusa
Gravatar

# re: DBCC DBREINDEX stored procedure

Yes that's why I mentioned that it should be run when there is very little activity on the system.
7/2/2004 9:57 AM | Tara
Gravatar

# DBCC DBREINDEX Stored Procedure - new version

7/30/2004 6:08 PM | Ramblings of a DBA
Gravatar

# DBCC DBREINDEX Stored Procedure - new version

7/30/2004 6:10 PM | Ramblings of a DBA
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET