SQLServer空间回收测试

字数 3026阅读 3783评论 3赞 0

<div><br></div><div>use master</div><div>go</div><div>drop database testdb</div><div>go</div><div>create database testdb</div><div>go</div><div>use testdb</div><div>go</div><div>create table test1 (id int,updated datetime,memo char(6000))</div><div>go</div><div>insert into test1 values (1,GETDATE(),'xxx')</div><div>go 10000</div><div>dbcc showfilestats() --1<span class="Apple-tab-span" style="white-space:pre">	</span>1<span class="Apple-tab-span" style="white-space:pre">	</span>1284<span class="Apple-tab-span" style="white-space:pre">	</span>1273<span class="Apple-tab-span" style="white-space:pre">	</span>testdb<span class="Apple-tab-span" style="white-space:pre">	</span></div><div>go</div><div>backup database testdb to disk='d:backuptestdb.bak' with init,stats=10 --82MB  datafile=82</div><div>go</div><div>----------------</div><div>use testdb</div><div>go</div><div>insert into test1 values (2,GETDATE(),'xxx')</div><div>go 10000</div><div>dbcc showfilestats() --1<span class="Apple-tab-span" style="white-space:pre">	</span>1<span class="Apple-tab-span" style="white-space:pre">	</span>2532<span class="Apple-tab-span" style="white-space:pre">	</span>2523<span class="Apple-tab-span" style="white-space:pre">	</span>testdb</div><div>go</div><div>backup database testdb to disk='d:backuptestdb1.bak' with init,stats=10 --161MB datafile=162</div><div>go</div><div>delete from test1 where ID=2</div><div>go</div><div>dbcc showfilestats() --1<span class="Apple-tab-span" style="white-space:pre">	</span>1<span class="Apple-tab-span" style="white-space:pre">	</span>2532<span class="Apple-tab-span" style="white-space:pre">	</span>1663</div><div>go</div><div>backup database testdb to disk='d:backuptestdb2.bak' with init,stats=10  --106MB datafile=162</div><div>go</div><div>--得出结论：delete堆表数据时腾出的空间未能自动收回！</div><div><br></div><div><br></div><div>use master</div><div>go</div><div>drop database testdb</div><div>go</div><div>create database testdb</div><div>go</div><div>use testdb</div><div>go</div><div>create table test1 (id int,updated datetime,memo char(6000))</div><div>go</div><div>create clustered index idx_test1 on test1 (id)</div><div>go </div><div>insert into test1 values (1,GETDATE(),'xxx')</div><div>go 10000</div><div>dbcc showfilestats() --1<span class="Apple-tab-span" style="white-space:pre">	</span>1<span class="Apple-tab-span" style="white-space:pre">	</span>1284<span class="Apple-tab-span" style="white-space:pre">	</span>1281<span class="Apple-tab-span" style="white-space:pre">	</span>testdb<span class="Apple-tab-span" style="white-space:pre">	</span></div><div>go</div><div>backup database testdb to disk='d:backuptestdb.bak' with init,stats=10 --83MB  datafile=83</div><div>go</div><div>----------------</div><div>use testdb</div><div>go</div><div>insert into test1 values (2,GETDATE(),'xxx')</div><div>go 10000</div><div>dbcc showfilestats() --1<span class="Apple-tab-span" style="white-space:pre">	</span>1<span class="Apple-tab-span" style="white-space:pre">	</span>2548<span class="Apple-tab-span" style="white-space:pre">	</span>2539<span class="Apple-tab-span" style="white-space:pre">	</span>testdb</div><div>go</div><div>backup database testdb to disk='d:backuptestdb1.bak' with init,stats=10 --163MB datafile=163</div><div>go</div><div>delete from test1 where ID=2</div><div>go</div><div>insert into test1 values (2,GETDATE(),'xxx')</div><div>go 10000</div><div>delete from test1 where ID=2</div><div>go</div><div>dbcc showfilestats() --1<span class="Apple-tab-span" style="white-space:pre">	</span>1<span class="Apple-tab-span" style="white-space:pre">	</span>2548<span class="Apple-tab-span" style="white-space:pre">	</span>1566</div><div>go</div><div>backup database testdb to disk='d:backuptestdb2.bak' with init,stats=10  --83MB datafile=163</div><div>go</div><div>alter index idx_test1 on dbo.test1 rebuild WITH (FILLFACTOR=90,ONLINE=ON)</div><div>go</div><div>dbcc showfilestats() --1<span class="Apple-tab-span" style="white-space:pre">	</span>1<span class="Apple-tab-span" style="white-space:pre">	</span>2548<span class="Apple-tab-span" style="white-space:pre">	</span>1283</div><div>go</div><div>dbcc shrinkdatabase (testdb,20)</div><div>go</div><div>dbcc showfilestats() --1<span class="Apple-tab-span" style="white-space:pre">	</span>1<span class="Apple-tab-span" style="white-space:pre">	</span>1602<span class="Apple-tab-span" style="white-space:pre">	</span>1283</div><div>go</div><div>--得出结论：聚集索引重组无法回收总空间，仅能回收已用空间</div><div><br></div><div><br></div><div>use master</div><div>go</div><div>drop database testdb</div><div>go</div><div>create database testdb</div><div>go</div><div>use testdb</div><div>go</div><div>create table test1 (id int,updated datetime,memo char(6000))</div><div>go</div><div>create clustered index idx_test1 on test1 (id)</div><div>go </div><div>create index idx_test1_m on test1 (updated)</div><div>go </div><div>insert into test1 values (1,GETDATE(),'xxx')</div><div>go 10000</div><div>backup database testdb to disk='d:backuptestdb.bak' with init,stats=10 --83MB  datafile=83</div><div>go</div><div>dbcc showfilestats() --1<span class="Apple-tab-span" style="white-space:pre">	</span>1<span class="Apple-tab-span" style="white-space:pre">	</span>1300<span class="Apple-tab-span" style="white-space:pre">	</span>1285<span class="Apple-tab-span" style="white-space:pre">	</span>testdb<span class="Apple-tab-span" style="white-space:pre">	</span></div><div>go</div><div>----------------</div><div>use testdb</div><div>go</div><div>insert into test1 values (2,GETDATE(),'xxx')</div><div>go 10000</div><div>dbcc showfilestats() --1<span class="Apple-tab-span" style="white-space:pre">	</span>1<span class="Apple-tab-span" style="white-space:pre">	</span>2548<span class="Apple-tab-span" style="white-space:pre">	</span>2547<span class="Apple-tab-span" style="white-space:pre">	</span>testdb</div><div>go</div><div>backup database testdb to disk='d:backuptestdb1.bak' with init,stats=10 --163MB datafile=163</div><div>go</div><div>delete from test1 where ID=2</div><div>go</div><div>insert into test1 values (2,GETDATE(),'xxx')</div><div>go 10000</div><div>delete from test1 where ID=2</div><div>go</div><div>dbcc showfilestats() --1<span class="Apple-tab-span" style="white-space:pre">	</span>1<span class="Apple-tab-span" style="white-space:pre">	</span>2548<span class="Apple-tab-span" style="white-space:pre">	</span>1287</div><div>go</div><div>backup database testdb to disk='d:backuptestdb2.bak' with init,stats=10  --83MB datafile=163</div><div>go</div><div>alter index idx_test1_m on dbo.test1 rebuild WITH (FILLFACTOR=90,ONLINE=ON)</div><div>go</div><div>dbcc showfilestats() --1<span class="Apple-tab-span" style="white-space:pre">	</span>1<span class="Apple-tab-span" style="white-space:pre">	</span>2548<span class="Apple-tab-span" style="white-space:pre">	</span>1283</div><div>go</div><div>--得出结论：非聚集索引重组无法回收总空间，仅能回收已用空间</div><div><div>dbcc shrinkdatabase (testdb,20)</div><div>go</div><div>dbcc showfilestats() --1<span class="Apple-tab-span" style="white-space: pre;">	</span>1<span class="Apple-tab-span" style="white-space: pre;">	</span>1602<span class="Apple-tab-span" style="white-space: pre;">	</span>1283</div><div>go</div></div><div>--<span style="line-height: 1.8em;">得出结论：回收空间还是需要收缩数据库</span></div><div><span style="line-height: 1.8em;"><br></span></div><div>所以SQLserver中不存在高水位线概念，也不存在reorg table概念，同db2数据存放在裸设备上空间无法回收一样！<span style="line-height: 1.8em;">NTFS</span><span style="line-height: 1.8em;">文件系统的同RAW一样无法灵活管理！</span></div>

master values 数据库 database insert

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！