0

Running a Windows 2003 with SQL Server 2005 on, with around ~85-90 jobs which run once per hour (at different times), generating new sitemaps for each website hosted on the server, along with un-publishing pages.

The issue which I'm having with the server is that, the server seems to be running in three stages, the jobs run between 8am - 6pm each day, and the server is restarted around 2-3am every morning.

Once the jobs start in the morning, they will run successfully for around 90 minutes (9.30am), taking around 3-5 seconds to complete the job.

After 9.30, the jobs will start correctly, then, get stuck on executing (using 100% CPU).

If I manually stop the job, the server goes back to normal, but then the jobs will fail to execute at all, throwing the error below. (They were changed to run every 60 minutes, not every 15 a few months ago, but the names were never changed)

Step ID     1
Server      [Server Name]
Job Name        Execute Replicate File For [website] web
Step Name       Vbscript for Replicate File  every 15Minutes
Duration        00:00:00
Sql Severity        0
Sql Message ID      0
Operator Emailed        
Operator Net sent       
Operator Paged      
Retries Attempted       0

Message
Executed as user: [Domain]\[User]. The step did not generate any output. 
The step failed.

Information about the script that is running: It is a VBScript

Dim IEObj 
Set IEObj=CreateObject("InternetExplorer.Application")
IEObj.Navigate "[weblink]/ReplicateFile.asp"
IEObj.visible=false
do Until IEObj.ReadyState=4
loop
IEObj.quit
Set IEObj=Nothing

@mellamokb - The script is down in the general settings as an ActiveX Script, not a T-SQL one, so I am unable to specify an output file for the errors.

Note: I didn't setup anything on this server or the CMS which it is replicating, and my knowledge of databases is fairly low.

ItWontWork
  • 67
  • 1
  • 5
  • Setup verbose logging so you can get some useful output and see if that helps: http://www.mssqltips.com/sqlservertip/1411/verbose-sql-server-agent-logging/ – mellamokb Apr 04 '12 at 12:15
  • Tell us more about those jobs. Are those VBScripts? Can you show us the source? Maybe there is a bug that causes them to enter endless loop? – surfen Apr 04 '12 at 12:22

1 Answers1

0

My guess is that the page you are displaying in the browser, [weblink]/ReplicateFile.asp, is erroring out on at least one of sessions.

The code

do Until IEObj.ReadyState=4
loop

will cause significant CPU usage while it is running. And, more importantly, if the browser never gets to ReadyState 4, this code will continue to run forever. Have two of three of these stuck, and you will see 100% CPU usage on your server.

Jeff Siver
  • 7,434
  • 30
  • 32
  • This does seem to be the issue in the script, I worked out how to prevent it from completely locking up once I stop a job, that was down to the process IE which was created by the script was still running and therefore, any new jobs couldn't create their version of IE. I am looking into a way of error checking that loop or to add a time limit on it, but I never code in VBScript, only ever used C#. – ItWontWork Apr 04 '12 at 14:16