?
Solved

DBCC DBREINDEX 20 Hours and counting....HELP!!

Posted on 2008-06-18
9
Medium Priority
?
1,193 Views
Last Modified: 2013-12-01
I'm running SQL Server 2000 Enterprise Edition.  HP Proliant , Quad 3.6Mhz, 8GB RAM, RAID 5, 4 146GB 15k rotational speed drives.  The RAID controller died a week ago and I replaced it and everything came up without issue.  I could issue a query and get results.

Since the server has been back up, an insert into a 105 million row table of 1,000 records is taking 15 minutes.  It used to take 10 seconds.  I have detailed process logs that track the duration and in three years it's never taken more than 20 seconds.  Also, the weekly Backup Exec SQL "pre-process" never completed and I cancelled it at 35 hours.  It used to take 30 minutes.  Backup Exec is able to backup small (500MB) databases from the same server without issue.

I thought of corruption so I ran DBCC CHECKTABLE ('inv_detail', repair_fast) and it ran in 23 minutes and found no errors.  Since a reindex had not been done in a few months I started a DBCC DBREINDEX.  I wrongly assumed I would get status as it completed each index as the Book OnLine shows.  It does not.

I'm 20 hours into the reindex and if I'm only through the first index I need to stop and use another approach.  If I kill the process how long will the kill process take?  Will I leave thousands of pages marked as allocated wrongly?  How can I figure out what indecies are complete?  How can I stop and keep the indecies that are complete?

I am 3 days late on monthly invoicing already.  Not good.

THANKS !!
0
Comment
Question by:hammermedia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 21817236
For one table, it's all going to be one transaction, I'm pretty sure, so if you KILL it the entire thing would have to roll back, which would likely take much longer than 20 hrs (i.e., than the time it took to do the rebuild itself).

Just to be sure, did you UPDATE STATISTICS on all the tables?  I would always try that first after any  kind of upgrade / service pack / media recovery, etc..
0
 

Author Comment

by:hammermedia
ID: 21817265
I didn't do UPDATE STATS because I understood that DBREINDEX updated the stats.  I thought I would be saving time.  Oops.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 21817345
True; actually you are right, DBREINDEX will indeed update the stats.

You must have some other issue on the box for it to be that slow.  It might have something to do with disk I/O -- given the recent controller problems -- but you should check the SQL error logs for any kind of messages.

Also, run a profiler and/or performance monitor -- from a *different machine*, you don't want to add load to the already afflicted one -- and see if anything stands out: disk I/O queue, CPU usage, etc..

Just in case, although not likely at all, also make sure SQL does not a low "max memory" setting.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:hammermedia
ID: 21817443
Max memory is at 6GB.  The disk queue length has been averaging 76 for hours.  Normally it's closer to zero.  CPU is at 3%.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 21817720
Hmm, those numbers are all fine.

Did you check for SQL errors?  Also check for hardware errors, including all database drives used for SQL.
0
 

Author Comment

by:hammermedia
ID: 21818121
No hardware errors.  SQL Application messages that it grew the MDF by 5GB.  Drives are fine.  No errors in the HP management console either.
0
 

Author Comment

by:hammermedia
ID: 21821719
37 hours and still going.  Is there ANYWAY to get some status?  Never in a million years would I think a reindex would take sooo long after a CHECKDB completed in 23 minutes.
0
 

Author Comment

by:hammermedia
ID: 21827488
46 hours and it completed.  Just started running test to see if it actually fixed the original issue.  I'm starting that noe.
0
 

Accepted Solution

by:
hammermedia earned 0 total points
ID: 22227808
So the final result was reindex.  Once The reindex completed it was MUCH quicker doing the indexes again.  Now the ENTIRE index process takes 53 MINUTES.  Not HOURS !!!

I can't recommend highly enough to reindex once a week.  I have another 250 Million row table and it takes 5 hours to completely reindex it.

Reindexing is on the weekly schedule.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question