sybase, How to shrink database files

Hi,
In sybase, does anyone know how to shrink database files? will that be as easy as sql server ?
motioneyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
I do....

dbcc shrinkfile('databasefilename', sizeinmbtoshrinkto)

so,
dbcc shrinkfile('mydbname_data', 1000)  --shrink to 1gb

or
dbcc shrinkfile('mydb_log', 0)  --//would need to be done after the log was truncated (backed up)

you can also do it in ssms, right click the db, go to tasks, then shrink files

HTH,
Tim
0
IncisiveOneCommented:
I am aware of those MS SQL commands.  More important, there is usually just one Database on the "server" so shrinking a device usually means shrinking a database.

    > In SYBASE [not MS], does anyone know how to shrink database files?

You cannot shrink database "files" in Sybase.  Sybase allows either /ufs files or raw partitions to be used as DEVICES; Databases are allocated to Devices; there may be many Databases per Device; so, if anything, you will be shrinking/re-sizing Devices, certainly not Databases.  Shrinking Databases (on several Devices) is different again.

Ok, you can shrink Databases and Devices in Sybase, but that requires performing brain surgery, and is not supported.  Furthermore, it is definitely only for very experienced DBAs, I would be doing you (and everyone who reads the EE archives) a disservice if I provided it here.

Cheers

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

motioneyeAuthor Commented:
Hi IncisiveOne,
Yes thanks for your explanation, yes a storage device in sybase can be share between database unlike Mcsft sql where one file per database.
0
motioneyeAuthor Commented:
Hi,
How about if I want to shrink storage device, will that be possible? Here you see that I'm having plenty of free spae

 device_name physical_name
         description
         status cntrltype vdevno vpn_low vpn_high
 ----------- ----------------------
         ------------------------------------------------------------------------------------------
         ------ --------- ------ ------- --------
 TestLog     G:\sybase\data\TestLog
         file system device, special, dsync on, directio off, physical disk, 12000
         .00 MB, Free: 11500.00 MB
         

(1 row affected)
 dbname size          allocated           vstart lstart
 ------ ------------- ------------------- ------ ------
 Test         7.00 MB Apr 18 2009  6:19PM      0   4096
 XFDS         3.00 MB May 12 2009 11:47PM   3584   1024

(1 row affected)
(return status = 0)
0
IncisiveOneCommented:
The appropriate, supported method is:
1 dump database to dump_file
2 ensure you have scripts for recreating the db, with the 'create/alter db' commands in the correct chronological sequence
3 drop database
4 [when the device is empty] drop device
5 create new device with correct size
6 create db from script [2]
7 load database from dump_file
Your database will be fine (it will have mixed data/log segments only if you got [2] wrong )

With small databases, it is very easy.

Cheers

0
motioneyeAuthor Commented:
Hi,
Not so practical when huge db, but will work on such small db, btw good to know how to manage space with sybase.
0
IncisiveOneCommented:
There are a lot more options to space management in Sybase, it being designed for larger systems, which really means Unix, which really means people expect Unix capabilities, and would be upset if restricted to Windoze capabilities.

Use Raw Partitions instead of /ufs files for Devices, they are much faster.

Obviously you have to plan and prepare your Devices when you build the server, and when you add databases.  If you want advice on how to do that properly (so that you never have to drop/create Devices), post another question.

Creating Devices that are too large or that cannot be used means that this step has not been done, and the Devices need to be dropped at some point.

Cheers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.