先发个帖子问个问题
我原本建立的数据库很大，现在觉得用不了那么多的空间，我想把它压缩小一点，该如何去做呢？ <P>SYBASE实现起来好象比较麻烦~~要重建表，然后把数据BCP一遍的样子。</P><P>其它的我也不懂了。</P> 这么麻烦，真是郁闷！！！ <P>把表、触发器和存储过程用ddl导出后再导到新库去，再用bcp导一下数据就可以了，不是太麻烦啊</P> 厉害啊！!! 就是这样 警告：本方法未经官方测试、官方认可。使用者自行承担一切后果！！！！<br><br>
An Unsupported Method to Shrink a Database<br>
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. <br>
General Outline<br>
The general outline of how to do it is: <br>
1. Make a backup of the current database <br>
2. Migrate data from sysusages fragments with high lstart values to fragments with low lstart values. <br>
3. Edit sysusages to remove high lstart fragments that no longer have data allocations. <br>
4. Reboot ASE. <br>
Details<br>
1. Dump your database. If anything goes wrong, you will need to recover from this backup! <br>
2. Decide how many megabytes of space you wish to remove from your database. <br>
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. <br>
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. <br>
4. Change the segmaps of the fragments you plan to
remove to 0. This will prevent future data allocations to these
fragments. <br>
Note: If any of the fragments you are using have user defined segments on them, drop those segments before doing this.<br>
sp_configure "allow updates", 1<br>
go<br>
reconfigure with override-- not necessary in System 11<br>
go<br>
update sysusages<br>
set segmap = 0<br>
where dbid = <dbid><br>
and lstart = <lstart><br>
go<br>
dbcc dbrepair(<dbname>, remap)<br>
go <br>
Ensure that there is at least one data (segmap 3) and one log (segmap 4) fragment, or one mixed (segmap 7) fragment. <br>
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. <br>
5. Determine which objects are on the fragments you plan to remove. <br>
traceon(3604)<br>
go<br>
dbcc usedextents( dbid,0,0,1)<br>
go <br>
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. <br>
6. Migrate these objects onto earlier fragments in the database. <br>
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). <br>
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. <br>
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. <br>
Once you have moved all the objects, delete the row from sysusages and reboot the server. <br>
Run dbcc checkdb and dbcc checkalloc on the database to be sure you are ok, then dump the database again. <br>
<br>
[此贴子已经被作者于2005-10-10 10:50:17编辑过]
<p 0in; font-weight: bold; font-family: SimSun; font-size: 16pt;">警告：以下方法未经官方测试、官方认可，使用者自行承担责任！！<br>
<br>
</p>
<p 0in; font-weight: bold; font-family: SimSun; font-size: 16pt;">An Unsupported Method to Shrink a Database</p>
<p 0in; font-family: SimSun; font-size: 10pt;">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. </p>
<p 0in; font-weight: bold; font-family: SimSun; font-size: 12pt;">General Outline</p>
<p 0in; font-family: SimSun; font-size: 10pt;">The
general outline of how to do it is: </p>
<ol 1in; direction: ltr; unicode-bidi: embed; margin-top: 0in; margin-bottom: 0in;" type="1">
<li value="1" 0pt; margin-bottom: 0pt; vertical-align: middle; font-family: SimSun; font-size: 10pt;">Make a backup
of the current database </li><li value="2" 0pt; margin-bottom: 0pt; vertical-align: middle; font-family: SimSun; font-size: 10pt;">Migrate data
from sysusages fragments with high lstart values to fragments with low
lstart values. </li><li value="3" 0pt; margin-bottom: 0pt; vertical-align: middle; font-family: SimSun; font-size: 10pt;">Edit sysusages
to remove high lstart fragments that no longer have data allocations. </li><li value="4" 0pt; margin-bottom: 0pt; vertical-align: middle; font-family: SimSun; font-size: 10pt;">Reboot ASE. </li>
</ol>
<p 0in; font-weight: bold; font-family: SimSun; font-size: 12pt;">Details</p>
<ol 1in; direction: ltr; unicode-bidi: embed; margin-top: 0in; margin-bottom: 0in;" type="1">
<li value="1" 0pt; margin-bottom: 0pt; vertical-align: middle; font-family: SimSun; font-size: 10pt;">Dump your
database. If anything goes wrong, you will need to recover from this
backup! </li><li value="2" 0pt; margin-bottom: 0pt; vertical-align: middle; font-family: SimSun; font-size: 10pt;">Decide how many
megabytes of space you wish to remove from your database. </li><li value="3" 0pt; margin-bottom: 0pt; vertical-align: middle; font-family: SimSun; font-size: 10pt;">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. <br>
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. </li><li value="4" 0pt; margin-bottom: 0pt; vertical-align: middle; font-family: SimSun; font-size: 10pt;">Change the
segmaps of the fragments you plan to remove to 0. This will prevent future
data allocations to these fragments. <br>
Note: If any of the fragments you are using have user defined
segments on them, drop those segments before doing this.<br>
sp_configure "allow updates", 1<br>
go<br>
reconfigure with override-- not necessary in System 11<br>
go<br>
update sysusages<br>
set segmap = 0<br>
where dbid = <dbid><br>
and lstart = <lstart><br>
go<br>
dbcc dbrepair(<dbname>, remap)<br>
go <br>
Ensure that there is at least one data (segmap 3) and one log (segmap
4) fragment, or one mixed (segmap 7) fragment. <br>
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. </li><li value="5" 0pt; margin-bottom: 0pt; vertical-align: middle; font-family: SimSun; font-size: 10pt;">Determine which
objects are on the fragments you plan to remove. <br>
traceon(3604)<br>
go<br>
dbcc usedextents( dbid,0,0,1)<br>
go <br>
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. </li><li value="6" 0pt; margin-bottom: 0pt; vertical-align: middle; font-family: SimSun; font-size: 10pt;">Migrate these
objects onto earlier fragments in the database. <br>
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). <br>
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. <br>
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. <br>
Once you have moved all the objects, delete the row from sysusages
and reboot the server. <br>
Run dbcc checkdb and dbcc checkalloc on the database to be sure you
are ok, then dump the database again. </li>
</ol>
<P>嘿嘿</P>
<P>俺也想当初那样整 结果缩不小</P>
<P>所以俺就自己建了个库 先建表 再用公司自己的一个应用(就相当于数据管道之类的) 把数据分别导进来 呵呵,,很快的</P>
页:
[1]