Shrinking Database
Ivan Santhumayor
defined segments etc..). I recently had a problem doing the shrink. After
restoring the sysusages fragments back to the original, allowed me to recover
the database. I then resorted to SQL-Backtrack logical method. This method is
very time consuming and at times drops stored procs which cannot be compiled due
to dependencies on #temp tables created elsewhere. Apart from this, its the most
stable and safest approach.
DFonza wrote:
> I forgot to add: you may run into a few undocumented ways of shrinking the
> database. If Sybase tech support approves of the method and supports it, go
> for it. Coming from a production environment, I'm not comfortable tweaking
> sysusages and running various dbcc commands as tech support has always said,
> "its not supported". If you have a good backup and a restore would take a
> short time, it may be worth trying. Just make sure you have several good
> backups...
>
> DF
--
Ivan Santhumayor
Firmwide Database Support
Goldman Sachs & Co
Note: These are my own views and not that of Goldman Sachs
Mark Miles
27GB, we have just been through an excercise to tidy up the database and
remove unwanted data. We have removed about 17% of the data but know I want
to shrink the database. Our DBA has performed the usual dropping and
recreating indices but he has said that to shrink the database he needs to
BCP all the tables (and some are big), recreate the database at the new size
then BCK the tables back in, which could take a few days.
Isn't there a better way to do this, I saw a article for SQL 7.0 with a DBCC
SHRINKDATABASE function but the SyBase books do not mention this for there
database or are there third party products that can do this easily?
Any help is welcome
Mark Miles
DFonza
database other than bcping out the data, using defgen to get object defs, and
using some other tool to get the ddl (Sybase Central or PowerDesigner).
If you'd like an easier way, look into using SQL Backtrack (logical database
dump/load). If you are under time constraints, it may be quicker to use the
Sybase tools unless you can find someone knowledgeable in SQL Backtrack or the
equivalent.
Good luck.
Don
DFonza
Mark Miles
We have about 3-4 months to come up with a solutions then say another
month to act on it. You both mentioned SQL-Backtrack, is there a site I can
point our DBA to get info on this?
Again thank you both for a quick responsse.
Mark
"Ivan Santhumayor" <ivan.san...@gs.com> wrote in message
news:38F3EECD...@gs.com...
> The undocumented method does work for simple database structures (no user
> defined segments etc..). I recently had a problem doing the shrink. After
> restoring the sysusages fragments back to the original, allowed me to
recover
> the database. I then resorted to SQL-Backtrack logical method. This method
is
> very time consuming and at times drops stored procs which cannot be
compiled due
> to dependencies on #temp tables created elsewhere. Apart from this, its
the most
> stable and safest approach.
>
>
> DFonza wrote:
>
DFonza
the links (products and services).
Ermedin Selmanovic
Your DBA is correct in saying that only way to do it is to BCP the data out,
re-create the database from scratch, allocating less space to it and then BCP
the data in. To speed up BCP in, indices should be dropped and put back on
once the data has been imported. Although it may take long time to do it,
for 27GB database on WinNT server it should take between 48 to 60hrs,
depending on network performance. We have done this in the past and have
not experienced any problems with it.
You should also notice an improvement in database performance since
re-creation of the database and BCP’ing the data in utilises the data and index
pages to do maximum. There are people out there who regularly re-create their
databases to gain on performance. Also these types of exercises give you good
chance to rehears your disaster recovery scenarios for Sybase database
recoveries.
I hope this helps,
Ermedin