SQL Connectivity not stable & jobs getting failed.
-
Dear All,
We have a server with MS-SQL 2008 64-bit edition. It is working fine till last month & we did not make any change on this server. But, since last month as & when we lost the SQL connectivity with this server & during this all jobs are getting failed. Here, this is to be noted that only SQL connectivity is lost between this duration & we are able to take Remote connection of this server & on server, if we connect to SQL through Management studio, it is getting connected.
On initial level, we started all SQL services & for next 3-4 days it works fine. But, again the issues came. Then we start all SQL services on daily basis in night. But, after some days again we are facing the problem. Today, again we faced the same problem.
When we search "SQL Server Agent" - Error Logs then found following errors. If we go through the below errors the errors are same except BOLD text.
--<><>-- --<><>-- --<><>-- --<><>--
05/30/2012 12:37:00,,Error,[382] Logon to server '(local)' failed (JobManager)
05/30/2012 12:37:00,,Error,[298] SQLServer Error: 258<c/> Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]
05/30/2012 12:37:00,,Error,[165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]
05/30/2012 12:37:00,,Error,[298] SQLServer Error: 258<c/> TCP Provider: Timeout error [258]. [SQLSTATE 08001]
--<><>-- --<><>-- --<><>-- --<><>--
--<><>-- --<><>-- --<><>-- --<><>--
05/30/2012 12:36:45,,Error,[382] Logon to server '(local)' failed (SaveAllSchedules)
05/30/2012 12:36:45,,Error,[298] SQLServer Error: 258<c/> Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]
05/30/2012 12:36:45,,Error,[165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]
05/30/2012 12:36:45,,Error,[298] SQLServer Error: 258<c/> TCP Provider: Timeout error [258]. [SQLSTATE 08001]
--<><>-- --<><>-- --<><>-- --<><>--
--<><>-- --<><>-- --<><>-- --<><>--
05/30/2012 12:36:30,,Error,[382] Logon to server '(local)' failed (ConnUpdateStartExecutionDate)
05/30/2012 12:36:30,,Error,[382] Logon to server '(local)' failed (ConnUpdateStartExecutionDate)
05/30/2012 12:36:30,,Error,[298] SQLServer Error: 258<c/> Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]
05/30/2012 12:36:30,,Error,[298] SQLServer Error: 258<c/> Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]
05/30/2012 12:36:30,,Error,[165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]
05/30/2012 12:36:30,,Error,[165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]
05/30/2012 12:36:30,,Error,[298] SQLServer Error: 258<c/> TCP Provider: Timeout error [258]. [SQLSTATE 08001]
05/30/2012 12:36:30,,Error,[298] SQLServer Error: 258<c/> TCP Provider: Timeout error [258]. [SQLSTATE 08001]
--<><>-- --<><>-- --<><>-- --<><>--
--<><>-- --<><>-- --<><>-- --<><>--
05/30/2012 11:04:15,,Error,[382] Logon to server '(local)' failed (ConnUpdateJobActivity_NextScheduledRunDate)
05/30/2012 11:04:15,,Error,[298] SQLServer Error: 258<c/> Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]
05/30/2012 11:04:15,,Error,[165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]
05/30/2012 11:04:15,,Error,[298] SQLServer Error: 258<c/> TCP Provider: Timeout error [258]. [SQLSTATE 08001]
05/30/2012 11:04:15,,Error,[382] Logon to server 'WINDOWS-WINDOWS' failed (ConnLogJobHistory)
05/30/2012 11:04:15,,Error,[298] SQLServer Error: 258<c/> Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]
05/30/2012 11:04:15,,Error,[165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]
05/30/2012 11:04:15,,Error,[298] SQLServer Error: 258<c/> TCP Provider: Timeout error [258]. [SQLSTATE 08001]
--<><>-- --<><>-- --<><>-- --<><>--
--<><>-- --<><>-- --<><>-- --<><>--
05/30/2012 10:54:14,,Error,[382] Logon to server 'WINDOWS-WINDOWS' failed (ConnLogJobHistory)
05/30/2012 10:54:14,,Error,[298] SQLServer Error: 258<c/> Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]
05/30/2012 10:54:14,,Error,[165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]
05/30/2012 10:54:14,,Error,[298] SQLServer Error: 258<c/> TCP Provider: Timeout error [258]. [SQLSTATE 08001]
--<><>-- --<><>-- --<><>-- --<><>--
--<><>-- --<><>-- --<><>-- --<><>--
05/30/2012 00:35:52,,Error,[364] The Messenger service has not been started - NetSend notifications will not be sent
--<><>-- --<><>-- --<><>-- --<><>--
Please guide how to resolve this issue permanently.
-
How is performance, particularly CPU usage on the server when you're getting that error? It may be the server is overloaded.
-
Are you running a ton of CPU/IO intensive jobs at the time? Do you have any Server monitoring tools that you can look back at CPU and IO usage?
How much memory is in this server and do you have SQL Server consuming all of it or are their apps installed on this server and everything is swapping memory......
Do you have a lot of IO intensive databases on this server?
Do you update stats on a regular basis, Rebuilt Indexes??? (thinking that you have long running queries that might be helped by doing these two.... a long shot)
If your data drive is more than half full have you stopped sql server and defragged that drive? (again, another long shot but if you can undertake the outage worth a try)
-
what is your max worker threads setting?
MVDBA
-
and how many connections are currently open on your server (perhaps your application is not closing connections properly - it's not unheard of!!!)
also - how much RAM on the box and what is the max ram allocated to SQL?
MVDBA
-
We are experiencing the same problem on one of our SQL Server instances. The only difference is that the server is on a VMWare box.
Were you able to resolve your problem?
-
-
Did you ever get an answer to your problem? I am having the same issue.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply