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.
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