Database reorganization

Database reorganization
We use SQL 2000 and our application database is around 2 Gb.
A third party consultant (Microsoft Certified) counseled us to perform
database reorganization, at least once each year.
The way he recommend us to execute the database reorganization is the
following:
1) backup the app database
2) delete the app database
3) restore the app database with another name oldDB
4) create a new app database
5) export all objects and data from oldDB to app DB by DTS
The questions I would like to ask you are:
Is that really necessary?
Is that really effective?
Is there another way to perform database reorganization?
Thanks,
MarcosWhy? Looks very strange.
You should have proper DataBase Disaster Recovery Strategy , means backup
all user databases as well as system databases and many many other things
<mripplinger74@.yahoo.com.br> wrote in message
news:1152100999.019653.291350@.a14g2000cwb.googlegroups.com...
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>|||<mripplinger74@.yahoo.com.br> wrote in message
news:1152100999.019653.291350@.a14g2000cwb.googlegroups.com...
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>
Not sure why you would go through all that. Personally I would never delete
my database to restore it back again. What happens if your restore does not
work? If you really wanted to do that I would do 2 and 3 the other way round
that way you have not lost anything if it does not go to plan.
My database is 288GB and I run a reorg job on it a few times a year
(although I am planning to run a regular job), by using one of the
maintenance plans. I would suggest you take a look at these before you start
deleting databases. Having said all that though unless your database is
growing rapidly you will probably see no noticable improvement, mine is
growing at a rate of 1.5 to 2GB a week. Incidentally when I started looking
after this database it was 150GB and it had never had a reorg job run on it,
performance was fine, but when I first ran it there was a very noticable
difference. I'm no SQL guru but this way works for me and it lets me sleep
easy, when we do a restore I''m usually at work all night.
Gav|||mripplinger74@.yahoo.com.br wrote:
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>
Gosh, I'm also Microsoft certified, and I'm going to tell you that
consultant is all wet. Guess you have to wonder how useful those
certifications are, huh?
A 2GB database is NOTHING - I've been working with 100+GB database for
several years. There is no need to do any "reorganization". Keep your
indexes defragged, keep the database files properly sized, don't shrink
your database or log files, and be vigilant about making sure the
queries hitting your database are written properly and efficiently.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> mripplinger74@.yahoo.com.br wrote:
> Gosh, I'm also Microsoft certified, and I'm going to tell you that
> consultant is all wet. Guess you have to wonder how useful those
> certifications are, huh?
> A 2GB database is NOTHING - I've been working with 100+GB database for
> several years. There is no need to do any "reorganization". Keep
> your indexes defragged, keep the database files properly sized, don't
> shrink your database or log files, and be vigilant about making sure
> the queries hitting your database are written properly and efficiently.
>
..well...nobody said that this consultant was certified on anything
related to SQL server... ;-)
Regards
Steen Schlter Persson (...who isn't certified in anything but just
knows something about SQL server...)
Databaseadministrator / Systemadministrator|||Uri.
We have a Disaster Recovery Plan but my question here is how to
reorganize database trying to improve his performance.
Thanks,
Marcos
Uri Dimant escreveu:

> Why? Looks very strange.
> You should have proper DataBase Disaster Recovery Strategy , means backup
> all user databases as well as system databases and many many other things
>|||Gav,
Just to be sure, when you say "reorg job" you are talking about use
a maintenance plan with the option reorganize data and index page
selected, right?
Do you believe it is enough? Even in the tables where the PK is not a
clustered index?
Thanks,
Marcos
Gav escreveu:
> Not sure why you would go through all that. Personally I would never delet
e
> my database to restore it back again. What happens if your restore does no
t
> work? If you really wanted to do that I would do 2 and 3 the other way rou
nd
> that way you have not lost anything if it does not go to plan.
> My database is 288GB and I run a reorg job on it a few times a year
> (although I am planning to run a regular job), by using one of the
> maintenance plans. I would suggest you take a look at these before you sta
rt
> deleting databases. Having said all that though unless your database is
> growing rapidly you will probably see no noticable improvement, mine is
> growing at a rate of 1.5 to 2GB a week. Incidentally when I started lookin
g
> after this database it was 150GB and it had never had a reorg job run on i
t,
> performance was fine, but when I first ran it there was a very noticable
> difference. I'm no SQL guru but this way works for me and it lets me sleep
> easy, when we do a restore I''m usually at work all night.
> Gav|||<mripplinger74@.yahoo.com.br> wrote in message
news:1152202785.006599.122210@.75g2000cwc.googlegroups.com...
> Gav,
> Just to be sure, when you say "reorg job" you are talking about use
> a maintenance plan with the option reorganize data and index page
> selected, right?
> Do you believe it is enough? Even in the tables where the PK is not a
> clustered index?
>
> Thanks,
> Marcos
Yes thats what I am talking about. My database contains approx 38,000
tables, the majority of these have clustered indexes. I have no idea how
many don't but the ones I have come accross so far are fairly insignificant
anyway. If you know of some tables without clustered indexes in your
database I would run a DBCC SHOWCONTIG before and after running a reorg job
and see what the effect is.
I plot database stats weekly and monthly, when the responce times start to
climb I run a reorg job and the responce times fall again. So from my point
of view it works fine.
Gav|||Tracy,
I agree that our database is not big, but neither our servers are. In
someplace the database is running in a workstation or in a laptop,
that's why I do care about it.
We have a maintenance plan to reorganize data and index page and we
schedule it to run every weekend, but I questioned if is it enough?
What the procedure detailed above give me more than this maintenance
plan?
Regards,
Marcos|||Sorry, I forgot to mention the guy is MCDBA :D