Sql-server – Why do connections to the database fail, but only sometimes

loginssql-server-2008-r2sql-server-agent

I am having some trouble diagnosing the cause of a problem I am having with Microsoft SQL Server 2008 R2.

There is an application written in C#.net that connects to the database.

Occasionally the application will throw this error:

Cannot open database "<database name>" requested by the login. The login failed.
Login failed for user '<user name>'.

I can successfully connect to the database through SQL Server Management Studio by copying and pasting the login information from my connection string in Web.config

Looking at the SQL Server log, this is the error that comes up:

Source      Message

Logon       Login failed for user '<user name>'. Reason: Failed to open the explicitly specified database.
Logon       Error: 18456 Severity: 14 State: 38.

Looking up that error code online doesn't provide any solutions. The recommendation is to run a trace using SQL Server Profiler and wait for the problem to happen.

I ran the trace and noticed that the following happens every time the error occurs (some lines removed for privacy reasons, click to enlarge):

sql trace

When the last item happens, this error message opens:

Failed to read trace data

I looked in the SQL Server Agent Jobs tree in SQL Server Management Studio and noticed some jobs in there that I created but don't use any more. I removed them and the error still comes up.

The only job left in there is:

  • syspolicy_purge_history

Which I assume are required to some extent.

Does anyone know how to fix this problem? What steps can I take to find and resolve the issue?

Detailed Version Info:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation  Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Best Answer

Is the error you see on that last line always preceded by the same set of jobs, or does the error happen in different places?

I've thought about this for the last half hour and everything I think of I can refute. Do you have any known networking issues on this server? I've seen something kind of like this with VMWare's networking stack being flaky and dropping connections. But then that doesn't make sense because the SQL box is showing an error.

Even though I ask about networking, I can come up with refutations to that, too. This is a tough one.

If the jobs listed above are the culprit, then I would need to see what they are doing be sure what was going on.

Related Question