Jun 30, 2014

Sql Server track or know all queries which executes and throw syntax error

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

No comments:

Post a Comment