1

As I understand it, based on some research but zero real-world experience with Sybase ASE, the only way to reclaim disk space once it's been allocated to a database is to export that database, create a new DB with the same schema, and reload all the exported data to the new database. Is this correct, or is there some other method?

Then: assuming the above is correct and a full export-recreate-reload is required, what's the most efficient way to do that? Are there tools that will automate all or part of that process? I'm being told we would have to write separate bcp export and import commands for each and every object in the database, which if true sounds easily scriptable by someone who knows Sybase ASE well enough. (I don't.)

This seems to me like a really basic housekeeping task, and it feels like I'm missing something obvious.

Ernie Longmire
  • 11
  • 2

3 Answers3

1

reorg rebuild

Tweak space management settings before the rebuild if needed. When reorg rebuild rebuilds a table, it rewrites all table and index rows according to the table’s current settings for reservepagegap, fillfactor, and exp_row_size. These properties all affect how quickly inserts cause a table to become fragmented, as measured by a low cluster ratio.

mgorven
  • 30,036
  • 7
  • 76
  • 121
joe
  • 11
  • 1
1

Your research is absolutley correct. Once you allocate device space to a database, you can't claim that space back. The only way to get disk space back is to drop the database, and the associated data devices.

To get the necessary database objects from the database if you don't already have them is to use the ddlgen utility. This will reverse engineer the database objects (tables, columns, triggers, procedures, etc.), and put it in a script file that can be executed to recreate the objects in an empty database.

To export the data, you can get the listing of user tables with the following query:

select name from [YOUR_DATABASE_NAME]..sysobjects where type = "U"
go

Capture the output into a seperate file, and it's easy to loop through to bcp out all the data.

Mathias R. Jessen
  • 24,907
  • 4
  • 62
  • 95
Michael Gardner
  • 113
  • 5
0

Check out sp100 new features, including database shrinking. http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01165.1600/doc/html/car1351120097458.html

Zion
  • 1