In this post we
are going to discuss how to
track or get the list of all sql queries which executes on sql server but has
some error may be syntax error or execution error.
To track the sql
error we are going to create an event session.
Step 1: Create
an event session by executing following script:
CREATE EVENT SESSION [Track Sql Errors] ON SERVER
ADD EVENT
sqlserver.error_reported(
ACTION(
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.sql_text,
sqlserver.username
)
WHERE (
[package0].[greater_than_equal_int64]([severity],(15))
--AND
[sqlserver].[database_name]= N'master'
--AND
[sqlserver].[is_system]= 0
--AND
[sqlserver].[client_app_name]<> N'Microsoft SQL Server Management Studio
- Query'
)
)
Note: You can modify your file conditions according to your requirement.
[sqlserver].[database_name]: To tack only specified database. To allow in multiple database use IN clause or remove filter condition.
[sqlserver].[is_system]
= 0 : To not track the sql errors due to system events.
[sqlserver].[client_app_name]
<> N'Microsoft SQL Server Management Studio - Query': To no track sql
errors which has generated from SSMS
Step 2: Start
the event session [Track Sql Errors]
ALTER EVENT SESSION [Track Sql Errors]
ON SERVER
STATE = start
Step 3: To get the tracking data execute following script:
DECLARE @EventXML AS XML
SELECT
@EventXML = CAST(target_data AS XML)
FROM sys.dm_xe_session_targets
st
JOIN sys.dm_xe_sessions s
ON st.event_session_address
= s.[address]
WHERE s.name
= 'Track Sql Errors'
SELECT
T.c.value('(./@timestamp)[1]','datetime') AS [Execution Time],
T.c.value('(data/value)[1]','int') AS [Error Number],
T.c.value('(data/value)[2]','int') AS [Error Severity],
T.c.value('(data/value)[8]','varchar(max)') AS [Error Message],
T.c.value('(action/value)[1]','varchar(200)') AS [Executed By],
T.c.value('(action/value)[2]','varchar(max)') AS [Query],
T.c.value('(action/value)[3]','varchar(100)') AS [Database Name],
T.c.value('(action/value)[4]','varchar(max)') AS [Host Name],
T.c.value('(action/value)[5]','varchar(max)') AS [Program Name]
FROM @EventXML.nodes('RingBufferTarget/event')
T(c)
Note: Data of event session will be updated after some regular intervals, so to get new data, execute the above script again and again.
Step 4: To drop the above event session execute following script:
DROP EVENT SESSION [Track
Sql Errors] ON SERVER
