How It Works: The SQLAgent Log File


I am still working to resolve the customers problem but during my investigation I briefly looked a the SQLAgent logging format and thought you all might like some of these details.

From: Robert Dorr
Sent: Monday, May 24, 2010 9:47 AM
Subject: RE: SPID in SLEEP state indefinitely

 

The error itself is from SQLAgent while calculating the next scheduled execution time.

 

IDS_POSSIBLE_DATE_CALC_SPIN                      “Warning [%ld]: Possible date calculation spin for Schedule %ld”

 

We are trying to find the next date and time that the scheduler will run.   In this case it is schedule #15 on your system.   As I look at this is would nice if it would spell out the Job in question so I will file a DCR to add more details to the message as well.

 

We will spin in this code calculating the date if

 

                Scheduler to run again

                AND Next Run Date < current date

                OR NextRunDate equal current date AND run time < current time

 

So it would help to see the SQLAgent log entries to see the logged date and time compared to the current setup for execution schedule #15

 

Reference Information

=============================================

Common SQLAgent Log format

 

Date time – (+|?|!)  [Resource Id] <<Message Text>>

 

        INFORMATION            ‘?’

        WARNING                ‘+’

        ERROR                  ‘!’

 

I opened up the sqlagent.rll as a resource DLL and you can see the resource id and format string that matches the inquiry.

clip_image002

OR

Date time – (+|?|!) <<Message Text>>

=========================================

Sent: Saturday, May 22, 2010 11:42 AM
Subject: RE: SPID in SLEEP state indefinetely

 

I am seeing a warning message in the Sql agent’s logs “[191] Warning [2]: Possible date calculation spin for Schedule 15”, but it said to be fixed in SQL 2000 SP4 (http://support.microsoft.com/kb/295378)

 

Any idea of it occurring on SQL 2008 SP1 and how to fix it? Is there any relation of this issue to forever waiting SPIDs?

Bob Dorr – Principal SQL Server Escalation Engineer

Comments (3)

  1. Pradeep Adiga says:

    Hi,

    Did you found out the root cause of this issue? Curious to know.

  2. Charley Hanania says:

    Hi Robert, any resolution/update to this post? I have a customer with this on SQL Server 2008.

    Thanks 🙂

  3. Vikram Gupta says:

    Hi,

    I am facing this issue in SQL Server 2008 R2 RTM as well, can you suggest resolution for it. I am currently planning to recreate the job which might resolve the issue.

    Regards,

    Vikram Gupta

Skip to main content