{"id":99,"date":"2015-01-14T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/how-to-log-errors-with-t-sql-trycatch"},"modified":"2026-03-18T21:51:32","modified_gmt":"2026-03-18T21:51:32","slug":"how-to-log-errors-with-t-sql-trycatch","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2015\/01\/14\/how-to-log-errors-with-t-sql-trycatch\/","title":{"rendered":"How to log errors with T-SQL try\/catch"},"content":{"rendered":"

Today a co-worker of mine (Brad) asked a question regarding the usage of T-SQL\u2019s Try-Catch<\/a> block. Brad wanted to write to a table every time a stored procedure failed so that it was easy to recover the error messages and track issues. His Try-Catch block looked something like this.<\/p>\n

BEGIN TRY\n\n
BEGIN TRANSACTION<\/p>\n\n
PRINT 'do work'<\/p>\n\n
COMMIT<\/p>\n\n
END TRY\nBEGIN CATCH<\/p>\n\n
DECLARE @errMsg VARCHAR(500) = ERROR_MESSAGE()\n, @errState INT = ERROR_STATE()\n, @errSeverity int = ERROR_SEVERITY()<\/p>\n\n
INSERT INTO dbo.Failures (errorMsg)\nVALUES (@errMsg)<\/p>\n\n
RAISERROR(@errMsg, @errSeverity, @errState);<\/p>\n\n
END CATCH<\/code><\/pre>\n<\/p>\n
The intention was that the RAISERROR command would be similar to a re-throw and roll back the transaction after writing to the table. Quick tangent, SQL Server 2012\u2019s new THROW command is preferred, in my opinion, over RAISERROR. Check out more on THROW here<\/a>. But I digress. The problem with the above code was that we knew deadlocks were occurring but there was never a single insert into the dbo.Failures table.<\/p>\n
Before announcing the solution, let\u2019s set up\u00a0a test for demonstration purposes.<\/p>\n
The test<\/h2>\n
Setup<\/h3>\n
This quick script will create our table with data and our table which will be used to log errors.<\/p>\n
CREATE TABLE dbo.ToBeBlocked (id TINYINT NOT NULL)\nCREATE TABLE dbo.Failures (id TINYINT IDENTITY(1,1) NOT NULL\n, errorMsg VARCHAR(500) NOT NULL)\nGO\nINSERT INTO dbo.ToBeBlocked (id) VALUES (1);\nINSERT INTO dbo.ToBeBlocked (id) VALUES (2);\nGO 10<\/code><\/pre>\n
Script 1<\/h3>\n
In this script we have the Try-Catch block seen above. I\u2019ve added a select and an update to help us setup a deadlock condition. The DEADLOCK_PRIORITY and REPEATABLE READ isolation level ensures that we take a lock on all rows in the table and try to update them after another script has stepped in the way. The WAITFOR DELAY is used to give time for you to run Script 2 in another query window before the update fires. Finally, it will query the Failures table to see if we inserted any rows.<\/p>\n
SET DEADLOCK_PRIORITY LOW\n\n
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ<\/p>\n\n
BEGIN TRY<\/p>\n\n
BEGIN TRANSACTION<\/p>\n\n
SELECT id FROM dbo.ToBeBlocked;<\/p>\n\n
-- Give us a chance to run the other script\n-- to cause a deadlock\nWAITFOR DELAY '00:00:05';<\/p>\n\n
UPDATE dbo.ToBeBlocked\nSET id = 3\nWHERE id = 2<\/p>\n\n
COMMIT<\/p>\n\n
END TRY\nBEGIN CATCH<\/p>\n\n
DECLARE @errMsg VARCHAR(500) = ERROR_MESSAGE()\n, @errState INT = ERROR_STATE()\n, @errSeverity int = ERROR_SEVERITY()<\/p>\n\n
INSERT INTO dbo.Failures (errorMsg)\nVALUES (@errMsg)<\/p>\n\n
RAISERROR(@errMsg, @errSeverity, @errState);<\/p>\n\n
END CATCH<\/p>\n\n
SELECT * FROM dbo.Failures<\/code><\/pre>\n<\/p>\n
Script 2<\/h3>\n
This script will be executed during the 5 sec delay from running Script 1. This will cause our deadlock and become the victor because Script 1\u2019s DEADLOCK_PRIORITY is set to LOW, making it the victim.<\/p>\n
BEGIN TRANSACTION\n\n
UPDATE dbo.ToBeBlocked\nSET id = 2\nWHERE id = 2;<\/p>\n\n
COMMIT<\/code><\/pre>\n<\/p>\n
The results<\/h2>\n
By executing Script 1 and causing a deadlock with Script 2, we receive these messages.<\/p>\n
> (20 row(s) affected)
\n>
\n>
\n> (0 row(s) affected)
\n>
\n> Msg 3930, Level 16, State 1, Line 28
\n>
\n>  The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
\n>
\n>  Msg 50000, Level 13, State 45, Line 31
\n>
\n>  Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
\n>
\n>
\n> (0 row(s) affected)
\n>
\n> Msg 3998, Level 16, State 1, Line 1
\n>
\n>  Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.<\/p>\n
We can also see that no records were inserted into the Failures table.<\/p>\n
\"\"<\/a><\/p>\n
The solution<\/h2>\n
The reason that Brad was left without any log records was because the INSERT INTO dbo.Failures was still part of the first, uncommitted, transaction. Since that transaction was deadlocked and could not be committed the, \u201cUncommittable transaction is detected,\u201d error was thrown and everything was rolled back, including the Failures record. The solution to this problem is to add a ROLLBACK statement before the INSERT. This will rollback the transaction which is in an uncommittable state and then allow the Failures record to be INSERTed as a separate transaction.<\/p>\n
BEGIN CATCH\n\n
DECLARE @errMsg VARCHAR(500) = ERROR_MESSAGE()\n, @errState INT = ERROR_STATE()\n, @errSeverity int = ERROR_SEVERITY()<\/p>\n\n
ROLLBACK --Solution<\/p>\n\n
INSERT INTO dbo.Failures (errorMsg)\nVALUES (@errMsg)<\/p>\n\n
RAISERROR(@errMsg, @errSeverity, @errState);<\/p>\n\n
END CATCH<\/code><\/pre>\n<\/p>\n
New results<\/h3>\n
> (20 row(s) affected)
\n>
\n>
\n> (0 row(s) affected)
\n>
\n>
\n> (1 row(s) affected)
\n>
\n> Msg 50000, Level 13, State 45, Line 33
\n>
\n> Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
\n>
\n>
\n> (1 row(s) affected)<\/p>\n
\"\"<\/a><\/p>\n
\n
\"\"Written by Derik Hammer of SQL Hammer<\/a><\/strong><\/p>\n
Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability<\/a>, disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n
Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n
Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource<\/a>.<\/p>\n
For more information, visit http:\/\/www.sqlhammer.com<\/a>. Follow Derik on Twitter<\/a> for SQL tips and chat<\/strong><\/p>\n
\n
\"\"Written by Derik Hammer of SQL Hammer<\/a><\/strong><\/p>\n
Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability<\/a>, disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n
Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n
Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource<\/a>.<\/p>\n
For more information, visit http:\/\/www.sqlhammer.com<\/a>. Follow Derik on Twitter<\/a> for SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
Today a co-worker of mine (Brad) asked a question regarding the usage of T-SQL\u2019s Try-Catch block. Brad wanted to write to a table every time a stored procedure failed so that it was easy to recover the error messages and track issues. His Try-Catch block looked something like this. BEGIN TRY BEGIN TRANSACTION PRINT ‘do […]<\/p>\n","protected":false},"author":1,"featured_media":571,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-99","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/99","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=99"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/99\/revisions"}],"predecessor-version":[{"id":361,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/99\/revisions\/361"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/571"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=99"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=99"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=99"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}