Recovery Mode 是 Simple 或 Bulk_Logged 在 Rebuild Index 可以省硬碟空間,但仍會耗空間

http://msdn.microsoft.com/en-us/library/ms191484(SQL.90).aspx

create database db
go
use db
alter database db set recovery simple
go
create table t(c1 int identity,c2 char(900) default convert(char(900),rand()))
go
set nocount on
go
insert t default values
go 100000

select * from t
–db 399616 KB log 576 KB
exec sp_helpdb ‘db’

–log 3904 kb
create index idx on t(c2)
–log 576 KB
dbcc shrinkfile(db_log)

alter index idx on t rebuild

–log 3520 kb
exec sp_helpdb ‘db’

–log 576 KB
dbcc shrinkfile(db_log)

alter database db set recovery bulk_logged

alter index idx on t rebuild

–log 3520 kb
exec sp_helpdb ‘db’

 

alter database db set recovery full

alter index idx on t rebuild

–log 125888 KB
exec sp_helpdb ‘db’

use master
drop database db

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: