I'm using this config, trying to find error when a certain host executes a task ( it runs inside a software ):

My Template

The user says that the software is running, but it's not updating what it needs to update.

So, I'm tracking their queries and procs, to find some errors.

Is this correct? If I use this:

select * from table where 

It shows me an error ( that's what I want ).

My error

Do you guys use something different? Is this a good way to find errors using trace? I use HOSTNAME with his machine.

Again, it's working. I just would like to know some other tips.

Thank you very much.


Depending on what version of sql server you are using, you can choose :

  1. Server side trace - you can use User Error Message Event with filter on error <> 0, error <> 1, severity <> 10
  2. Extended Events

To automate import of server side trace files, you can use WMI alerts

Note: There is a performance impact on a busy server when you use Profiler. Also, from sql server 2008 R2 and up, I would highly recommend using Extended Events (we are in the process of moving all our server side traces to XEvents).

| improve this answer | |

Have you looked into SQL Server's Ring Buffer, especially the Exception Ring Buffer?

From SQL Server Ring Buffers and The Fellowship of the Ring by Sudarshan Narasimhan, I got the following query which works great for detecting uncaught exceptions and errors:

SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
    dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as Time_Stamp,
    cast(record as xml).value('(//Exception//Error)[1]', 'varchar(255)') as [Error],
    cast(record as xml).value('(//Exception/Severity)[1]', 'varchar(255)') as [Severity],
    cast(record as xml).value('(//Exception/State)[1]', 'varchar(255)') as [State],
    cast(record as xml).value('(//Exception/UserDefined)[1]', 'int') AS [isUserDefinedError],
    cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],
    cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type], 
    cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
    tme.ms_ticks as [Current Time]
from sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
cross join sys.sysmessages msg
where rbf.ring_buffer_type = 'RING_BUFFER_EXCEPTION' --and cast(record as xml).value('(//SPID)[1]', 'int') <> 0--in (122,90,161,179)
and msg.error = cast(record as xml).value('(//Exception//Error)[1]', 'varchar(500)') and msg.msglangid = 1033 --and [Error] = 4002
ORDER BY rbf.timestamp ASC
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.