Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sybase, How to shrink database files

Posted on 2009-07-11
8
Medium Priority
?
2,398 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 6

Accepted Solution

by:
IncisiveOne earned 2000 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 2000 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

610 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