Log Shipping 會將來源資料庫 rebuild index 的結果一併帶到目的資料庫嗎?

朋友的問題,看來是可以,測試如下:

create database dbOrg
use dbOrg
create table t(c1 int identity constraint pk primary key,
c2 time(7) default(sysdatetime()),
c3 varchar(10))
go
insert t(c3) values(‘a’)
go
insert t(c3) select c3 from t
go 20

declare @dbid int= DB_ID(N’dbOrg’);
declare @objectid int = OBJECT_ID(N’t’);
select * from sys.dm_db_index_physical_stats (@dbid, @objectid, NULL , NULL, N’LIMITED’)

image

 

–restore 的資料庫其索引狀況破碎程度相同

backup database dbOrg to disk=’c:\temp\dbOrg.bak’
restore filelistonly from disk=’c:\temp\dbOrg.bak’
restore database dbOrg2 from disk=’c:\temp\dbOrg.bak’
with move ‘dbOrg’ to ‘c:\temp\dbOrg2.mdf’,move ‘dbOrg_log’ to ‘c:\temp\dbOrg2.ldf’,standby=’C:\dbOrgStandby.bak’,replace
go
declare @dbid int= DB_ID(N’dbOrg2′);
declare @objectid int = OBJECT_ID(N’t’);
select * from sys.dm_db_index_physical_stats (@dbid, @objectid, NULL , NULL, N’LIMITED’)
go

image

 

–重整索引,以清除破碎

alter index pk on t rebuild
go
declare @dbid int= DB_ID(N’dbOrg’);
declare @objectid int = OBJECT_ID(N’t’);
select * from sys.dm_db_index_physical_stats (@dbid, @objectid, NULL , NULL, N’LIMITED’)

image

–檢視 Log 檔可以看到大幅增加 size 外,tx log 的內容我看不懂,但似乎是移動 data page 的相關資料

SELECT * FROM sys.fn_dblog(NULL,NULL)

–備份並還原交易紀錄,看起來目的端 index 也經由交易紀錄重建索引了

backup log dbOrg to disk=’c:\temp\dbOrgLog.bak’
restore log dbOrg2 from disk=’c:\temp\dbOrgLog.bak’ with recovery

go

declare @dbid int= DB_ID(N’dbOrg2′);
declare @objectid int = OBJECT_ID(N’t’);
select * from sys.dm_db_index_physical_stats (@dbid, @objectid, NULL , NULL, N’LIMITED’)

image

單純臆測,就檔案大小來看,rebuild index 的內容應該可以透過 log backup/restore 讓目的端一併達到 rebuild 的結果

image

發表迴響

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

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 位部落客按了讚: