はるかのブログ

SQLServerのログシッピングは、トランザクションバックアップの出力、コピー、リストアが別々のジョブで実行される。

デフォルトでログシッピングを作成した場合、15分間隔でジョブが作成されるが、MasterとSlaveのデータ差異が１５分とは限らない為、Slave機で以下のクエリを流して同期状態を確認する。

[code]

SET NOCOUNT ON
USE master

/* DB名を入れる変数 */
DECLARE @dbname varchar(80);

/* DB一覧 */
DECLARE DBnameList CURSOR FOR
select name from sys.databases

/* sp_help_log_shipping_secondary_databaseの結果を入れる */
CREATE TABLE #TMP (
secondary_id nchar(128)
,primary_server nchar(128)
,primary_database nchar(128)
,backup_source_directory nchar(128)
,backup_destination_directory nchar(128)
,file_retention_period int
,copy_job_id nchar(128)
,restore_job_id nchar(128)
,monitor_server nchar(128)
,monitor_server_security_mode nchar(128)
,secondary_database nchar(128)
,restore_delay int
,restore_all int
,restore_mode int
,disconnect_users int
,block_size int
,buffer_count int
,max_transfer_size int
,restore_threshold int
,threshold_alert int
,threshold_alert_enabled int
,last_copied_file nchar(128)
,last_copied_date datetime2
,last_copied_date_utc datetime2
,last_restored_file nchar(128)
,last_restored_date datetime2
,last_restored_date_utc datetime2
,history_retention_period int
,last_restored_latency int
)

/* ループ処理開始 */
OPEN DBnameList;
FETCH NEXT FROM DBnameList INTO @dbname

WHILE (@@FETCH_STATUS = 0)
BEGIN
print @dbname
INSERT INTO #TMP EXEC sp_help_log_shipping_secondary_database @dbname
FETCH NEXT FROM DBnameList INTO @dbname
END

CLOSE DBnameList
DEALLOCATE DBnameList

/* 一覧出力 */

select
secondary_database as ‘DB名’
,last_copied_date as ‘最終コピー時刻’
,last_restored_date as ‘最終リストア時刻’
,last_restored_file as ‘最終リストアファイル名’
from #TMP

/* TMPDB削除 */
DROP TABLE #TMP

[/code]

Slave側のDBをログシッピングのStandbyモードで動かしている場合、
SSMSのユーザーセッションが残っているとリストアが失敗する事がある。

会社のとあるシステムでは、15分毎にLSCOPYを行い、深夜に1回、LSRESTOREジョブが動いているが、その良く失敗する。
酷い時は週5回は失敗して、「データが更新されてない！」と利用部門からクレームが来て手動でジョブを実行していた。

接続を切って帰ってくれればいいのだが、何度言ってもダメなので、
LSRESTOREジョブにステップを追加して、当該DBへのアクセスを全てkillする事にした。

その時に動いている処理があるのかもしれないが、ムシャクシャして書いた。

[code]
SET NOCOUNT ON
CREATE TABLE #sp_who (
spid smallint
,ecid smallint
,status nchar(30)
,loginame nchar(128)
,hostname nchar(128)
,blk char(5)
,dbname nchar(128)
,cmd nchar(16)
,request_id int)

INSERT INTO #sp_who EXEC sp_who

DECLARE CUR_SPID CURSOR FOR select spid from #sp_who where dbname = ‘DB名’

DECLARE @SPID nchar(5)

OPEN CUR_SPID

FETCH NEXT FROM CUR_SPID INTO @SPID

WHILE (@@FETCH_STATUS = 0)

BEGIN
DECLARE @command nchar(128)
SET @command = N’kill ‘ + @SPID

EXEC sp_executesql @command

FETCH NEXT FROM CUR_SPID INTO @SPID

END

CLOSE CUR_SPID
DEALLOCATE CUR_SPID
DROP TABLE #sp_who
[/code]

これで、翌朝にドタバタ＆イライラしなくても済みそう。