November 2, 2006 at 1:29 pm
We have a very large table (Over 40 million records) with a clustered and non-clustered index.
When I try to run the DBCC Index defrag utility against this table, it times out.
Is their an option I need to set to get around this problem?
Thanks for any help.
Rob
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
November 2, 2006 at 2:21 pm
Could you change the default timeout value?
November 2, 2006 at 3:36 pm
Please provide details.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
November 3, 2006 at 4:47 am
Hi,
Not sure this will help... Are you calling the command from SQL Query Analyzer? If so there is an option to set the Query timeout, is this set to 0 on your machine? If not then set that.
Hope this helps!
Peter Gadsby
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 3, 2006 at 8:08 am
An index defrag is an online opperation so I'm curious as to where the timeout is comming from. How long does it run before you get a timeout? An index defrag should also be giving you progress messages as to the percent of work it has completed. How much of the table gets defrag'd before it times out? What is the exact message you are getting?
November 3, 2006 at 8:50 am
HI,
A very simple test... I created a temporary table started a transaction inserted some data and on another session tried the DBCC INDEXDEFRAG and I got a lock ....
Session 1
BEGIN TRANSACTION
INSERT into WIBBLE values('a')
select * from wibble (tablock)
Session 2
dbcc indexdefrag(scratch,WIBBLE,IX_A)
The DBCC INDEXDEFRAG was waiting for me to rollback or commit a transaction. So to make sure you haven't got a lock run sp_who2 whilst the process is running
Regards
Peter Gadsby
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 3, 2006 at 10:22 am
Right, DBCC INDEXDEFRAG must lock portions of the table in order to defrag the index. If you use a tablock hint, you are locking the entire table, thereby preventing DBCC INDEXDEFRAG from being able to lock portions of the index to start it's defragging. The original poster spoke of the INDEXDEFRAG operation timing out, did your test give you a timeout error? The original poster also noted that his table has 40 Million rows, which tells me that DBCC INDEXDEFRAG should be able to be working on the table online. If he has a process that is getting a table lock on a 40 Million row table, he's got problems!
November 3, 2006 at 11:36 am
The query timeout was set to 600 seconds. I set it to 0 and reran the defrag. It has been running for about an hour and is 98% done. Problem solved.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
November 3, 2006 at 2:37 pm
I spoke too soon. The entire process took 4 hours.
We really need to look at archiving. At least it didn't
time out.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
November 14, 2006 at 4:37 am
Hi John,
My test did give a timeout error, what I was trying to say by showing this was that another process may have been table locking and that was causing his DBCC index defrag to take ages...
Regards
Peter Gadsby
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 14, 2006 at 8:49 am
The database I used did not have any conflicts. However, it is a bit underpowered and 40 million rows is huge.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply