Just wondering if anybody's ever done this... DBREINDEX x 2

v2000, four tables, one is freeking huge.  the other three or similarly huge, but not as bad as the other.  Say I've got 2B records in one, and a bit less than that in the others.
(B = billion)

heavily fragmented, i need to defrag. the last time i did this, it took 46hrs.  completed successfully.. just rather timely.  surely it's going to be greater this time around, as the data is never purged.  

i'm starting to wonder... what if i split it up, two different jobs:  two tables in one, two tables in the other, and ran them in parallel?  fully dedicated machine, completely dedicated to me and only me, throughout the Easter weekend.  

What do you think?  Might that be too harsh to the machine?  It should be ok... don't you think?
ample, ample disk space on all drives... simple recovery model... so i'm not too worried about chewing tons of disk.  But.... maybe I should be more worried.

I don't know.

Anybody ever tried anything like this?
LVL 17
Who is Participating?

Improve company productivity with a Business Account.Sign Up

MrMintanetConnect With a Mentor Commented:
This sounds brilliant.  I did something similar, and to be honest, I think I had more data than this.  "Harsh"?  I think it will be fine.  You said it, "Ample disk space".  That and the recovery model is really all you need.  Anything else is just routine, IMHO.
dbaSQLAuthor Commented:
well, i hope so.... i think i'm going to give it a go.  i will let you know.  
in a day or two..... :-)
brandonvmooreConnect With a Mentor Commented:
You might consider doing some research on how to optimize your storage system and database in order to elimate the need for defragmentation.  I'm guessing you don't have to do it often, but if you do then this would be the way to go.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

dbaSQLAuthor Commented:
oh yes... i agree.  in sql agent, my job description:

    Hopefully only once more before upgrade.... dbcc dbreindex.

my new model in v2008 is a partitioned structure.  i fully intend to take advantage of he ability to parition selectively, by partition
There will always be a need to defrag.  What are you talking about brandon?
dbaSQLAuthor Commented:
agreed... there will be.  slight type-o in my previous post:

 i fully intend to take advantage of he ability to MAINTAIN THE paritions selectively, by partition

that maintenance may be defrag, backups, who knows... but, it's going to help me optimize my storage system such that it is more manageable, and doesn't require such ridiculous runtimes for mantenance of this nature.
Mr Mintanet: Yes, I should have said that it greatly reduces the need for defragging.
chapmandewConnect With a Mentor Commented:
partitioning is the way to go, when you can.

becareful with dbreindex....it rebuilds the reindex rather than the reorg (defrag), which means it is going to take a lock on the table(s)
dbaSQLAuthor Commented:
yep.... that's why i chose tihs weekend to do it.  Good Friday, Easter Sunday... nobody's trading.  That db is mine.  Two of my four targeted tables are complete... two are still running.  And I expect they will be yet for some time.

dbaSQLAuthor Commented:
Msg 1204, Sev 19: The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration. [SQLSTATE HY000]

>>>what if i split it up, two different jobs:  two tables in one, two tables in the other, and ran them in parallel?  

as i said, that box is completely dedicated to me during this effort.  i have to assume that splitting it up into two jobs was a problem.  and one just crapped on the other.  i don't know what else it could have been.

regardless..... dbcc dbreindex (tablename, '',90)
ran for X number of hours, and got interrupted.  

wonder, if i run it again now, is it resuming the efforts from before... or starting over completely from the ground up?  i mean, since it's a rebuild and not a reorg, isn't it an all or nothing?
Hmmm...I think the reorg would start over where it left off, but not the reindex.  If you restart, its likely going to start at the beginning...otherwise, it might leave the db in an inconsistent state.
dbaSQLAuthor Commented:
yeah... haven't actually done this precise move before, but i am inclined to agree, chap.  ouch
dbaSQLAuthor Commented:
splitting it into two jobs, to be run in parallel, tripped me.  (they ran into eachother)
in the future, we'll just do it sequentially.   (till i upgrade to the partitioned version and can handle things more selectively, that is)
thank you each for your input
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.