网站 › 『 Sybase ASE 』 › [转帖]调整数据库日志空间为数据空间

ehxz 发表于 2007-11-6 15:15:07

[转帖]调整数据库日志空间为数据空间

<div class="t_msgfont" id="message7551116">1 准备工作<br/>&nbsp; &nbsp; 1、备份master和用户数据库。<br/>&nbsp; &nbsp; 2、在做以下操作时，确认没有其它业务在操作。<br/>&nbsp; &nbsp; 3、将用户数据库设置为单用户状态。<br/>&nbsp; &nbsp; sp_dboption ‘dbname’,’single’,’true’<br/>&nbsp; &nbsp; go<br/>&nbsp; &nbsp; use dbname<br/>&nbsp; &nbsp; go<br/>&nbsp; &nbsp; checkpoint<br/>&nbsp; &nbsp; go<br/><br/>2&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;查询出日志设备文件<br/>Sp_helpdb dbname<br/>go<br/><br/>输出类似如下：<br/>device_fragments&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;size&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; usage<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;created&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; free kbytes<br/>------------------------------ ------------- --------------------<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;------------------------- ----------------<br/>xtpt&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;200.0 MB data only<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Aug 10 2007&nbsp;&nbsp;1:53PM&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;186856<br/>xtpt&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;100.0 MB data only<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Aug 10 2007&nbsp;&nbsp;1:53PM&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;101936<br/>xtpt&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;300.0 MB data only<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Aug 10 2007&nbsp;&nbsp;1:53PM&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;306000<br/>xtpt&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;100.0 MB data only<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Aug 10 2007&nbsp;&nbsp;1:53PM&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;102000<br/>xtpt&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;324.0 MB data only<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Aug 10 2007&nbsp;&nbsp;1:53PM&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;330480<br/>xtpt_log&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;54.0 MB log only<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Sep 10 2007&nbsp;&nbsp;4:39PM&nbsp; &nbsp;&nbsp; &nbsp; not applicable<br/>xtpt_log1&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;100.0 MB&nbsp;&nbsp;log only<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Sep 11 2007 10:54AM&nbsp; &nbsp;&nbsp; &nbsp; not applicable<br/>如果只有一个数据库设备用于日志，这时候需要添加一个数据库日志设备。如果有多个日志设备，直接进行下一步操作。<br/><br/>3&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;查询日志段使用的设备名<br/>select sysdevices.name,sysusages.* from sysusages,sysdevices where sysusages.dbid = 6 and sysusages.vstart=sysdevices.low and sysusages.segmap = 4 order by sysusages.lstart<br/><br/>这里查询出来有多条记录，在下一步删除日志段的时候，要从后向前删除，直道达到你的要求。<br/>name&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;dbid&nbsp; &nbsp;segmap&nbsp; &nbsp;&nbsp; &nbsp;lstart&nbsp; &nbsp;&nbsp; &nbsp;size<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;vstart&nbsp; &nbsp;&nbsp; &nbsp;pad&nbsp; &nbsp; unreservedpgs crdate<br/>------------------------------ ------ ----------- ----------- -----------<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;----------- ------ ------------- --------------------------<br/>xtpt_log&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; 6&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;4&nbsp; &nbsp;&nbsp; &nbsp;131072&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;6912<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;67108864&nbsp; &nbsp;NULL&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; 6885&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Sep 10 2007&nbsp;&nbsp;4:39PM<br/>xtpt_log1&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;6&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;4&nbsp; &nbsp;&nbsp; &nbsp;137984&nbsp; &nbsp;&nbsp; &nbsp; 12800<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;83886080&nbsp; &nbsp;NULL&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;12750&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Sep 11 2007 10:54AM<br/><br/>4&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;在数据库设备上删除日志段<br/>use dbname<br/>go<br/>sp_dropsegment logsegment,dbname,devicename<br/>go<br/><br/>例如：<br/>use xtpt<br/>go<br/>sp_dropsegment logsegment,xtpt,xtpt_log1<br/>go<br/><br/>5&nbsp; &nbsp;&nbsp; &nbsp; 将日志段设备扩展为数据段设备<br/>sp_extendsegment segname,dbname,devname<br/><br/>例如：<br/>sp_extendsegment default, xtpt,xtpt_log1<br/>go<br/><br/>6&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;进行数据库检查<br/>dbcc traceon(3604)<br/>go<br/>dbcc checkdb(dbname)<br/>go<br/>dbcc checkalloc(dbname)<br/>go<br/>dbcc checkdb(master)<br/>go<br/>dbcc checkalloc(master)<br/>go<br/><br/>7&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;完成后的处理<br/>重起数据库Server，将应用数据库的单用户取消。<br/>sp_dboption ‘dbname’,’single’,’false’<br/>go<br/>use dbname<br/>go<br/>checkpoint<br/>go<br/><br/><br/>注意：<br/>&nbsp; &nbsp;&nbsp;&nbsp;以上操作是比较危险的操作，所以请在操作前一定注意做好数据备份。</div><div class="t_msgfont"><div class="msgheader">QUOTE:</div><div class="msgborder"><div class="t_msgfont">FROM:http://bbs.chinaunix.net/thread-1011484-1-1.html</div><div class="t_msgfont">作者：<a class="bold" href="http://bbs.chinaunix.net/profile-uid-181706.html" target="_blank">jarjar</a>
                        </div></div></div>

查看完整版本: [转帖]调整数据库日志空间为数据空间