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.

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.
share|improve this question
    
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 '13 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. –  psam Jan 5 '13 at 6:59
add comment

2 Answers

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

share|improve this answer
    
Not .Net. Its a Cognos reporting server that initiates the queries. –  psam Jan 4 '13 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 '13 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. –  psam Jan 5 '13 at 7:01
add comment

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.

CREATE TABLE Log_tbl
(
    LogDate DATETIME,
    ProcessInfo VARCHAR(500),
    Text NVARCHAR(MAX)
)

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: ]

share|improve this answer
    
Need is to the SQL Statements. I can already see the network failures on the Event Viewer. –  psam Jan 4 '13 at 5:41
add comment

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.