Author |
Topic |
gwhiz
Yak Posting Veteran
78 Posts |
Posted - 2004-07-02 : 13:58:41
|
I have a strange problem going on that I was wondering if anyone else had experienced or might have some insight into. This has now happened to me twice this is a SQL Server Enterprise 2000 clustered box. SQL Agent has simply stopped running jobs. The following errors are displayed in the error log. It looks like the agent is trying to start the jobs but can't. Stopping the Agent and restarting just the agent corrects the problem. Any ideas.2004-06-27 16:14:42 - ! [298] SQLServer Error: 109, [SQLSTATE 08001] 2004-06-27 16:14:42 - ! [298] SQLServer Error: 109, ConnectionOpen (LoadNetlibs()). [SQLSTATE 01000] 2004-06-27 16:14:42 - ! [382] Logon to server '(local)' failed (JobManager)2004-06-27 16:15:00 - ! [380] Unable to start JobManager thread for job Transaction Log Backup Job for DB Maintenance Plan 'PIMS LogShipping'2004-06-27 16:15:20 - ! [298] SQLServer Error: 109, [SQLSTATE 08001] |
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-07-02 : 14:50:26
|
How many connections is SQL configured/licensed for? Is it possible you are hitting the max connections? |
|
|
gwhiz
Yak Posting Veteran
78 Posts |
Posted - 2004-07-02 : 16:21:11
|
No this SQL server is configured for 3000 connections and we probably have around ~100 or so. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-02 : 18:44:51
|
And you have 3000??????????????????????????????????You need to set this to 350 or so.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-07-05 : 06:27:25
|
It looks like a logon issue. Have you chnaged any passwords recently?-------Moo. :) |
|
|
gwhiz
Yak Posting Veteran
78 Posts |
Posted - 2004-07-06 : 16:36:28
|
Derrick,Why do you say this needs to be changed this is simply the number of licenses value? I'm not aware of any impact this could have other than to lockout connections once this limit is hit.Mr_mistNo these passwords have not changed since the account was implemented a couple of months ago. The strange thing is all I have to do is stop and restart the agent and everything returns to normal. No fussing with the passwords or accounts. |
|
|
imlachws
Starting Member
3 Posts |
Posted - 2004-07-09 : 15:25:05
|
I am having very much the same problem, only I am not running a cluster server. Every so often the Agent fails to log in. Restarting the Agent 'fixes' the problem. No idea what could be the cause.select @@versionMicrosoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft CorporationEnterprise Edition on Windows NT 5.2 (Build 3790: )Agent Error Log[298] SQLServer Error: 109, [SQLSTATE 08001] [298] SQLServer Error: 109, ConnectionOpen (LoadNetlibs()). [SQLSTATE 01000] [382] Logon to server 'XXXXX' failed (ConnAttemptCachableOp) If I figure anything out I'll post what I discover.... |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-09 : 15:32:49
|
Do you have service pack 3a + the latest security patch (includes bug fixes as well) installed?Tara |
|
|
imlachws
Starting Member
3 Posts |
Posted - 2004-07-09 : 21:21:50
|
Good question. The OS is Windows Server 2003 (NT 5.2) with the latest security patch, KB840374 The @@version output indicates that I am at SP3 for SQL2K. Reference the table below:SQL Server version @@version Product level======================= ========= =============SQL2K Original Release 8.00.194 RTMDB Components SP1 8.00.384 SP1DB Components SP2 8.00.534 SP2DB COmpenents SP3 or 3a 8.00.760 SP3The version of my ssnetlib.dll is 2000.80.766.0 indicating SP3a.[url]http://support.microsoft.com/default.aspx?scid=/support/servicepacks/SQL/2000/SP3ReadMe.asp#_identifying_the_current_version_of_sql_server_or_analysis_services[/url] |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-09 : 22:32:53
|
You should have 818 if you've got the latest security patch installed. Not sure if it'll help you here, but it's worth a shot at least.Tara |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-09 : 23:44:15
|
The reason you shouldn't have your connections setup to 3000 is because connections take up memory. They don't take up a lot, but it's memory you shouldn't ever have to worry about. Don't set a high limit on something just because you can. If you are normally at 300 and you get up to 3000, you have some extremely serious problems.Also, you only have so many threads available. After you get up to a certain amount, you should start forcing people to "get in line" if you will so you don't just saturate the server and lock it up.Here's a note from Books Online also:Important If you must use this option, do not set the value too high because each connection takes approximately 24 kilobytes (KB) of overhead regardless of whether the connection is being used. If you exceed the maximum number of user connections, you receive an error message and are not able to connect until another connection becomes available.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
web guy
Starting Member
5 Posts |
Posted - 2004-07-19 : 12:18:43
|
Has anyone been able to resolve the problems mentioned here? Did reducing the number of connections/threads allowed help?We've encountered a very similar error intermitently over the last couple months:2004-07-17 00:00:30 - ! [298] SQLServer Error: 18, SSL Security error [SQLSTATE 08001] 2004-07-17 00:00:30 - ! [298] SQLServer Error: 1364, ConnectionOpen (SECCreateCredentials()). [SQLSTATE 01000] 2004-07-17 00:00:30 - ! [382] Logon to server '(local)' failed (JobManager)These appear when the database is supposed to be performing its nightly backups, which presumably failed.We didn't have time to troubleshoot before so we just rebooted the system, but it's happened too many times to ignore now.We only have SQL2000 SP2 installed; but comments above make it seem that the latest service pack and fixes don't help anyway. We're not clustered either.Could a hacker attack have compromised the system in some way?As a temporary solution should we just set up a script to restart the SQL Agent every day?Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-19 : 13:24:44
|
Are you getting password attacks on SA, ADMIN and ROOT? (Turn on FAILED LOGIN tracking in Entperise Manger - right click Server - its in PROPERTIES.)This will fill up your SQL and Event logs with failed attempts, if that's whats happening. I've known it bring a server to its knees.Kristen |
|
|
web guy
Starting Member
5 Posts |
Posted - 2004-07-19 : 13:44:53
|
Kristen:I presume you mean that the constant updating of logs is what brings the server to its knees, and not the password attacks themselves?If so, I don't believe this is the case because the "Audit Level" in the Security tab is currently set to "off". |
|
|
gwhiz
Yak Posting Veteran
78 Posts |
Posted - 2004-07-19 : 14:08:04
|
Derrick,Ok maybe I was not clear. The server is licensed for 3000 connections. But the user connections property within SQL is set to the default which is 0. I don't see how the licensing setting can have an effect on the memory usage of SQL Server. I have not seen this to be the case. Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-19 : 14:16:14
|
Sorry, no its the password attack that is the problem - SQL spends all its time saying "Sorry, no dice, wrong password" - but when it happens, AND is being logged, it will create a LOT of log data!Kristen |
|
|
web guy
Starting Member
5 Posts |
Posted - 2004-07-19 : 15:00:13
|
Kristen:oops, looks like I was unclear myself... yes, that's what I meant to ask, whether it was the logging of the password attacks that brings down the server, not just writing logs themselves.In any event, I don't think the database is being DDoS'ed, unless the attacks make it so all connections are forced to stay open. It's set to the default 0 i.e. unlimited right now. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-19 : 15:01:29
|
Did the 818 security patch help?Tara |
|
|
web guy
Starting Member
5 Posts |
Posted - 2004-07-19 : 15:05:56
|
Incidentally, the database server got knocked over again--in a record 3 hours. I was unable to manually restart the SQL and had to reboot the entire server.It failed with the error:[257] Startup error: Another instance of SQLServerAgent is already servicing server instance MSSQLSERVER |
|
|
imlachws
Starting Member
3 Posts |
Posted - 2004-07-19 : 20:14:11
|
Tara,I finally found the security update at Microsoft.com... [url]http://www.microsoft.com/technet/security/bulletin/MS03-031.mspx[/url] for anyone else who is interested. After reading the issues it resolves, I too am doubtful that it will repair this occurrence. I will however, work to get the patch installed. Thanks for making it's existence known to the group.The occurrence of this issue is infrequent and I have not been able to identify a cause such that it can be reproduce. The best I could do is report back over some period of time that it has not yet recurred. As mentioned by another poster, it is troublesome because jobs that rely on the agent, e.g. backups, dbcc's, update stats, ... indicate successful execution in the Job Manager but the jobs do not run. Only attentive log verification discovers the problem.Scott |
|
|
web guy
Starting Member
5 Posts |
Posted - 2004-07-20 : 09:22:33
|
If only SQLServerAgent's inability to access the database were the only problem... I'm starting to think my issue is something else entirely, despite the similar error messages. When we get those errors, it means our web applications can't access the database either, so customers see an ugly error page instead. Enterprise Manager is still able to access the DB though.I won't be able to update the server for a few days, so in the meantime I'm writing a script to poll some of our critical pages every now and then, and fire off an alert email if it can't access them. |
|
|
Next Page
|