Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aladdin27
Starting Member
2 Posts |
 Posted - 2006-04-12 : 14:12:05
|
| I just executed a query that rebuilds a table with the dbcc dbreindex command. This takes way to much time to complete. The table has alle the reservations for 7 hotels, so this is about 700.000 records. I want my users to be able to get in the system as soon as possible but can I interrupt this query or does this mean I'll have to start over?Please advise |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-04-12 : 14:47:10
|
| dbcc indexdefrag is an "online" operation. unfortunately dbcc dbreindex is not.you can abort the query, but it means that you will have to start over.if possible do dbcc indexdefrag during a maintenance window when users don't access the system.for 700.000 rows it should be < 5 minutes on a decent system though.rockmoose |
 |
|
|
aladdin27
Starting Member
2 Posts |
Posted - 2006-04-12 : 15:06:56
|
| 5 minutes will be better then 2 hours I guess so thanks rockmoose I'll dig up some more information about this and consider this option. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-04-12 : 15:13:14
|
| I meant that dbcc dbreindex should complete pretty quick as well for 700.000 records.2 hours seem much too long.Run "sp_who2" to see if some your dbcc dbreindex command is blocked by another process.* too late now though I guess...rockmoose |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-13 : 02:25:41
|
| Do folk use DBREINDEX in preference to INDEXDEFRAG?Apart from very small tables I see no point at all - but I'm happy for someone to tell me otherwise!Kristen |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-04-13 : 14:59:18
|
| I've been doing a defrag each night, and reindexing on the weekend. My understanding was that the defrag wasn't as thorough as the reindex and that I still needed an occasional reindex. But if my understanding is incorrect, I'd love to know that it is safe to get rid of that piece of it and just stick solely to the defragging. My defrag job goes through all indexes that our out of the bounds I've set and completes really quickly and with little growth in my log file. While the reindex job takes over 3 hours to complete and causes huge log file growth. |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-04-13 : 16:52:08
|
| I just happened to come across this article on the subject by accident so I thought I'd share it here:http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx |
|
|
|
|
|
|
dbcc dbreindex