先发个帖子问个问题

wycking

免费乐捐，感谢支持

我原本建立的数据库很大，现在觉得用不了那么多的空间，我想把它压缩小一点，该如何去做呢？

ehxz

SYBASE实现起来好象比较麻烦~~要重建表，然后把数据BCP一遍的样子。

其它的我也不懂了。

wycking

这么麻烦，真是郁闷！！！

hchi0118

把表、触发器和存储过程用ddl导出后再导到新库去，再用bcp导一下数据就可以了，不是太麻烦啊

[em05]

guduxiao

厉害啊！!!

jesse411

就是这样

flybean

警告：本方法未经官方测试、官方认可。使用者自行承担一切后果！！！！

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.
Details
1. Dump your database. If anything goes wrong, you will need to recover from this backup!
2. Decide how many megabytes of space you wish to remove from your database.
3. Examine sysusages for the database. You will be shrinking the database by removing the fragments with the highest lstart values. If the current fragments are not of appropriate sizes, you may need to drop the database, recreate it so there are more fragments, and reload the dump.
A trivial case: An example of a time when you can easily shrink a database is if you have just altered it and are sure there has been no activity on the new fragment. In this case, you can directly delete the last row in sysusages for the db (this row was just added by alter db) and reboot the server and it should come up cleanly.
4. Change the segmaps of the fragments you plan to remove to 0. This will prevent future data allocations to these fragments.
Note: If any of the fragments you are using have user defined segments on them, drop those segments before doing this.
sp_configure "allow updates", 1
go
reconfigure with override -- not necessary in System 11
go
update sysusages
set segmap = 0
where dbid = <dbid>
and lstart = <lstart>
go
dbcc dbrepair(<dbname>, remap)
go
Ensure that there is at least one data (segmap 3) and one log (segmap 4) fragment, or one mixed (segmap 7) fragment.
If the server has been in use for some time, you can shrink it by deleting rows from sysusages for the db, last rows first, after making sure that no objects have any allocations on the usages.
5. Determine which objects are on the fragments you plan to remove.
traceon(3604)
go
dbcc usedextents( dbid,0,0,1)
go
Find the extent with the same value as the lstart of the first fragment you plan to drop. You need to migrate every object appearing from this point on in the output.
6. Migrate these objects onto earlier fragments in the database.
Objids other than 0 or 99 are objects that you must migrate or drop. You can migrate a user table by building a new clustered index on the table (since the segmap was changed, the new allocations will not go on this fragment).
You can migrate some system tables (but not all) using the sp_fixindex command to rebuild its clustered index. However, there are a few system tables that cannot have their clustered indexes rebuilt, and if they have any allocations on the usage, you are out of luck.
If the objid is 8, then it is the log. You can migrate the log by ensuring that another usage has a log segment (segmap 4 or 7). Do enough activity on the database to fill an extents worth of log pages, then checkpoint and dump tran.
Once you have moved all the objects, delete the row from sysusages and reboot the server.
Run dbcc checkdb and dbcc checkalloc on the database to be sure you are ok, then dump the database again.

[此贴子已经被作者于2005-10-10 10:50:17编辑过]

flybean

免费乐捐，感谢支持

警告：以下方法未经官方测试、官方认可，使用者自行承担责任！！

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.

Details

1. Dump your database. If anything goes wrong, you will need to recover from this backup!
2. Decide how many megabytes of space you wish to remove from your database.
3. Examine sysusages for the database. You will be shrinking the database by removing the fragments with the highest lstart values. If the current fragments are not of appropriate sizes, you may need to drop the database, recreate it so there are more fragments, and reload the dump.
   A trivial case: An example of a time when you can easily shrink a database is if you have just altered it and are sure there has been no activity on the new fragment. In this case, you can directly delete the last row in sysusages for the db (this row was just added by alter db) and reboot the server and it should come up cleanly.
4. Change the segmaps of the fragments you plan to remove to 0. This will prevent future data allocations to these fragments.
   Note: If any of the fragments you are using have user defined segments on them, drop those segments before doing this.
   sp_configure "allow updates", 1
   go
   reconfigure with override -- not necessary in System 11
   go
   update sysusages
   set segmap = 0
   where dbid = <dbid>
   and lstart = <lstart>
   go
   dbcc dbrepair(<dbname>, remap)
   go
   Ensure that there is at least one data (segmap 3) and one log (segmap 4) fragment, or one mixed (segmap 7) fragment.
   If the server has been in use for some time, you can shrink it by deleting rows from sysusages for the db, last rows first, after making sure that no objects have any allocations on the usages.
5. Determine which objects are on the fragments you plan to remove.
   traceon(3604)
   go
   dbcc usedextents( dbid,0,0,1)
   go
   Find the extent with the same value as the lstart of the first fragment you plan to drop. You need to migrate every object appearing from this point on in the output.
6. Migrate these objects onto earlier fragments in the database.
   Objids other than 0 or 99 are objects that you must migrate or drop. You can migrate a user table by building a new clustered index on the table (since the segmap was changed, the new allocations will not go on this fragment).
   You can migrate some system tables (but not all) using the sp_fixindex command to rebuild its clustered index. However, there are a few system tables that cannot have their clustered indexes rebuilt, and if they have any allocations on the usage, you are out of luck.
   If the objid is 8, then it is the log. You can migrate the log by ensuring that another usage has a log segment (segmap 4 or 7). Do enough activity on the database to fill an extents worth of log pages, then checkpoint and dump tran.
   Once you have moved all the objects, delete the row from sysusages and reboot the server.
   Run dbcc checkdb and dbcc checkalloc on the database to be sure you are ok, then dump the database again.

xxgsymj

嘿嘿

俺也想当初那样整 结果缩不小

所以俺就自己建了个库 先建表 再用公司自己的一个应用(就相当于数据管道之类的) 把数据分别导进来 呵呵,,很快的