SQL Server Job Stops. Please Take a look

[rajeessh]

I have a job that runs for every minute in SQL Server.
It runs a stored procedure from my database.
When i try to change the system time so that the job will
produce me the desired results the job stops for a period
of time which is uncertain for my knowledge.

Then i tried changing the system date and then querying
getdate immediately from SQL Server. It takes about 2 minutes for SQL server to realise my date changes of the
server as for as now.
Can anyone throw me some light on this issue.
We need to get this up by monday or we are screwed.
Pl help

 

[Corran007]

I am not sure i understand. So you have a job that runs a stored proc every min. Why are you changing the system time to produce your desired results? Systime should be set to as close to the actually time as possible then not changed. So your asking why a job that depends on the systime stops working for a short bit after you change the system time. Its proably confused for a bit you said it takes a few mins for getdate to give the proper time. Sqlserver doesnt like being changed like that much. I would avoid changing the system time.

If your having to change your the systime to get your proc to produce the desired result, change your proc.

 

[tlbroadbent]

Once the time is set on the server, it may take a few seconds or even minutes for all the services to synch to the server time.

I don't understand what changing the sytem time has to do with getting the correct result from your stored procedure. Once the time is set, the job should run as scheduled. However, if the job doesn't finish in 1 minute, the SQL Agent will not start the job again. Is it possible that the job isn't finishing within a minute?

Please clarify the problem. Explain what you want the SP to do and what it is doing that is incorrect. Depending on the SQL Agent to start a job every mnute may not be reasonable. Perhaps there is an alternative way to get the result you need. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[rajeessh]

Thanx for faster response.
Sorry that i have confused you
We are developing a software to track service industries and they concentrate on preventive maintainance of the equipments. So we have a job that runs and schedule the PM calls necessary for equipments.This job runs in customer site daily once and this goes smooth as far
as customers are concerned.

When our marketing guys go on demo with their laptop they would like to simulate this scenario by making a change of their date and wait for sometime and hence show customers attending the demo the efficiency of our software. When he tried to do it when system date is changed the job that was running was stopped and after some time resumed.

But the job gets executed in a minute after resumption.
Even we tried scheduling job for every five minutes but our marketing team needed a sudden change.

When probing into the problem the SQL server was picking the date change only after 50 secs in my machine. Why this happen and why job execution stops after the date change even when job Status is not executing.
The issue is critical and we must respond in not less than a day or two.

I will be out of station and hence my colleague rajeshkhanna who has marked this thread will provide the follow throughs and will seek advices.Kindly help

Thanx in advance
Srinivasan Rajesh