問題描述

我正在嘗試構建一個 SQL 腳本，以便在刪除復制之前等待快照代理完成創建快照(如果正在進行)

I'm trying to build a SQL script to wait till snapshot agent finish create snapshot (if it is in progress) before dropping the replication

當前狀態:我們有一些 SQL 腳本來禁用復制(它們作為 VSTS 發布管道的一部分運行).有時，可能會生成快照.如果在執行快照時禁用復制，腳本將失敗.

Current status: We have some SQL scripts to disable replication (they are run as part of VSTS release pipelines). Sometimes, there might be a snapshot being generated. If replication is being disabled while a snapshot is in progress, script fails.

我正在使用下面的腳本來檢查快照代理的狀態

I'm using below script to check the status of snapshot agent

選擇狀態從 dbo.MSReplication_monitordataWHERE 發布 = 'PublicationName' 和 agent_type = 1) = 3

SELECT status FROM dbo.MSReplication_monitordata WHERE publication = 'PublicationName' and agent_type = 1) = 3

最終目標:

我需要幫助以實現以下目標:

I want help to achieve the following:

腳本檢查快照代理是否正在運行.如果它正在運行，它將等待它完成(生成快照)，然后執行操作(刪除復制).

script check if snapshot agent if running or not. If it is running, it will wait till it is completed (snapshot generated), then do the action (drop replication).

我已經有了刪除復制的腳本，我需要幫助的是處理這種情況的邏輯.

I already have the scripts for dropping replication, what I need help about is the logic to handle this scenario.

我的問題不是來自 如何檢查復制快照代理狀態?

我需要有關邏輯的幫助以創建腳本以等待快照代理空閑(如果它正在運行).我不知道如何在 t-sql 中做到這一點.

i need help with the logic to create a script to wait till snapshot agent is idle (if it is running). I don't know how to do it in t-sql.

我相信 (waitfor) 或 (while) 會有所幫助，但我不知道如何使用它們.

I believe (waitfor) or (while) will help, but I don't know how to use them.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/waitfor-transact-sql?view=sql-server-2017https://docs.microsoft.com/en-us/sql/t-sql/language-elements/while-transact-sql?view=sql-server-2017

推薦答案

我能夠使用 while 循環使用以下內容:

I was able to use while loop using below:

    use [distribution];
    declare @status int = 2

     select @status = status
     FROM dbo.MSReplication_monitordata
     WHERE publication = 'PublicationName' and agent_type = 1

     while @status = 3
     begin
     WAITFOR DELAY '00:00:03'
     select @status = status
     FROM dbo.MSReplication_monitordata
     WHERE publication = 'Publication.Name' and agent_type = 1
     end

這篇關于等待快照代理狀態完成的 T-SQL 腳本的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！