How can I capture queries that fail on the SQL Server due to connection failures?

Profiler doesnt seem to capture if there is a connectivity loss.

This is scenario:

  1. DB server running in Domain A
  2. SQL Client running on a server in Domain B - runs a transaction that would typically take 15 seconds to complete.
  3. Network connectivity breaks at 10th second between Domain A and Domain B
  4. SQL client displays error message - network failure
  5. Transaction not completed on the DB server. No records updated.
  • Are you talking about queries that time out while they're running? It's really unclear what you're after given the comments you've left on the answers below.
    – Jon Seigel
    Jan 4, 2013 at 14:29
  • Not time-out. They are the transactions that fail due to network issues. The result of the transaction is not sent back to requesting component because of network failure. Want to identify the ones that fail only due to network issues. The transactions/queries can be seen running for a brief moment but dont complete the transaction.
    – Sam P
    Jan 5, 2013 at 6:59

2 Answers 2


I believe you have to do this on application level and not on SQL Server.

Here is .NET way of inserting in to the event viewer: http://forums.asp.net/t/1503109.aspx/1

  • Not .Net. Its a Cognos reporting server that initiates the queries.
    – Sam P
    Jan 4, 2013 at 5:43
  • Doesn't Cognos has it's own logs stored in a *.log files somewhere in it's installation directory? This might also be helpful: businessintelligence.ittoolbox.com/groups/technical-functional/…
    – avakharia
    Jan 4, 2013 at 17:38
  • Dont have much control over Cognos. Also Cognos is just one component facing the issue. There are several other components like Portal server, and ESB that are facing similar failed statements/transactions due to intermittent network failures. Need is to capture those transaction that reach SQL Server but the component initiating these transactions dont get a response back. Also SQL does not complete these transactions because of network failure.
    – Sam P
    Jan 5, 2013 at 7:01

You can query the SQL logs using EXEC SP_readerrorlog.

Create a table and load the log events using calling this stored proc. You can schedule a job to refresh the load in any interval you need.

    LogDate DATETIME,
    ProcessInfo VARCHAR(500),

INSERT Log_tbl
EXEC SP_readerrorlog

SELECT * FROM Log_tbl 

Quering the table above, you can look for an error similar to what I got below:

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: ]

  • Need is to the SQL Statements. I can already see the network failures on the Event Viewer.
    – Sam P
    Jan 4, 2013 at 5:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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