We were once asked in class what TRY/CATCH doesn’t handle besides object existence errors.
It’s well documented in Books Online (BOL). If you’re like me, then tl;dr. Are we even calling it Books Online these days? I still say “bookmark lookup” instead of “key lookup”. I suppose I’ll be saying Books Online for quite some time too. At least these days it really is online.
Here’s a shortened version:
- Warnings or informational messages that have a severity of 10 or lower
- Errors that have a severity of 20 or higher that stop the session
- Attentions
- When a session is KILLed
In addition to the above, there are some errors that are not handled when it occurs at the same level as the TRY/CATCH:
- Compilation errors
- Errors that occur during statement-level recompilation (object existence errors fall into this category)
In all of these cases, be sure that the calling application/object handles the error.
My favorite usage of a TRY/CATCH construct is when you are purging data and retrying the DELETE due to a deadlock:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
CREATE PROC usp_Table2_Purge (@purgedate datetime, @batch int = 5000) AS SET NOCOUNT ON; DECLARE @rc int = 1, @retries tinyint = 0, @maxretries tinyint = 10, @errormessage nvarchar(4000), @errorseverity int, @errorstate int; WHILE @rc <> 0 AND @retries <= @maxRetries BEGIN; BEGIN TRY; BEGIN TRAN; DELETE TOP (@batch) Table2 FROM Table2 JOIN Table1 ON Table2.Table1Id = Table1.Table1Id WHERE Table1.CreatedDate < @purgeDate; SELECT @rc = @@ROWCOUNT; IF XACT_STATE() = 1 COMMIT TRANSACTION; END TRY BEGIN CATCH; IF XACT_STATE() <> 0 ROLLBACK TRANSACTION; IF ERROR_NUMBER() = 1205 AND @retries < @maxRetries -- 1205 is the deadlock error BEGIN; SET @retries = @retries + 1; WAITFOR DELAY '00:00:10'; --pause to hopefully avoid deadlock next batch END; ELSE -- some other error or done retrying BEGIN; SELECT @errormessage = ERROR_MESSAGE() + '. Error in line ' + CAST(ERROR_LINE() AS varchar(1000)) + ' of procedure ' + ERROR_PROCEDURE(), @errorseverity = ERROR_SEVERITY(), @errorstate = ERROR_STATE(); RAISERROR (@errormessage, @errorseverity, @errorstate); RETURN; END; END CATCH; END; RETURN; |
Brent says: when you’re looking at TRY/CATCH, it’s also good to remember the traditional guidance about transactions: keep ’em short. Do your selects, variable settings, background info gathering, etc before the transaction starts.
25 Comments. Leave new
Thank you, Tara.
This is very important especially “Compilation errors” case.
I’d like to add more comment.
Typically, we can represent the case with 207 or 208 error#, Invalid column/object name.
Here is one simple example.
Below statement will be ended with transaction OPEN state.
BEGIN TRAN
BEGIN TRY
SELECT * FROM NoExistTable
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0
ROLLBACK TRAN
END CATCH
GO
SELECT @@TRANCOUNT
It could happen more frequently in Dynamic SQL statement.
We can resolve the issue with “SET XACT_ABORT ON”.
Thanks,
Hey Simon…
What if XACT_STATE = -1 (error).
You aren’t going to get any resolution if you’re only looking at the successful (1) transactions.
I believe 0 means nothing happened.
XACT_STATE definitely needs to check every transaction for error handling.
The example I gave one is a simple case to explain compile error issue.
When you test it, it’s not hit the “Catch” block at all.
It just stops at the “SELECT * FROM NoExistTable”.
So, any statements in Catch block aren’t executed even you check XACT_STATE.
This case only the resolution is XACT_ABORT setting.
I just want to explain the specific case of “Compilation errors” which is not able to handle by Catch block.
Simon
Compilation errors can’t be handle if they are in the same scope. If such “errors” come from a “subscope”
EXEC(‘SELECT * FROM dbo.NonExistingTable’) then TRY/CATCH will intercept them:
BEGIN TRY
EXEC(‘SELECT * FROM dfagsfdh’)
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
END CATCH
If such “errors” come from a
...then that error can be intercepted by CATCH block.We found a tricky limitation in try/catch.
It seems that in the catch block you can refer to the ERROR_XXXXX() functions in order to get details about the latest exception occurred only. I mean, If I’m getting two errors with a single statement, in the catch block I will always get information of the latest error only.
For example, if you set a database to single_user and then try to do a backup of it, you will get two errors, one saying that the db is in single user, and one saying the backup is terminating abnormally.
Wrapping the code in a try/catch will result in getting only the error of backup terminating abnormally. No way to get the real reason for that, that is db in single user.
Ok you can use throw statement to get all the exceptions, but this will release the control in the code, losing any advantage of the catch routine.
What do you think about it?
Dumb question – why would you put a database in single_user mode to do a backup?
It was related to a migration/scale out process, when we need to move a database from an instance to another. Setting single_user will make sure no further data change will be made to the db. The db would have been dropped after the backup, and then restored on another instance.
That was a legitimate question. Regardless of the reason _why_ the database would be in single_user mode, it illustrates a real problem where a relevant reason for failure is being hidden. I actually see Davide’s example as a very good way to simulate the issue, in the same manner as I sometimes deliberately put a “1/0” in a VIEW to provoke an error and test my error handling code.
Just like Davide, I ran into a similar issue where I was performing a backup on a server where the backup path had been altered. The backup operation was failing because of an underlying NTFS permission issue. But the calling procedure’s TRY/CATCH block which was responsible for logging errors would only record “BACKUP DATABASE is terminating abnormally” as an ERROR_MESSAGE(), which didn’t provide much insight into the source of the problem.
The concealed underlying error message, the only one that really mattered, was the following:
Cannot open backup device ‘(filepath\name)’. Operating system error 5(Access is denied.).
That is the error message that would have helped me quickly understand the nature of the problem. I could enumerate dozens of random circumstances which could cause a backup to fail… none of which will be visible in the CATCH block, meaning none will be properly logged, nor properly handled.
Relevant backup error messages are being concealed when using ERROR_MESSAGE() in a TRY/CATCH block. This is a real problem, and I am still looking for a proper way around it. Until then, whenever I’ll run into a problem, I’ll need to repeat the backup command manually in SSMS and hope for the failing condition to be consistent enough for me to be able to reproduce the problem.
Tara, thank you for this article
There also look to be some catch with regards to using linked server queries in the TRY…CATCH construct
For example, the exception below is not trappable inside the TRY…CATCH if a linked server is involved:
Error: 213, Severity: 16, State: 7
Column name or number of supplied values does not match table definition.
If a linked server is not involved though, the same exception is perfectly trappable
Below are the examples of the non-trappable “Column name or number of supplied values does not match table definition.” exceptions:
(none of the four ways listed below traps the exception)
DECLARE @exec_str nvarchar(max);
BEGIN TRY
/* option 1 */
INSERT my_local_db.dbo.my_local_table( field_1, field_2, field_3)
EXEC MY_LINKED_SERVER.the_db.dbo.the_proc ‘20140718’ ;
/* option 2 */
–INSERT my_local_db.dbo.my_local_table( field_1, field_2, field_3)
— EXEC( ‘ EXEC MY_LINKED_SERVER.the_db.dbo.the_proc ”20140718” ‘);
/* option 3 */
–SET @exec_str = ‘
–INSERT my_local_db.dbo.my_local_table( field_1, field_2, field_3)
— EXEC MY_LINKED_SERVER.the_db.dbo.the_proc ”20140718” ;
–‘;
–EXEC sp_executesql @exec_str;
/* option 4 */
–SET @exec_str = ‘
–INSERT my_local_db.dbo.my_local_table( field_1, field_2, field_3)
— EXEC( ” EXEC MY_LINKED_SERVER.the_db.dbo.the_proc ””20140718”” ”);
–‘;
–EXEC sp_executesql @exec_str;
END TRY
BEGIN CATCH
SELECT ‘CATCH’;
SELECT ERROR_MESSAGE();
END CATCH
The reason this behavior has caught my attention originally, is that there looks to be something special about this particular exception: there are other “more severe” situations, like missing [the_proc] procedure altogether, that are still perfectly trappable in the TRY..CATCH
yeah and connection issues, at least with linked severs, are also not handles by try/catch… Another catch! *drumroll*
Hi Tara/Brent, Just wonder if the Try/Catch block gets to execute during an automatic failover of SQL AlwaysOn environment. One of our application has a loop to do insert/delete and after an automatic failover (AAG), the application hung. I thought the SQL that was executed during the failover may have interrupted and rolled back, but how to handle or capture this exception in an alwayson environment?
Please shed some light to this.
Thanks & regards,
Irene
It’ll get rolled back during crash recovery. You’d have to handle the exception from the application as the connection is now severed. If the application hung due to a failover, I believe the issue is in the application. Do a test in a test environment. Doesn’t even have to be an AG, just stop the service while the application is trying to run a query and then see what happens.
Hi, I’m curious why you need to check XACT_STATE in the try block before you commit? If there has been an issue would it not have taken it into the catch being the try should always be safe to commit?
Gavin, it’s explained in the examples section of the XACT_STATE doc: https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql?view=sql-server-2017. “The following example uses XACT_STATE in the CATCH block of a TRY…CATCH construct to determine whether to commit or roll back a transaction. Because SET XACT_ABORT is ON, the constraint violation error causes the transaction to enter an uncommittable state.” If there isn’t an active transaction, the rollback will throw an error.
I can see why you’d check in the catch block but I can’t see why the try block. Is there a situation where a transaction can go into an uncommittable state but not trip into the catch block?
Just coding safeguards. It’s in the template I use for try/catch transactions.
I realise this is just a sample so probably not really a valid point but… By having that xact_state check in the try block if somehow something does put the transaction in an uncommittable state and not trigger the catch you’ll handle it in the try block by not running the commit and essentially return a success back to the calling code. I’d almost rather the try just ran commit triggering it to fall into the catch block if there is a problem where the error is then handled and probably passed on to the calling code. Potentially I’m missing something here though.
Anyway thanks for the posts, I’ve learnt a lot from this blog.
Honestly I have no idea, but I get what you’re saying. I’m a production DBA. That code is just from a template that I grabbed somewhere a long time ago when try/catch was added to TSQL. I don’t usually write code anyway. lol
Can someone confirm the following setting has to be present in order to use XACT_STATE( )?
SET XACT_ABORT ON;
And if so, should it be set once globally? Or is it per Connection? In other words, should I include it as first line in my Stored Procedure?
Grif – for random unrelated questions, check out a Q&A site like https://dba.stackexchange.com.
Any advice for handling when a table is changed out from under you, like a field is added, and your stored proc fails to INSERT because it’s missing a field? Apparently this is considered a “compile-time” error, and can’t be caught.
create table #x (x int);
begin try
INSERT INTO #X values(1, 2);
end try
begin catch
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
end catch
drop table #x
What I get is:
Msg 213, Level 16, State 1, Line 4
Column name or number of supplied values does not match table definition.
This is not caught, but bubbles up to the SQL Agent job running the proc, which we do not monitor.
I struggle to identify what category this type of error falls into. I have a script to “Kill” all jobs that are running and the TRY CATCH does not catch this error. The job exists, obviously, so it doesn’t fall into the “Object Existence Error” or the “Compilation Error”. Does anyone have some insight to this?
Msg 22022, Level 16, State 1, Line 17
SQLServerAgent Error: Request to stop job (from User HappyGilmore) refused because the job is not currently running.
Sean – for general questions, head over to a Q&A site like https://dba.stackexchange.com or https://sqlservercentral.com.
Brent – Long time follower, I LOVE this example. I will be publishing it to all of our developers as the *correct* way to handle deadlocks within code.