You can enhance error handling with Extended Events to overcome the TRY...CATCH shortcoming you are experiencing. The design pattern involves these steps:
- Create and start an Extended Events Session: it will capture
sqlserver.error_reported events, filtered primarily by SPID.
- Execute a statement in a
TRY block.
- Within (or after) the
CATCH block, read the XEvents session data.
- Use the available data to respond to the error(s) as appropriate.
- Stop and drop the XEvents session.
Here's an example that can be run manually in a single batch from SSMS. Just make sure to replace all occurrences of "2016" in the script with your SPID.
CREATE EVENT SESSION [Error Handling Session(2016)]
ON SERVER
ADD EVENT sqlserver.error_reported
(
ACTION(
sqlserver.session_id,
sqlserver.sql_text
)
WHERE [package0].[not_equal_unicode_string]([message],N'''''')
AND [severity]>(10)
AND [sqlserver].[session_id]=(2016)
)
ADD TARGET package0.ring_buffer
WITH (
--ALLOW_SINGLE_EVENT_LOSS
--NO_EVENT_LOSS
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_MEMORY=4096 KB,
MAX_DISPATCH_LATENCY=1 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=ON,
STARTUP_STATE=OFF
);
ALTER EVENT SESSION [Error Handling Session(2016)] ON SERVER STATE=START;
BEGIN TRY
BACKUP DATABASE master
TO DISK = 'master.diff.bak'
WITH DIFFERENTIAL;
END TRY
BEGIN CATCH
DECLARE @XEData XML
SELECT @XEData = CAST(xet.target_data AS XML)
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'Error Handling Session(2016)';
/*
Check value of "totalEventsProcessed" to ensure events have been
dispatched to event session target (ring_buffer).
If no events have been processed, delay for a period of MAX_DISPATCH_LATENCY +1 (in seconds).
*/
IF @XEData.value('(/RingBufferTarget/@totalEventsProcessed)[1]', 'INT') = 0
BEGIN
WAITFOR DELAY '00:00:02';
SELECT @XEData = CAST(xet.target_data AS XML)
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'Error Handling Session(2016)';
END
--Comment/uncomment as desired to show the formatted XML session data.
SELECT @XEData;
--Shred the XML. Do whatever you want with this data:
-- log it to a table, create and send an email alert, etc.
SELECT
x.c.value(N'(@name)[1]', N'NVARCHAR(MAX)') AS EventName,
x.c.value(N'(@timestamp)[1]', N'datetime') AS EventTime,
x.c.value(N'(data[@name="error_number"]/value)[1]', N'NVARCHAR(MAX)') AS ErrorNumber,
x.c.value(N'(data[@name="severity"]/value)[1]', N'NVARCHAR(MAX)') AS Severity,
x.c.value(N'(data[@name="state"]/value)[1]', N'NVARCHAR(MAX)') AS [State],
x.c.value(N'(data[@name="user_defined"]/value)[1]', N'NVARCHAR(MAX)') AS UserDefined,
x.c.value(N'(data[@name="category"]/text)[1]', N'NVARCHAR(MAX)') AS Category,
x.c.value(N'(data[@name="destination"]/text)[1]', N'NVARCHAR(MAX)') AS Destination,
x.c.value(N'(data[@name="is_intercepted"]/value)[1]', N'NVARCHAR(MAX)') AS IsIntercepted,
x.c.value(N'(data[@name="message"]/value)[1]', N'NVARCHAR(MAX)') AS [Message],
x.c.value(N'(action[@name="sql_text"]/value)[1]', N'NVARCHAR(MAX)') AS SqlText,
x.c.value(N'(action[@name="session_id"]/value)[1]', N'NVARCHAR(MAX)') AS SessionId
FROM @XEData.nodes('//RingBufferTarget/event') AS x(c)
--WHERE x.c.value(N'(data[@name="destination"]/text)[1]', N'NVARCHAR(MAX)') = 'USER'
END CATCH
ALTER EVENT SESSION [Error Handling Session(2016)] ON SERVER STATE=STOP;
DROP EVENT SESSION [Error Handling Session(2016)] ON SERVER;
GO
After running the script, your SSMS output should look similar to this:
There's a lot of moving parts there. But you might be able to make it work for you. I wrote a few related blog posts about TRY...CATCH that may be helpful:
The Unfulfilled Promise of TRY...CATCH
Enhanced T-SQL Error Handling With Extended Events
Part 2: Enhanced T-SQL Error Handling With Extended Events
