Solved

sybase, How to shrink database files

Posted on 2009-07-11
8
2,017 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

773 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