How It Works: The SQLAgent Log File

Published Jan 15 2019 12:58 PM 209 Views
Microsoft
First published on MSDN on May 27, 2010

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

%3CLINGO-SUB%20id%3D%22lingo-sub-316096%22%20slang%3D%22en-US%22%3EHow%20It%20Works%3A%20The%20SQLAgent%20Log%20File%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-316096%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20May%2027%2C%202010%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EI%20am%20still%20working%20to%20resolve%20the%20customers%20problem%20but%20during%20my%20investigation%20I%20briefly%20looked%20a%20the%20SQLAgent%20logging%20format%20and%20thought%20you%20all%20might%20like%20some%20of%20these%20details.%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3EFrom%3A%20%3C%2FB%3E%20Robert%20Dorr%20%3CBR%20%2F%3E%20%3CB%3E%20Sent%3A%20%3C%2FB%3E%20Monday%2C%20May%2024%2C%202010%209%3A47%20AM%20%3CBR%20%2F%3E%20%3CB%3E%20Subject%3A%20%3C%2FB%3E%20RE%3A%20SPID%20in%20SLEEP%20state%20indefinitely%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EThe%20error%20itself%20is%20from%20SQLAgent%20while%20calculating%20the%20next%20scheduled%20execution%20time.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EIDS_POSSIBLE_DATE_CALC_SPIN%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%22Warning%20%5B%25ld%5D%3A%20Possible%20date%20calculation%20spin%20for%20Schedule%20%25ld%22%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EWe%20are%20trying%20to%20find%20the%20next%20date%20and%20time%20that%20the%20scheduler%20will%20run.%26nbsp%3B%26nbsp%3B%20In%20this%20case%20it%20is%20schedule%20%2315%20on%20your%20system.%26nbsp%3B%26nbsp%3B%20As%20I%20look%20at%20this%20is%20would%20nice%20if%20it%20would%20spell%20out%20the%20Job%20in%20question%20so%20I%20will%20file%20a%20DCR%20to%20add%20more%20details%20to%20the%20message%20as%20well.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EWe%20will%20spin%20in%20this%20code%20calculating%20the%20date%20if%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EScheduler%20to%20run%20again%3C%2FP%3E%0A%20%20%3CP%3EAND%20Next%20Run%20Date%20%26lt%3B%20current%20date%3C%2FP%3E%0A%20%20%3CP%3EOR%20NextRunDate%20equal%20current%20date%20AND%20run%20time%20%26lt%3B%20current%20time%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CEM%3E%20So%20it%20would%20help%20to%20see%20the%20SQLAgent%20log%20entries%20to%20see%20the%20logged%20date%20and%20time%20compared%20to%20the%20current%20setup%20for%20execution%20schedule%20%2315%20%3C%2FEM%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CSTRONG%3E%20Reference%20Information%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%0A%20%20%3CP%3ECommon%20SQLAgent%20Log%20format%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EDate%20time%20%E2%80%93%20(%2B%7C%3F%7C!)%26nbsp%3B%20%5BResource%20Id%5D%20%26lt%3B%3CMESSAGE%20text%3D%22%22%3E%26gt%3B%3C%2FMESSAGE%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EINFORMATION%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'%3F'%3C%2FP%3E%0A%20%20%3CP%3EWARNING%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'%2B'%3C%2FP%3E%0A%20%20%3CP%3EERROR%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'!'%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EI%20opened%20up%20the%20sqlagent.rll%20as%20a%20resource%20DLL%20and%20you%20can%20see%20the%20resource%20id%20and%20format%20string%20that%20matches%20the%20inquiry.%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F66437i857DEE1CFEDA9AD5%22%20%2F%3E%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3EOR%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CP%3EDate%20time%20%E2%80%93%20(%2B%7C%3F%7C!)%20%26lt%3B%3CMESSAGE%20text%3D%22%22%3E%26gt%3B%3C%2FMESSAGE%3E%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3ESent%3A%20%3C%2FB%3E%20Saturday%2C%20May%2022%2C%202010%2011%3A42%20AM%20%3CBR%20%2F%3E%20%3CB%3E%20Subject%3A%20%3C%2FB%3E%20RE%3A%20SPID%20in%20SLEEP%20state%20indefinetely%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EI%20am%20seeing%20a%20warning%20message%20in%20the%20Sql%20agent%E2%80%99s%20logs%20%E2%80%9C%5B191%5D%20Warning%20%5B2%5D%3A%20Possible%20date%20calculation%20spin%20for%20Schedule%2015%E2%80%9D%2C%20but%20it%20said%20to%20be%20fixed%20in%20SQL%202000%20SP4%20(%20%3CA%20href%3D%22http%3A%2F%2Fsupport.microsoft.com%2Fkb%2F295378%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Fsupport.microsoft.com%2Fkb%2F295378%20%3C%2FA%3E%20)%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EAny%20idea%20of%20it%20occurring%20on%20SQL%202008%20SP1%20and%20how%20to%20fix%20it%3F%20Is%20there%20any%20relation%20of%20this%20issue%20to%20forever%20waiting%20SPIDs%3F%3C%2FP%3E%0A%20%20%3CP%3EBob%20Dorr%20-%20Principal%20SQL%20Server%20Escalation%20Engineer%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-316096%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20May%2027%2C%202010%20I%20am%20still%20working%20to%20resolve%20the%20customers%20problem%20but%20during%20my%20investigation%20I%20briefly%20looked%20a%20the%20SQLAgent%20logging%20format%20and%20thought%20you%20all%20might%20like%20some%20of%20these%20details.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-316096%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQL%20Agent%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 12:58 PM
Updated by: