Solved

sybase, How to shrink database files

Posted on 2009-07-11
8
1,950 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

914 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

21 Experts available now in Live!

Get 1:1 Help Now