最佳实践 - 存储过程日志记录

Question

如果您有一个长时间运行的存储过程，您是否会记录其操作，还是只等待以下消息：

"命令已成功完成。"

我认为，在这个主题上可能有很多解决方案，但是否存在任何最佳实践 - 经常使用的简单解决方案？

编辑

我在这个主题上找到了一个有趣的链接

http://weblogs.sqlteam.com/brettk/archive/2006/09/21/12391.aspx

文章描述了使用日志表，但存在一个问题

日志记录过程必须在任何事务之外执行

由于我使用的游标和在每一行上插入一行到该表格中，所以我无法在外部调用该插入操作。

有什么想法吗？

编辑2

挖掘中..

在SQL Server中有一个xp_logevent。你试过了吗？

那么SQL Server Profiler呢？

还有一个为存储过程创建日志文件

- hgulyan

---

任何超过几个选择和更新的存储过程。例如，如果您使用游标并且需要监视进度。我的问题有什么问题吗？ - hgulyan

Answer 1

您是如何调用存储过程的？如果是通过管理工具，则可以按以下方式轻松打印出消息。

RAISERROR ('Some debugging info', 0, 1) WITH NOWAIT

与使用PRINT语句相比，这种方法更可取，因为消息会立即显示。通过为Connection.InfoMessage事件添加处理程序，也可以在ADO.NET中捕获这些消息。

我看到您已经将SQL Profiler列为可能的选择。您可能会有兴趣了解，您可以记录自己的用户可配置事件，这些事件可以在SQL Profiler中查看。

- Martin Smith

---

我正在管理工作室中调用sp，但问题很普遍，因此如果有一个好的日志记录方式，也可以在应用程序中使用。 我不需要RAISERROR，因为我需要记录过程，而不是错误。 PRINT可能是一个选项，但如果sp每秒打印1000行呢？ 我已经尝试过SQL Profiler。这是一个不错的选择，但它并不是真正的日志记录。我想它更像是监控，并且Profiler不是一个通用的解决方案。 也许解决方案是结合所有在这里提到的内容。 - hgulyan

不要被名称所迷惑，RAISERROR并不仅限于错误。任何严重程度小于10的情况都可以用于信息消息。无论你使用哪种日志框架，你都需要设计它以确保适当数量的信息被记录下来。 - Martin Smith

不会停止SP，因为严重级别较低。详情请参阅http://msdn.microsoft.com/en-us/library/ms178592.aspx。 - Martin Smith

使用 Print 命令，直到存储过程结束你才能收到消息（我想是这样的 - 或者至少你得等待一段时间）。而 RAISERROR ... WITH NOWAIT 则可以避免这个问题。实际上，根据这个链接所说，Print 语句只是 "严重级别为0的消息"。http://www.sommarskog.se/error-handling-I.html - Martin Smith

打印输出是有缓冲的，因此您不会立即看到结果，而且它们可能与错误流以不同的顺序出现，这两者都使用单独的缓冲区。由于raiserror使用单独的缓冲区（可以强制使用每个语句的“WITH NOWAIT”指令来刷新），所以对于大多数日志记录目的来说，这是更好的选择。 - Joel Coehoorn

Answer 2

为了了解每个操作需要多长时间以及上一个操作修改了多少行，我会在每个条目中添加当前日期+时间和最后一行计数。 我使用以下步骤：

CREATE PROCEDURE dbo.[Log]
    @Message NVARCHAR(512),
    @RowCount INT = null OUTPUT,
    @Delimiter NCHAR(1) = N' ',
    @PadChar NCHAR(1) = N'-'
AS
    BEGIN
        SET @RowCount = @@ROWCOUNT;

        DECLARE @LogDate AS NVARCHAR(50);
        DECLARE @RowCountPadded AS NCHAR(8);

        SET @LogDate = CONVERT(NVARCHAR(50),GETDATE(),121);
        SELECT @RowCountPadded = CASE @RowCount WHEN 0 THEN REPLICATE(@PadChar,8) ELSE REPLACE(STR(@RowCount, 8), SPACE(1), @PadChar) END; 

        SET @Message = @LogDate + @Delimiter + @RowCountPadded + @Delimiter + @Message;
        RAISERROR (@Message, 0, 1) WITH NOWAIT;
    END

因此，在您的程序中，添加如下日志输出：

EXEC dbo.[Log] 'the message';

它会产生这个：

2012-12-28 11:28:25.197 -------- the message

如果您之前执行了某些操作，您将看到破折号所在的行计数。如果您需要行计数以进行其他操作（例如记录到表中），则可以从过程中作为输出参数获取它。

更新：如果您想创建一次并在任何地方使用此过程，请使用此gist。

-- 已删除的行 --

- dalenewman

Answer 3

通常我们使用日志表并在事务处理方面小心谨慎。我们基本上避免涉及到超出SQL Server范围的任何事情（例如写入文件系统、调用外部服务、.NET程序集等）。

我们也尽量避免使用游标 -- 你的存储过程是否因为效率低下而运行缓慢？

- Joe

---

你能具体说明一下吗？ 如果在事务之外无法插入日志表怎么办？我知道游标不被推荐使用，但这是一个一次性的脚本，没有其他方法，所以这是需要使用游标的情况。 - hgulyan

Answer 4

我使用这个过程。

CREATE PROCEDURE dbo.PrintLog (
    @Msg VARCHAR(2048)
    , @Option VARCHAR(100) = ''
    , @Separator VARCHAR(10) = '-'
    )
/*
@Option is a string containing possible values as B,A,D,T
if you want to print separator before message, include B
if you want to print separator after message, include A
if you want to print date, include D
if you want to print time, include T
Sample: 'BAD'

The order of characters does not matter. it is not case sensitive

Usage:
    exec dbo.PrintLog 'Timed Log', 'T'
    exec dbo.PrintLog 'Dated Log', 'D'
    exec dbo.PrintLog 'With Separator and Time', 'BT', '><'
    exec dbo.PrintLog 'With Separator and Date', 'BAD', '*'
    exec dbo.PrintLog 'With Separator and DateTime', 'BADT', 'x'
*/
AS
BEGIN
    declare @tempStr varchar(100)
    set @tempStr = replicate(@Separator, 50)
    IF charindex('B', upper(@Option)) > 0
        raiserror (@tempStr, 10, 1) with nowait

    DECLARE @prompt VARCHAR(max) = ''

    IF charindex('D', upper(@Option)) > 0
        SET @prompt = convert(VARCHAR, SysDatetime(), 101) + ' '

    IF charindex('T', upper(@Option)) > 0
        SET @prompt = @prompt + convert(VARCHAR, SysDatetime(), 108) + ' '
    SET @prompt = @prompt + @Msg

    raiserror (@prompt, 10, 1) with nowait

    set @tempStr = replicate(@Separator, 50)
    IF charindex('A', upper(@Option)) > 0
        raiserror (@tempStr, 10, 1) with nowait

    RETURN
END

GO

用法

    exec dbo.PrintLog 'Date and Timed Log', 'DT'
    exec dbo.PrintLog 'Dated Log', 'D'
    exec dbo.PrintLog 'With Separator and Time', 'BT', '><'
    exec dbo.PrintLog 'With Separator and Date', 'BAD', '*'
    exec dbo.PrintLog 'With Separator and DateTime', 'BADT', 'x'

您也可以将参数默认值更改为所需的值。

- FLICKER