Ramblings of a DBA

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

My Links



Search this Blog


Post Categories



There are several things that a DBA sets up in a production environment, one of these things is rebuilding the indexes.  This is done with DBCC DBREINDEX.  Some systems can not afford the locks that are held for long periods of time with DBCC DBREINDEX so they use DBCC INDEXDEFRAG instead.  DBCC DBREINDEX still needs to be run occassionally even on these types of systems. 

For systems that have lots of tables, it can be tiresome to write out the DBCC DBREINDEX command for each of the tables.  To make it easier, you can run this to write out the statements for you:

SELECT 'DBCC DBREINDEX([' + u.name + '.' + o.name + '], '''', 80)'
FROM sysobjects o
INNER JOIN sysusers u ON o.uid = u.uid
WHERE o.name <> 'dtproperties' AND o.xtype = 'u'
ORDER BY o.name

The above query accounts for tables that aren't owned by dbo and sets the fillfactor to 80. 

Print | posted on Monday, December 15, 2003 11:40 AM | Filed Under [ SQL Server - Database Administration ]



# DBCC INDEXDEFRAG stored procedure

6/22/2004 7:56 PM | Ramblings of a DBA

# DBCC INDEXDEFRAG stored procedure

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

Powered by:
Powered By Subtext Powered By ASP.NET