数据库体积增大问题 ( 积分: 100 )

数据库是SQL&nbsp;Server&nbsp;2000,发现数据库文件增大的特别快，不是日志文件，而是数据文件(mdf),试过收缩，但收缩的有限，仔细看了一下，是表的空间浪费太多。sp_spaceused显示有的表unused空间到几百M。找到了微软的一个解释，可试了之后仍不能释放表的剩余空间，哪位大侠知道怎么回事？谢谢<br>附：微软的官方解释。<br><br><br>Space&nbsp;is&nbsp;not&nbsp;released&nbsp;after&nbsp;you&nbsp;delete&nbsp;some&nbsp;rows&nbsp;from&nbsp;a&nbsp;table&nbsp;in&nbsp;SQL&nbsp;Server&nbsp;2000<br>View&nbsp;products&nbsp;that&nbsp;this&nbsp;article&nbsp;applies&nbsp;to.<br>Article&nbsp;ID&nbsp;:&nbsp;934378&nbsp;<br>Last&nbsp;Review&nbsp;:&nbsp;April&nbsp;4,&nbsp;2007&nbsp;<br>Revision&nbsp;:&nbsp;1.0&nbsp;<br>SYMPTOMS<br>Consider&nbsp;the&nbsp;following&nbsp;scenario.&nbsp;You&nbsp;delete&nbsp;some&nbsp;rows&nbsp;from&nbsp;a&nbsp;table&nbsp;in&nbsp;Microsoft&nbsp;SQL&nbsp;Server&nbsp;2000.&nbsp;You&nbsp;run&nbsp;the&nbsp;sp_spaceused&nbsp;stored&nbsp;procedure&nbsp;against&nbsp;the&nbsp;table.&nbsp;In&nbsp;this&nbsp;scenario,&nbsp;the&nbsp;result&nbsp;of&nbsp;the&nbsp;sp_spaceused&nbsp;stored&nbsp;procedure&nbsp;shows&nbsp;that&nbsp;lots&nbsp;of&nbsp;space&nbsp;in&nbsp;the&nbsp;table&nbsp;is&nbsp;not&nbsp;released.&nbsp;This&nbsp;problem&nbsp;is&nbsp;not&nbsp;resolved&nbsp;even&nbsp;after&nbsp;you&nbsp;run&nbsp;the&nbsp;DBCC&nbsp;UPDATEUSAGE&nbsp;statement&nbsp;against&nbsp;the&nbsp;table.<br>Back&nbsp;to&nbsp;the&nbsp;top<br><br>CAUSE<br>This&nbsp;problem&nbsp;occurs&nbsp;because&nbsp;the&nbsp;Page&nbsp;Free&nbsp;Space&nbsp;(PFS)&nbsp;ghost&nbsp;flag&nbsp;is&nbsp;not&nbsp;set&nbsp;correctly&nbsp;on&nbsp;the&nbsp;PFS&nbsp;page.&nbsp;Therefore,&nbsp;the&nbsp;ghost&nbsp;row&nbsp;clean-up&nbsp;thread&nbsp;thinks&nbsp;that&nbsp;no&nbsp;ghost&nbsp;row&nbsp;exists&nbsp;in&nbsp;the&nbsp;data&nbsp;pages&nbsp;that&nbsp;are&nbsp;covered&nbsp;by&nbsp;the&nbsp;PFS&nbsp;page.<br>Back&nbsp;to&nbsp;the&nbsp;top<br><br>WORKAROUND<br>To&nbsp;work&nbsp;around&nbsp;this&nbsp;problem,&nbsp;use&nbsp;one&nbsp;of&nbsp;the&nbsp;following&nbsp;methods:?&nbsp;Rebuild&nbsp;the&nbsp;index&nbsp;or&nbsp;the&nbsp;heap&nbsp;that&nbsp;the&nbsp;data&nbsp;that&nbsp;you&nbsp;deleted&nbsp;belongs&nbsp;to.&nbsp;After&nbsp;the&nbsp;index&nbsp;or&nbsp;the&nbsp;heap&nbsp;is&nbsp;rebuilt,&nbsp;the&nbsp;PFS&nbsp;page&nbsp;will&nbsp;be&nbsp;re-created.&nbsp;Additionally,&nbsp;the&nbsp;PFS&nbsp;ghost&nbsp;flag&nbsp;will&nbsp;be&nbsp;set&nbsp;correctly&nbsp;on&nbsp;the&nbsp;PFS&nbsp;page.&nbsp;<br>?&nbsp;Add&nbsp;a&nbsp;new&nbsp;record&nbsp;to&nbsp;the&nbsp;table,&nbsp;and&nbsp;then&nbsp;delete&nbsp;the&nbsp;row&nbsp;that&nbsp;you&nbsp;added.&nbsp;<br><br>Back&nbsp;to&nbsp;the&nbsp;top<br><br>STATUS<br>Microsoft&nbsp;has&nbsp;confirmed&nbsp;that&nbsp;this&nbsp;is&nbsp;a&nbsp;problem&nbsp;in&nbsp;the&nbsp;Microsoft&nbsp;products&nbsp;that&nbsp;are&nbsp;listed&nbsp;in&nbsp;the&nbsp;&quot;Applies&nbsp;to&quot;&nbsp;section.<br>Back&nbsp;to&nbsp;the&nbsp;top<br><br>MORE&nbsp;INFORMATION<br>When&nbsp;a&nbsp;row&nbsp;is&nbsp;deleted&nbsp;from&nbsp;a&nbsp;table,&nbsp;the&nbsp;following&nbsp;things&nbsp;occur:?&nbsp;The&nbsp;row&nbsp;is&nbsp;marked&nbsp;to&nbsp;be&nbsp;a&nbsp;ghost&nbsp;row.&nbsp;<br>?&nbsp;The&nbsp;PFS&nbsp;page&nbsp;is&nbsp;updated.&nbsp;It&nbsp;indicates&nbsp;that&nbsp;the&nbsp;data&nbsp;page&nbsp;where&nbsp;the&nbsp;deleted&nbsp;row&nbsp;originally&nbsp;existed&nbsp;contains&nbsp;a&nbsp;ghost&nbsp;row.&nbsp;<br>?&nbsp;A&nbsp;PFS&nbsp;ghost&nbsp;flag&nbsp;is&nbsp;set&nbsp;on&nbsp;the&nbsp;PFS&nbsp;page.&nbsp;<br>When&nbsp;the&nbsp;ghost&nbsp;row&nbsp;clean-up&nbsp;thread&nbsp;runs,&nbsp;the&nbsp;thread&nbsp;performs&nbsp;the&nbsp;following&nbsp;steps:1.&nbsp;The&nbsp;thread&nbsp;checks&nbsp;whether&nbsp;the&nbsp;PFS&nbsp;ghost&nbsp;flag&nbsp;is&nbsp;set&nbsp;on&nbsp;the&nbsp;PFS&nbsp;page.&nbsp;If&nbsp;the&nbsp;PFS&nbsp;ghost&nbsp;flag&nbsp;is&nbsp;not&nbsp;set,&nbsp;the&nbsp;thread&nbsp;knows&nbsp;that&nbsp;no&nbsp;ghost&nbsp;row&nbsp;exists&nbsp;in&nbsp;the&nbsp;data&nbsp;pages&nbsp;that&nbsp;are&nbsp;covered&nbsp;by&nbsp;the&nbsp;PFS&nbsp;page.&nbsp;The&nbsp;thread&nbsp;will&nbsp;skip&nbsp;the&nbsp;PFS&nbsp;page&nbsp;and&nbsp;check&nbsp;the&nbsp;next&nbsp;PFS&nbsp;page.&nbsp;<br>2.&nbsp;If&nbsp;the&nbsp;PFS&nbsp;ghost&nbsp;flag&nbsp;is&nbsp;set&nbsp;on&nbsp;the&nbsp;PFS&nbsp;page,&nbsp;the&nbsp;thread&nbsp;searches&nbsp;in&nbsp;the&nbsp;PFS&nbsp;page&nbsp;to&nbsp;find&nbsp;data&nbsp;pages&nbsp;that&nbsp;have&nbsp;ghost&nbsp;rows.&nbsp;<br>3.&nbsp;For&nbsp;each&nbsp;data&nbsp;page&nbsp;that&nbsp;the&nbsp;thread&nbsp;finds,&nbsp;the&nbsp;thread&nbsp;deletes&nbsp;the&nbsp;ghost&nbsp;rows&nbsp;in&nbsp;the&nbsp;data&nbsp;page.&nbsp;<br><br>Back&nbsp;to&nbsp;the&nbsp;top<br><br><br>--------------------------------------------------------------------------------<br><br>APPLIES&nbsp;TO<br>?&nbsp;Microsoft&nbsp;SQL&nbsp;Server&nbsp;2000&nbsp;Standard&nbsp;Edition&nbsp;<br>?&nbsp;Microsoft&nbsp;SQL&nbsp;Server&nbsp;2000&nbsp;Workgroup&nbsp;Edition&nbsp;<br>?&nbsp;Microsoft&nbsp;SQL&nbsp;Server&nbsp;2000&nbsp;Developer&nbsp;Edition&nbsp;<br>?&nbsp;Microsoft&nbsp;SQL&nbsp;Server&nbsp;2000&nbsp;Enterprise&nbsp;Edition

你的表里面可能大量使用了Char之类的字段

mdb确实有这个问题.一般可以在程序关闭前pack一次.pack的例子论坛搜吧,应该不少

字串类型最好用varchar,而不用Char

優化以下數據庫的表結構等，然後壓縮下數據庫看看呢！

谢谢各位的回答，系统数据库是由SQL７升级而来，SQL&nbsp;7时没有出现这种情况，现在增长的速度那叫一个快，有表中的未用空间很多，但就是释放不出来。

升级的时候没有选对结构吧

我现在用的数据库&nbsp;&nbsp;一个表就2G多&nbsp;真晕&nbsp;一删数据就出现3G日志

我们也试过，后来打完补丁后，将数据做了一次导入导出。问题解决。

来者有分!

多人接受答案了。