How can I get all the failure SQL queries logs o

Question

I want to track all the failure SQL in the log metrics, how can i get the output

Answer 1

HI,

Thanks for reaching out to Microsoft Q&A.

You can enable the audit logs for this purpose.

Please find detailed guidance in below link :

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql

Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

You can use Extended Events to capture all T-SQL instructions failing. Use the sqlserver.error_reported event as shown below:

CREATE EVENT SESSION azure_monitor
ON DATABASE

ADD EVENT sqlserver.error_reported(
ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.session_id, sqlserver.sql_text,
        sqlserver.username))

You can capture failures and store then on a file located on an Azure Storage Account container.

Answer 3

Hi @Robert Antwi

Here is an example of an Extedned Events Session to collect SQL Errors. this is actually the one I use

If you don't feel confortable with Extended Events you can try using SQL Profiler and capture error_reported events (but I do not recommend this option)

CREATE EVENT SESSION [sql_text_and_errors] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)
    WHERE ([error_number]<>(2528) AND [error_number]<>(3014) AND [error_number]<>(4035) AND [error_number]<>(8153) AND [error_number]<>(5701) AND [error_number]<>(5703) AND [error_number]<>(8153) 
	AND [error_number]<>(22803) AND [error_number]<>(18265) AND [error_number]<>(14205) AND [error_number]<>(14213) AND [error_number]<>(14214) AND [error_number]<>(14215) AND [error_number]<>(14216) 
	AND [error_number]<>(14549) AND [error_number]<>(14558) AND [error_number]<>(14559) AND [error_number]<>(14560) AND [error_number]<>(14561) AND [error_number]<>(14562) AND [error_number]<>(14563) 
	AND [error_number]<>(14564) AND [error_number]<>(14565) AND [error_number]<>(14566) AND [error_number]<>(14567) AND [error_number]<>(14568) AND [error_number]<>(14569) AND [error_number]<>(14570) 
	AND [error_number]<>(14635) AND [error_number]<>(14638) AND [error_number]<>(951) AND [error_number]<>(3211) AND [error_number]<>(9104) AND [error_number]<=(50000))) 
ADD TARGET package0.event_file(SET filename=N'C:\Temp\xEvent_Errors',max_file_size=(4000))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION sql_text_and_errors ON SERVER
STATE=START;