SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 DBCC DBREINDEX Question
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

1384
Starting Member

9 Posts

Posted - 09/15/2006 :  10:24:23  Show Profile
I understand that this is an offline process. However, what happens when this is executed without database exclusivity? Does it simply pass over tables that are in use? The reason I ask is that my company has a nightly cleanup process that runs DBREINDEX on fragmented tables. At times I have noticed that processes have been run concurrent with this. Moving forward I will make sure the system is not being used while DBREINDEX is running. However, I wanted to get an understanding of the impact of running this in that manner historically. Any insight would be appreciated. Thanks.

Luis Martin
Yak Posting Veteran

Argentina
54 Posts

Posted - 09/15/2006 :  10:28:59  Show Profile
If you are running DBREINDEX, suppose with table "X1", and other user, job, etc., try to work with the same table you will have a lock there.
Go to Top of Page

1384
Starting Member

9 Posts

Posted - 09/15/2006 :  11:26:19  Show Profile
Thanks Luis. For a related question, now say you are running an automated cleanup routine. This script runs DBREINDEX on table 'A', completes that reindex, and attempts to move on to table 'B'. However, table 'B' is currently being used by another process. Given this scenario, does DBREINDEX abort, disregard B1 (because it is in use), and move on to reindex table 'C'? Does it compete? What happens here.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/15/2006 :  12:57:15  Show Profile
If you are worried about it you might like to look at DBCC INDEXDEFRAG instead.

We only run it on tables which are "sufficiently" fragmented to save time, and log space.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Rebuilding%20Indexes,DBCC%20CHECDB,Reindex

Also have a look at Tara's blog which has describes some very useful maintenance tools

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36661 Posts

Posted - 09/15/2006 :  13:21:52  Show Profile  Visit tkizer's Homepage
You need to read the MS article about index fragmentation before deciding whether you should run DBCC DBREINDEX or DBCC INDEXDEFRAG.

Here's my blog on that:
http://weblogs.sqlteam.com/tarad/archive/2006/04/11/9585.aspx

And here's my blog for the maintenance routines Kristen mentions:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Tara Kizer
Go to Top of Page

1384
Starting Member

9 Posts

Posted - 09/15/2006 :  13:35:34  Show Profile
Lots of excellent advice! Thank you!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000