Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

DBCC DBREINDEX

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 ]

Feedback

Gravatar

# DBCC INDEXDEFRAG stored procedure

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

# 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