SQLServer空间回收测试

use master

go

drop database testdb

go

create database testdb

go

use testdb

go

create table test1 (id int,updated datetime,memo char(6000))

go

insert into test1 values (1,GETDATE(),'xxx')

go 10000

dbcc showfilestats() --1 1 1284 1273 testdb

go

backup database testdb to disk='d:backuptestdb.bak' with init,stats=10 --82MB  datafile=82

go

----------------

use testdb

go

insert into test1 values (2,GETDATE(),'xxx')

go 10000

dbcc showfilestats() --1 1 2532 2523 testdb

go

backup database testdb to disk='d:backuptestdb1.bak' with init,stats=10 --161MB datafile=162

go

delete from test1 where ID=2

go

dbcc showfilestats() --1 1 2532 1663

go

backup database testdb to disk='d:backuptestdb2.bak' with init,stats=10  --106MB datafile=162

go

--得出结论：delete堆表数据时腾出的空间未能自动收回！

use master

go

drop database testdb

go

create database testdb

go

use testdb

go

create table test1 (id int,updated datetime,memo char(6000))

go

create clustered index idx_test1 on test1 (id)

go 

insert into test1 values (1,GETDATE(),'xxx')

go 10000

dbcc showfilestats() --1 1 1284 1281 testdb

go

backup database testdb to disk='d:backuptestdb.bak' with init,stats=10 --83MB  datafile=83

go

----------------

use testdb

go

insert into test1 values (2,GETDATE(),'xxx')

go 10000

dbcc showfilestats() --1 1 2548 2539 testdb

go

backup database testdb to disk='d:backuptestdb1.bak' with init,stats=10 --163MB datafile=163

go

delete from test1 where ID=2

go

insert into test1 values (2,GETDATE(),'xxx')

go 10000

delete from test1 where ID=2

go

dbcc showfilestats() --1 1 2548 1566

go

backup database testdb to disk='d:backuptestdb2.bak' with init,stats=10  --83MB datafile=163

go

alter index idx_test1 on dbo.test1 rebuild WITH (FILLFACTOR=90,ONLINE=ON)

go

dbcc showfilestats() --1 1 2548 1283

go

dbcc shrinkdatabase (testdb,20)

go

dbcc showfilestats() --1 1 1602 1283

go

--得出结论：聚集索引重组无法回收总空间，仅能回收已用空间

use master

go

drop database testdb

go

create database testdb

go

use testdb

go

create table test1 (id int,updated datetime,memo char(6000))

go

create clustered index idx_test1 on test1 (id)

go 

create index idx_test1_m on test1 (updated)

go 

insert into test1 values (1,GETDATE(),'xxx')

go 10000

backup database testdb to disk='d:backuptestdb.bak' with init,stats=10 --83MB  datafile=83

go

dbcc showfilestats() --1 1 1300 1285 testdb

go

----------------

use testdb

go

insert into test1 values (2,GETDATE(),'xxx')

go 10000

dbcc showfilestats() --1 1 2548 2547 testdb

go

backup database testdb to disk='d:backuptestdb1.bak' with init,stats=10 --163MB datafile=163

go

delete from test1 where ID=2

go

insert into test1 values (2,GETDATE(),'xxx')

go 10000

delete from test1 where ID=2

go

dbcc showfilestats() --1 1 2548 1287

go

backup database testdb to disk='d:backuptestdb2.bak' with init,stats=10  --83MB datafile=163

go

alter index idx_test1_m on dbo.test1 rebuild WITH (FILLFACTOR=90,ONLINE=ON)

go

dbcc showfilestats() --1 1 2548 1283

go

--得出结论：非聚集索引重组无法回收总空间，仅能回收已用空间

--得出结论：回收空间还是需要收缩数据库

所以SQLserver中不存在高水位线概念，也不存在reorg table概念，同db2数据存放在裸设备上空间无法回收一样！NTFS文件系统的同RAW一样无法灵活管理！