如何查看SQL存储过程的运行进度? [英] How to see progress of running SQL stored procedures?

问题描述

考虑下面的存储过程..

Consider the following stored procedure..

CREATE PROCEDURE SlowCleanUp (@MaxDate DATETIME)
AS
BEGIN
    PRINT 'Deleting old data Part 1/3...'
    DELETE FROM HugeTable1 where SaveDate < @MaxDate

    PRINT 'Deleting old data Part 2/3...'
    DELETE FROM HugeTable2 where SaveDate < @MaxDate

    PRINT 'Deleting old data Part 3/3...'
    DELETE FROM HugeTable3 where SaveDate < @MaxDate

    PRINT 'Deleting old data COMPLETED.'
END

假设每个删除语句都需要很长时间才能删除，但我喜欢在 SQL Management Studio 中运行它时查看此存储过程的进度.换句话说，我喜欢查看 PRINT 语句的输出以了解我在任何给定时间的位置.但是，似乎我只能在 ENTIRE 运行结束时看到 PRINT 输出.有没有办法让我可以实时看到 PRINT 输出?如果没有，有没有其他方法可以查看正在运行的存储过程的进度?

Let's say that each delete statement take a long time to delete, but I like to see the progress of this stored procedure when I'm running it in SQL Management Studio. In other words, I like to see the the output of the PRINT statements to see where I'm at any given time. However, it seems that I can only see the PRINT outputs at the end of the ENTIRE run. Is there a way to make it so that I can see the PRINT outputs at real time? If not, is there any other way I can see the progress of a running stored procedure?

推荐答案

如果您使用 RAISERROR 严重性为 10 或更低，并使用 NOWAIT 选项，它将立即向客户端发送信息性消息:

If you use RAISERROR with a severity of 10 or less, and use the NOWAIT option, it will send an informational message to the client immediately:

RAISERROR ('Deleting old data Part 1/3' , 0, 1) WITH NOWAIT

这篇关于如何查看SQL存储过程的运行进度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！