等待快照代理状态完成的 T-SQL 脚本

2021-09-10 00:00:00 tsql sql-server

我正在尝试构建一个 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

相关文章