Another DBCC DBREINDEX Question

I am interested in finding out what would happen if I were to call a stored procedure that reindexes a specific table (SQL Server 2000) from .NET code. Specifically, is DBCC DBREINDEX a blocking call (my current assumption is that it in fact is)? I am calling this procedure from a secondary thread, while my primary thread executes other tasks. I think this is a feasible solution, but I just wanted to make sure if my assumption is correct.

Also, I am using a trusted connection, but I am not sure how to treat the SqlConnection object once I call the SPROC; I won't be able to close the connection until the SPROC returns - what would the immediate consequence of keeping a connection open for a long time be (if DBCC DBREINDEX is a blocking call, then my connection will stay open until the SPROC returns). (if DBCC DBREINDEX is a blocking call, then my connection will stay open until the SPROC returns)?

Thanks in advance!

Edit: I forgot to mention, but this whole task is being executed as a scheduled task from the command line - so I also have the option of using osql, but again am not sure about the consequences of calling DBCC DBREINDEX from osql.

i am not sure i understand the requirement... nevertheless this hints may help u..... DBCC DBREINDEX is a offline command... ie. the table will not be available for any pupose while re-index is going on. If the another thread you mentioned is accessing same table .. then it will be blocked. if it is accessing another table then there is no issue

Madhu

|||

Madhu K Nair wrote:

i am not sure i understand the requirement... nevertheless this hints may help u..... DBCC DBREINDEX is a offline command... ie. the table will not be available for any pupose while re-index is going on. If the another thread you mentioned is accessing same table .. then it will be blocked. if it is accessing another table then there is no issue

Madhu

Maybe I confused you...

The SPROC that I am calling will call DBCC REINDEX: .NET Code > SqlConnection > DBCC REINDEX

What I am asking is what will happen if I call a the stored procedure that contains the T-SQL to reindex the table; will my .NET thread block until this SPROC returns (which could theoretically take hours)? Here is a simple layout of my T-SQL SPROC:

CREATE PROC ReindexArchive

AS

BEGIN

DBCC DBREINDEX('TableName', '', 70)

WITH NO_INFOMSGS

END

GO

So what I am trying to ask is if this sproc gets called from .NET code, will that call be blocked until the table is reindexed and the SPROC returns control to the calling .NET code. Thanks.

|||

i think you need to create multi threads here. otherwise , you will have to wait till the REINDEX Procedure completes to go to next step

Madhu