Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 SQL Agent Problem

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?
Go to Top of Page

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.
Go to Top of Page

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.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

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. :)
Go to Top of Page

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_mist

No 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.
Go to Top of Page

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 @@version

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise 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....
Go to Top of Page

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
Go to Top of Page

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  RTM
DB Components SP1       8.00.384  SP1
DB Components SP2       8.00.534  SP2
DB COmpenents SP3 or 3a 8.00.760  SP3

The 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]
Go to Top of Page

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
Go to Top of Page

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.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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".
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-19 : 15:01:29
Did the 818 security patch help?

Tara
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page
    Next Page

- Advertisement -