Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database Microsoft SQL Server 2008r2 I have some applications in different programming languages. Some of them are legacy and I would not like to modify. Is there a way to log at server level all query errors, regardless of the application causing it? I would like to know, for each error, the query causing it, the error type, and ideally loginame and hostname.

share|improve this question
1  
You can capture the XE event error_reported. Create a session for that event, specify a predicate (unless you want this for all databases) and have a file as the target for later consumption/analysis. –  Thomas Stringer Feb 19 '13 at 11:53

2 Answers 2

up vote 4 down vote accepted

If you are just looking to log this information, you can set up an Extended Events session and capture the error_reported event. Here is an example:

CREATE EVENT SESSION [ErrorCapture] 
ON SERVER 
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE 
    (
        [severity] >= (11)
    )
) 
ADD TARGET package0.asynchronous_file_target
(
    SET filename=N'C:\SqlServer\Testing\ErrorCapture.xel'
)
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

ALTER EVENT SESSION [ErrorCapture]
ON SERVER
STATE = START;
GO

To test this out, here is a test error with RAISERROR():

raiserror('This is a test error', 2, 1);
go

Then by looking at the XE log through the sys.fn_xe_file_target_read_file system function, you will be able to see all of the logged errors.

share|improve this answer
    
How do I delete an event session I don't need anymore? –  carlo.borreo Feb 19 '13 at 16:48
    
drop event session [EventSessionName] on server; Where [EventSessionName] is the name of your event session that you want to drop. –  Thomas Stringer Feb 19 '13 at 16:52
    
[I am not sure if I am abusing your patience, I hope I am still within boundaries] I get thousands of errors of type "Changed database context to 'MAINDB'" and "Changed language setting to us_english." Is there a way to avoid logging these (non-)errors? –  carlo.borreo Feb 19 '13 at 17:10
    
In other words, I would like to log errors with severity >= 11, while these are 10. Is it possible? –  carlo.borreo Feb 19 '13 at 17:29
    
@carlo.borreo Absolutely. You just need to specify a predicate for the event and have the severity set to 11 or higher. –  Thomas Stringer Feb 19 '13 at 19:16

Yes. Monitor for Event Notifications in the Errors and Warnings Event Category. Specifically, the Exception event will be raised for each error. For example, based on the code in MS SQL Server third party transaction blocking monitor tools:

use msdb;
go

create queue [errors];
go

create service errors on queue [errors] (
    [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
go

create event notification [errors]
    on server for EXCEPTION
    to service N'errors', N'current database';
go

If we check the queue, we'll see all exception reported:

receive cast(message_body as xml) from errors;


<EVENT_INSTANCE>
  <EventType>EXCEPTION</EventType>
  <PostTime>2013-02-19T13:52:21.443</PostTime>
  <TextData>Violation of PRIMARY KEY constraint 'PK__x__3213E83F31271CCE'. Cannot insert duplicate key in object 'dbo.x'. The duplicate key value is (1).</TextData>
  ...
  <HostName>...</HostName>
  <ApplicationName>Microsoft SQL Server Management Studio - Query</ApplicationName>
  <LoginName>...</LoginName>
  ...
</EVENT_INSTANCE>
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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