Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

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?
0
dbaSQL
Asked:
dbaSQL
  • 7
  • 2
  • 2
  • +1
3 Solutions
 
MrMintanetCommented:
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.
0
 
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..... :-)
0
 
brandonvmooreCommented:
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.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
0
 
MrMintanetCommented:
There will always be a need to defrag.  What are you talking about brandon?
0
 
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.
0
 
brandonvmooreCommented:
Mr Mintanet: Yes, I should have said that it greatly reduces the need for defragging.
0
 
chapmandewCommented:
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)
0
 
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.

0
 
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?
0
 
chapmandewCommented:
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.
0
 
dbaSQLAuthor Commented:
yeah... haven't actually done this precise move before, but i am inclined to agree, chap.  ouch
0
 
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
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 7
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now