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?
(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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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?
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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?
>>>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.
ASKER
yeah... haven't actually done this precise move before, but i am inclined to agree, chap. ouch
ASKER
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
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
ASKER
in a day or two..... :-)