Re: SQL Server Agent hangs and becomes unresponsive

From: John Bell (jbellnewsposts_at_hotmail.com)
Date: 12/27/04 

Date: Mon, 27 Dec 2004 05:33:57 -0000

Hi Sean

Have you tried to manually execute each step in the job? Check that you can
log onto the FTP server, that the files are in the correct format, and they
are being processed/archived correctly, there is no residual data that is
causing problems. Stopping an starting the server will not help if there is
a very very large or corrupted file on the FTP server being half downloaded
each time! If there is a backlog you may want to process your files
separately by removing the FTP section.

You should also check that maintainance is being done on the tables
concened. Also check the disc space and fragmentation.

You may want to re-think the architecture, possibly downloading more often
of uploading into a staging area/database.

You may want to ensure that alerts are being sent for the success (or
failure) of they job, then you sort out any problems early.

HTH

John

"Sean Blaes" <Sean Blaes@discussions.microsoft.com> wrote in message
news:2442081E-3E6D-481B-922E-CE8D2F9331F7@microsoft.com...
>I have my SQL Server and Agent both installed as a power use named
> "SQL_User", the jobs include some standard backup and maintenance plan
> jobs,
> along with a fairly long running (up to about 30 minutes) DTS job that
> manages downloading and importing data from a remote server via FTP, then
> merging that data with my own.
>
> This has been running fairly well for about a year, but lately I've been
> having significant problems with SQL Server Agent. It seems to hang
> regularly, the symptoms being that if I open Enterprise Manger and view
> jobs
> (after refreshing the view) then all jobs show a last date run of "cannot
> determine the last job was run" or something like that. So, I shut down
> SQL
> Server Agent via Enterprise manager, which gives me a message that says it
> is
> not responding to the control request. Enterprise Manager at this point
> shows SQL Server Agent as still running. Finally, if I try to shut it
> down
> again via Enterprise Manager, it shuts down immediately, and I can restart
> it. I've checked the event viewer and there don't appear to be any error
> messages in the system or application logs. I am running SQL Server 2000
> SP3. The database is fairly large, but not huge (a few tables with around
> 1-2 million rows, with many tables on the order of tens of thousands.)

Relevant Pages

  • RE: FTP and ISA setup
    ... Please follow the instruction described on the following KB to enable external clients to access your FTP server. ... Local port: Fixed port ... Change the EnablePortAttack value to 1. ...
    (microsoft.public.windows.server.sbs)
  • Re: Is this a 3-Leg Perimeter scenario?
    ... Do you mean the FTP server is hosted on the ... This newsgroup only focuses on SBS technical issues. ... The detailed network diagram. ...
    (microsoft.public.windows.server.sbs)
  • Re: Microsoft FTP Server problem on W2K?
    ... client (rather than another server, as in proxy transfer), the IP address ... port) currently in use on the control connection. ... >the remote FTP server was, at least at a TCP level, prepared to accept the ...
    (microsoft.public.inetserver.iis.security)
  • Re: SQL Login audit log
    ... In event viewer, there are 3 logs SYSTEM, Error and Application ... OTherwise, in Enterprise Manager ... >> You can right-click the server in ENTERPRISE MANAGER and check the ... >>> Server not SQL Server users. ...
    (comp.databases.ms-sqlserver)
  • Re: How to develop FTP Server On PPC?
    ... FTP server due to licensing restrictions. ... the server portions (there's no FTP client to my knowledge on CE), ... © 2003 Microsoft Corporation. ...
    (microsoft.public.windowsce.embedded.vc)
Loading