Problem
You have an Azure SQL Managed Instance and you want to set up SQL Server alerts for errors with severity 17-25, similar what you would do for an on-prem SQL Server. You go to the SQL Server Agent folder in Object Explorer, expand it, and whoops – there is no Alerts folder.
As of time of writing this article (June 2025), Azure SQL Managed Instance doesn’t have this functionality, and we don’t have any ETA on when it will be implemented. So, how can we setup alerts in Azure SQL MI to notify us when there are issues?
Solution
You could try to configure Diagnostics Settings on a database level (for each or some databases) in the Azure portal, so they write information about SQL errors to log analytics workspace. Then you could use Azure Monitor and alert rules to get alerted. But that involves some Kusto queries which can get tricky. That solution will be outside of scope of this article.

What we will do is try another solution, more fun for hardcore database administrators. Since Managed Instance kind of “doesn’t have local file system” and cannot use Azure file shares (it would be nice if it could) – we will use extended events to write errors to an XEL file in a blob storage account. And then configure a process that will read that XEL file, format text and send emails (if errors are found).
Steps Overview
- Create a storage account
- Configure access for the Managed Instance to storage account
- Create extended events session
- T-SQL for reading from a XEL file
- Schedule SQL Agent job
Prerequisites
Before proceeding, make sure you have Database Mail configured and working. Configuring Database Mail on Azure SQL Managed Instance is outside of the scope of this article, but you can find detailed documentation on it here and here.
It is convenient to have a “default” mail profile configured and set, so you don’t have to specify its name at all when calling msdb..sp_send_dbmail stored procedure. Otherwise, just specify the “AzureManagedInstance_dbmail_profile”.
1 – Create a Storage Account
First thing we will need is to create a storage account. I’ve seen recommendations in the documentation to have the storage account in the same region as the Managed Instance, and use the same replication type (GRS, or GZRS, depending on if Managed Instance has or doesn’t have Zone-redundancy) as SQL MI. During the tests I performed, differing regions still worked, for example my SQL MI was West US 2 and the storage account was East US, creating and starting extended events worked.
One thing that did not work for me, is starting Extended Events when the storage account was Premium BlockBlobStorage. Extended Events session could be created, but starting it would throw this error:
Msg 25602, Level 17, State 0, Line 75
The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”, encountered a configuration error during initialization. Object cannot be added to the event session. The operating system returned error 13: ‘The data is invalid.’ while creating the file ‘https://yourstorageaccount.blob.core.windows.net/extendedevents/Errors_0_133946757494710000.xel’.
So go with account type of StorageV2 (general purpose v2), regardless of Standard or Premium performance, and avoid Block Blob Storage. At least that’s what I did during the test on General Purpose SQL MI. Business Critical SQL MI might have some chance of behaving differently, but I haven’t tested that.
Then, go to Containers, and create a private container with the name “extendedevents”. The name can be anything, just for the purpose of this exercise we will use “extendedevents”.
2 – Configure Access for the Managed Instance to the Storage Account
Managed Instance will need a credential to access the blob storage account.
In the Azure portal, go to storage account, Security > Shared access signature. Check box all resource types and permissions, make sure the end date is far enough in the future, hit the “Generate SAS and connection string” button.
Copy the SAS token (sv=…) and then paste it into this T-SQL code:
/* MSSQLTIPS.com (T-SQL) */
CREATE CREDENTIAL [https://yourstorageaccount.blob.core.windows.net/extendedevents]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<<<paste your SAS token here>>>'
Note that you will need to replace “yourstorageaccount” with the actual name of the storage account you created.
3 – Create Extended Events Session
Then, to create the actual session, run the below T-SQL:
/* MSSQLTIPS.com (T-SQL) */
CREATE EVENT SESSION [Error_Reporting] ON SERVER
ADD EVENT sqlserver.error_reported( ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.username)
WHERE ([severity]>=(17)))
ADD TARGET package0.event_file(SET filename=N'https://yourstorageaccount.blob.core.windows.net/extendedevents/Errors.xel',max_file_size=(102400),max_rollover_files=(5))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
Here are important notes:
You will need to replace “yourstorageaccount” with the actual name of the storage account you created.
Make sure the created blob file is big enough, to serve as long as possible before rolling over to a different file. This is because multiple XEL files can’t be read from the URL at the same time using wildcards, as you would do when working with local file system (or a fileshare). In this example, a 100 GB file is created. If you have a large number of errors on your system, you could consider creating a larger file. I haven’t tested how fast the XEL file could be filled on a very busy system with lots of errors. Storage pricing is low enough, even 1 TB is cheap, as long as you use Standard performance, not Premium. We do not need Premium for logs.
Also note that we are creating the session for errors with minimal severity of 17 and higher. You could play a bit and create a separate session for just errors with severity of 15,16 – you may be surprised to see that some of your applications are having SQL errors with severity of 15,16 without you or developers even knowing about. But on the other hand, if you have Change Data Capture, and/or some other features, that second session (severity 15,16) can get flooded with informational messages, which is not our goal.
Start the Session
Run the below to start the session:
/* MSSQLTIPS.com (T-SQL) */
ALTER EVENT SESSION [Error_Reporting] on SERVER STATE = START
4- T-SQL for Reading from an XEL File
Now the interesting part, how we are going to read the data from the XEL file.
After you created and started the session, go to the “extendedevents” container and note the actual file name. We created it as Errors.xel, but – it actually got a name more complicated than that. In my example, it is Errors_0_133945865715620000.xel. The script will be smart enough to get the actual file name from sys.dm_xe_session_targets DMV.
The below T-SQL is using the sys.fn_xe_file_target_read_file system function to read the XML values from the XEL file, turn them into “normal” values, that are easier to deal with and create a formatted report and send it via email.
The most important parameters (variables) in the script will be @XE_Session_Name, which is a name of extended events session (in our case, it is Error_Reporting), and @Minutes_Back, which specifies how many last minutes of log file records we are going to check.
For example, if you want the “Error Reporting” job to run every five minutes, and report to you everything that happened during the last 5 minutes, set @Minutes_Back to 5. Or you can have it run more frequently – set the @Minutes_Back accordingly.
And of course, set the rest of the parameters related to sending the database mail – recipients, reply_to, from, subject.
/* MSSQLTIPS.com (T-SQL) */
SET QUOTED_IDENTIFIER ON
/* parameters */
declare
@Path nvarchar(260), /* path to a XEL file (URL or local drive/fileshare for SQL Server) */
@Address varbinary(8) /* memory address of the session */
/* configurable parameters */
declare
@XE_Session_Name nvarchar(256), /* name of the Extended Events session */
@Minutes_Back int, /* lookback period in minutes (based on "timestamp_utc" in XEL file) */
@_recipients varchar(max), /* list of email recipients */
@_from_address varchar(max), /* from email address */
@_reply_to varchar(max), /* reply to email address */
@_subject nvarchar(255) /* subject of email */
/* set the main parameters */
set @XE_Session_Name = 'Error_Reporting'
set @Minutes_Back = 5
/* parameters for email report */
set @_recipients = 'dba@domain.com'
set @_from_address = 'dba@domain.com'
set @_reply_to = 'dba@domain.com'
set @_subject = 'Error Reporting'
/* get the memory address of the session */
select
@Address = [address]
from sys.dm_xe_sessions where [name] = @XE_Session_Name
/* get the path to .xel file */
select
@Path = substring(
target_data, /* expression */
charindex('file name="',target_data) + 11, /* start */
(charindex('.xel"',target_data) + 4) - (charindex('file name="',target_data) + 11) /* length */
)
from sys.dm_xe_session_targets
where event_session_address = @address
and target_data is not NULL
/* temp table for holding last N minutes of errors */
drop table if exists #Errors
create table #Errors (
ID int identity primary key,
timestamp_utc datetime,
[database_name] nvarchar(128) NULL,
[error_number] int,
severity int,
[state] int,
category int,
category_desc nvarchar(128),
destination nvarchar(128),
session_server_principal_name nvarchar(128) NULL,
username nvarchar(128) NULL,
client_app_name nvarchar(128) NULL,
client_hostname nvarchar(128) NULL,
[message] nvarchar(2048),
sql_text nvarchar(3000) NULL
)
/* xml shredding */
insert into #Errors (timestamp_utc, [database_name], [error_number], severity, [state], category, destination, session_server_principal_name, username, client_app_name, client_hostname, [message], sql_text)
SELECT
[timestamp_utc] = cast(timestamp_utc as datetime),
[database_name] = event_data_xml.value('(event/action[@name = "database_name"]/value/text())[1]', 'nvarchar(128)'),
[error_number] = event_data_xml.value('(event/data[@name = "error_number"]/value/text())[1]', 'int'),
severity = event_data_xml.value('(event/data[@name = "severity"]/value/text())[1]', 'int'),
[state] = event_data_xml.value('(event/data[@name = "state"]/value/text())[1]', 'int'),
category = event_data_xml.value('(event/data[@name = "category"]/value/text())[1]', 'sysname'),
destination = event_data_xml.value('(event/data[@name = "destination"]/text/text())[1]', 'nvarchar(200)'),
session_server_principal_name = event_data_xml.value('(event/action[@name = "session_server_principal_name"]/value/text())[1]', 'nvarchar(128)'),
username = event_data_xml.value('(event/action[@name = "username"]/value/text())[1]', 'nvarchar(128)'),
client_app_name = event_data_xml.value('(event/action[@name = "client_app_name"]/value/text())[1]', 'nvarchar(128)'),
client_hostname = event_data_xml.value('(event/action[@name = "client_hostname"]/value/text())[1]', 'nvarchar(128)'),
[message] = event_data_xml.value('(event/data[@name = "message"]/value/text())[1]', 'nvarchar(2048)'),
[sql_text] = event_data_xml.value('(event/action[@name = "sql_text"]/value/text())[1]', 'nvarchar(3000)')
FROM sys.fn_xe_file_target_read_file(@Path, NULL, NULL, NULL) xft
CROSS APPLY (SELECT CAST(xft.event_data AS XML)) CA(event_data_xml)
where cast(timestamp_utc as datetime) > dateadd(minute,-@Minutes_Back, getdate())
/* resolve category description */
update #Errors
set category_desc = case category
when 1 then 'UNKNOWN'
when 2 then 'SERVER'
when 3 then 'DATABASE'
when 4 then 'LOGON'
when 5 then 'JOB'
when 6 then 'REPLICATION'
when 7 then 'SECURITY'
when 8 then 'USER'
when 9 then 'QUERY PROCESSING'
when 10 then 'SYSTEM'
when 11 then 'RESOURCE'
when 12 then 'IO'
when 13 then 'NETWORKING'
when 14 then 'BACKUP/RESTORE'
when 15 then 'AGENT'
when 16 then 'FULL-TEXT SEARCH'
when 17 then 'CLR'
when 18 then 'SERVICE BROKER'
when 19 then 'DTC (Distributed Transactions)'
when 20 then 'MEMORY'
when 21 then 'SCHEDULER'
when 22 then 'STORAGE'
when 23 then 'EXECUTION'
when 24 then 'DEADLOCK'
when 25 then 'ALWAYS ON / HIGH AVAILABILITY'
when 26 then 'POLYBASE'
when 27 then 'MACHINE LEARNING SERVICES'
when 28 then 'GRAPH PROCESSING'
when 29 then 'TEMPORAL TABLES'
when 30 then 'OTHER FEATURES'
end
/* fill the report's text */
declare
@ID int = 1,
@Report nvarchar(max) = '',
@NewLine nvarchar(10) = CHAR(13) + CHAR(10)
while @ID <= (select max(ID) from #Errors)
begin
select @Report = @Report + cast(@ID as nvarchar) + '
Error number -- ' + cast([error_number] as nvarchar) + ', Severity -- ' + cast(severity as nvarchar) + ', State -- ' + cast([state] as nvarchar) + '
Timestamp UTC -- ' + convert(nvarchar,timestamp_utc,21) + '
Database name -- ' + isnull([database_name],'') + '
Category -- ' + category_desc + '
Destination -- ' + [destination] + '
Original login -- ' + isnull([session_server_principal_name],'') + '
Client hostname -- ' + isnull(client_hostname,'') + '
Message -- ' + [message] + '
SQL text -- ' + isnull(sql_text,'') + '
'
from #Errors
where ID = @ID
set @ID = @ID + 1
end
/* send the report over email */
if (select count(*) from #Errors) > 0 begin
exec msdb..sp_send_dbmail
@profile_name = 'AzureManagedInstance_dbmail_profile',
@recipients = @_recipients,
@from_address = @_from_address,
@reply_to = @_reply_to,
@subject = @_subject,
@body = @Report
end
Note that above script works for SQL Server 2017 through 2025.
SQL Server 2016’s XEL file does not have timestamp_utc column. It is still possible to adapt the script for SQL Server 2016, by removing timestamp_utc, it just won’t have the ability to read the last 5 minutes – it will read the entire file.
5 – Schedule the SQL Agent Job
Once you tested the script and it works on your system, we can proceed to the last step which is to schedule a SQL Agent Job. Most DBAs are probably familiar with this part. Schedule it to run as often as you want: 60, 15, 5, or 1 minute. Don’t forget to set the @Minutes_Back parameter accordingly, otherwise you could get repeated error messages in email reports, or no error messages.
Create Errors and Test
For testing, you can create errors using this T-SQL:
/* MSSQLTIPS.com (T-SQL) */
raiserror('test error',17,1)
raiserror('test error severity 20, disconnects the session',20,1) with log
Email Report
The email report will look like this. Don’t forget to configure the Restore line breaks in emails.

Pros of the Approach
While the SQL Server Agent Alerts feature can notify you only about errors that have “is_event_logged” = 1 flag in sys.messages (and written to the Windows Application Log), the proposed solution with “error_reported” XE event captures all errors with specified severity levels, so you can see everything that is going on in the system, regardless if it is written to the application log or not. Some errors you can’t really do anything about, some others will pinpoint important things such as application or infrastructure problems, or problems with features in the database engine itself.
Also, the solution can be used not only in Azure SQL Managed Instance, but also in SQL Server, and when the XEL file is stored on a local drive, wildcards can be used when reading from it.
The report that is sent via email, contains all errors that happened during the look back period. This can be convenient to get 1 summary email instead of 20 separate emails for each error individually.
Also, the email report contains the full text of the message (error), while when you open the logs via View Target Data in SSMS GUI, the messages are truncated. Some errors, for example related to networking, can have IP address at the end of the message. It is not seen in the SSMS GUI, while it is seen in the email report.
Cons of the Approach
This solution is new and hasn’t been tested on systems with a very high volume of errors. Behaviors are unknown when the XEL log file fills with too much data. But there may not be too many systems with high error counts. Make sure to create big enough files in blob storage, so you don’t have to worry about that too much.
Conclusion
In this article, we introduced a new solution for error reporting for Azure SQL Managed Instance and SQL Server. This solution can be especially useful in Managed Instance, as it doesn’t have Alerts feature implemented yet (as of June 2025).
Next Steps