August 12, 2014 at 2:24 am
Hello all.
I am having trouble with SQL Server Agent on an instance of SQL 2008 R2. The agent appears to be running, but none of my scheduled jobs are being run. They’re scheduled to run, and on closer examination it looks as though they are running but there is never any consequence and the job history doesn’t update afterwards to reflect whether it was successful or failed.
In addition to the above, the SQL Server Agent Error Log is being populated every 30 seconds with the following messages:
[298] SQLServer Error: 5, Named Pipes Provider: Could not open a connection to SQL Server [5]. [SQLSTATE 08001]
[165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
[298] SQLServer Error: 5, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]
...then one of the following three:
- [382] Logon to server '[server name removed]' failed (ConnLogJobHistory)
- [382] Logon to server '[server name removed]' failed (ConnAttemptCachableOp)
- [382] Logon to server '[server name removed]' failed (ConnSetJobCompletionState)
It sounds (to me) like a permissions problem but I certainly haven’t made any changes either to the accounts that the jobs are run under or the account that the SQL Server Agent service runs under (a dedicated domain account with appropriate permissions).
I’m at a bit of a loss with this and would be keen to hear your suggestions for what else I can check to hopefully sort this out!
Thanks in advance,
Dan
August 13, 2014 at 9:17 am
Even though you think this is unlikely, I think most reasonable cause is SQLAgent service account now lacks sufficient rights (either within SQL or at OS-level). To further diagnose this you could try and use SQL Profiler to trace the activity when you try to stop/start SQLAgent service. Be sure to capture just about all errors/warning and security audit events, Stored Procedure SP:Starting/Completed, RPC:Starting/Completed, and TSQL SQL:BatchStarting/Completed events. Might help convince you one way or the other.
August 13, 2014 at 9:21 am
danchard (8/12/2014)
Hello all.I am having trouble with SQL Server Agent on an instance of SQL 2008 R2. The agent appears to be running, but none of my scheduled jobs are being run. They’re scheduled to run, and on closer examination it looks as though they are running but there is never any consequence and the job history doesn’t update afterwards to reflect whether it was successful or failed.
In addition to the above, the SQL Server Agent Error Log is being populated every 30 seconds with the following messages:
[298] SQLServer Error: 5, Named Pipes Provider: Could not open a connection to SQL Server [5]. [SQLSTATE 08001]
[165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
[298] SQLServer Error: 5, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]
...then one of the following three:
- [382] Logon to server '[server name removed]' failed (ConnLogJobHistory)
- [382] Logon to server '[server name removed]' failed (ConnAttemptCachableOp)
- [382] Logon to server '[server name removed]' failed (ConnSetJobCompletionState)
It sounds (to me) like a permissions problem but I certainly haven’t made any changes either to the accounts that the jobs are run under or the account that the SQL Server Agent service runs under (a dedicated domain account with appropriate permissions).
I’m at a bit of a loss with this and would be keen to hear your suggestions for what else I can check to hopefully sort this out!
Thanks in advance,
Dan
Check to make sure the service account password was not changed or that the service account password did not expire and get locked out.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply