Sybase Question:
Download Questions PDF

How to Shrink a Database?


It has historically been difficult to shrink any database except tempdb (because it is created fresh every boot time). The two methods commonly used have been:

1. Ensure that you have scripts for all your objects (some tools like SA Companion, DB Artisan or from Sybperl can create scripts from an existing database), then bcp out your data, drop the database, recreate it smaller, run your scripts, and bcp in your data.
2. Use a third-party tool such as DataTool's SQL Backtrack, which in essence automates the first process.

This technote outlines a third possibility that can work in most cases.
An Unsupported Method to Shrink a Database
This process is fairly trivial in some cases, such as removing a recently added fragment or trimming a database that has a log fragment as its final allocation, but can also be much more complicated or time consuming than the script and bcp method.

General Outline
The general outline of how to do it is:
1. Make a backup of the current database
2. Migrate data from sysusages fragments with high lstart values to fragments with low lstart values.
3. Edit sysusages to remove high lstart fragments that no longer have data allocations.
4. Reboot ASE.

Download Sybase Interview Questions And Answers PDF

Previous QuestionNext Question
How do I audit the SQL sent to the server?How to clear a log suspend?