Solved

sybase, How to shrink database files

Posted on 2009-07-11
8
1,890 Views
Last Modified: 2012-05-07
Hi,
In sybase, does anyone know how to shrink database files? will that be as easy as sql server ?
0
Comment
Question by:motioneye
8 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24830368
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
 
LVL 14

Expert Comment

by:shru_0409
ID: 24830452
0
 
LVL 6

Accepted Solution

by:
IncisiveOne earned 500 total points
ID: 24831581
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
 

Author Comment

by:motioneye
ID: 24834196
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:motioneye
ID: 24836737
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
 
LVL 6

Assisted Solution

by:IncisiveOne
IncisiveOne earned 500 total points
ID: 24836909
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
 

Author Comment

by:motioneye
ID: 24837396
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
 
LVL 6

Assisted Solution

by:IncisiveOne
IncisiveOne earned 500 total points
ID: 24837849
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Backup Job question 4 19
Concatenating multiple comments into one row 16 42
Report Builder 9 30
shrink datafile Sql server 4 14
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now