TSQL事务内日志记录

Question

我正试图在事务内部写入日志文件，以便即使事务回滚，日志仍然存在。

--开始代码

begin tran

将[something]插入到dbo.logtable中

[[主要代码在此]]

rollback

commit

--结束代码

你可以说在事务开始前就做日志，但这并不容易，因为事务在运行该S-Proc之前就已经开始了（即该代码是更大事务的一部分）。

因此，简而言之，有没有一种方法可以在事务内编写不属于事务的特殊语句。我希望我的问题讲得清楚。

- Arvid

---

请查看https://dev59.com/C2865IYBdhLWcg3wnf9t。 - Martin Klinke

Answer 1

使用表变量(@temp)来保存日志信息。表变量可以在事务回滚时保持不变。

请参考这篇文章。

- BradC

---

正是我要建议的。我已经成功地使用过这个方法很多次了。 - HLGEM

问题在于，如果发生其他任何事情，比如连接超时或执行超时，你将没有日志可查。 - Talon

Answer 2

我有两种方法来实现这个，具体取决于我的需求。两种方法都使用变量，这些变量在回滚后保留它们的值。

1) 创建一个DECLARE @Log varchar(max)变量并使用它：@SET @Log=ISNULL(@Log+'; ','')+'Your new log info here'。随着事务的进行不断追加信息到这个变量中。我会在提交或回滚之后将其插入到日志中，只有在出现错误（在CATCH`块中）或者需要调试问题时才会将@Log值插入真正的日志表中。

2) 创建一个DECLARE @LogTable table (RowID int identity(1,1) primary key, RowValue varchar(5000)。在事务进行过程中逐步将信息插入到这个表中。我喜欢使用OUTPUT子句将事务中实际使用的行的IDs（和其他带有消息的列，例如“DELETE item 1234”）插入到此表中。我会在提交或回滚之后将此表插入到实际的日志表中。

- KM.

---

这两个都不错，只是我没有轻松访问交易部分。基本上，我正在尝试调试一个用C#编写的程序。 - Arvid

（让我们完成这个吧 :-) C#代码开始一个事务，然后调用一个存储过程，在最后要么提交事务，要么回滚事务。 我只能轻松地访问到存储过程，希望我能在那里添加一些调试代码，即使回滚了事务也能生效。 - Arvid

你可以尝试使用xp_cmdshell(Transact-SQL)（但这可能会被关闭以防止安全风险）来在你的过程中包含以下内容：EXEC xp_cmdshell echo 'your message here' >>log.txt 以将其写入文件。你也可以尝试使用一个输出参数@ErrorInfo varchar(max)`，通过它将错误信息传回C#程序，以便记录日志。 - KM.

Answer 3

如果父事务回滚，则日志数据也会回滚 - SQL服务器不支持正确的嵌套事务。一种可能性是使用CLR存储过程进行记录。这可以在事务之外打开其自己的数据库连接，并输入和提交日志数据。

- ConcernedOfTunbridgeWells

---

使用CLR日志记录过程是最佳解决方案之一。如果能够以某种方式自动获取调用存储过程的实际参数值，那就太好了...我们总是可以梦想。 - Pavel Urbančík

Answer 4

将日志输出到表中，使用时间延迟，并使用WITH(NOLOCK)查看。

看起来@arvid想要调试存储过程的操作，并且能够更改存储过程。

c#代码启动事务，然后调用s-proc，在结束时提交或回滚事务。我只能轻松访问s-proc

我有类似的情况。因此，我修改了存储过程以将所需的输出记录到表中。然后在存储过程的末尾放置了一个时间延迟。

WAITFOR DELAY '00:00:12';  -- 12 second delay, adjust as desired

在另一个SSMS窗口中，使用未提交读隔离级别（下面的“WITH(NOLOCK)”）快速读取表

SELECT * FROM dbo.NicksLogTable WITH(NOLOCK);

如果你需要一个永久记录日志的解决方案（包括事务回滚的位置），那么这并不是你想要的解决方案。但从我的目的来说，能够以临时方式调试代码非常合适，尤其当链接服务器、xp_cmdshell 和创建文件表都被禁用的时候 :-(

对于顶起一个12年前的帖子感到抱歉，但在那段时间内，微软没有实现嵌套事务或自主事务，这也值得批评。

- Kiwi Nick

Answer 5

如果您想模拟嵌套事务行为，可以使用命名事务：

begin transaction a

create table #a (i  int)

select * from #a
save transaction b

create table #b (i  int)
select * from #a
select * from #b

rollback transaction b

select * from #a
rollback transaction a

在SQL Server中，如果您想要一个“子事务”，您应该使用save transaction xxxx，它的作用类似于Oracle的检查点。

- Ed Green

---

这个答案根本没有回答问题。提问者并不是在问子事务，相反他想要将某些操作排除在事务环境之外，并直接提交到数据库中。 - Tipx

我的回复是针对上面的评论，我认为我已经相当简洁地回答了它。 - Ed Green