none
SQL Server Event ID 17052: SQLServerAgent has terminated unexpectedly.

    Question

  • Hello All,

          We are getting EventID17052 in one of our production server from last 3 months. SQL Server Agent is restarting on its own. As per threads about same issue I dig into the Jobs and processes running at that time in Server and I have found one job is causing this issue and Agent crash is happening same time that job is scheduled but not everyday. Server is running with SQL Server 2005 SP2 (Microsoft SQL Server 2005 - 9.00.3042.00 (X64)   Feb 10 2007 00:59:02   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1) ).  Please let me  know is there any fix for this error and also let me know SP3 upgrade to SQL Server 2005 will help for it or not?

    I am also providing full error details from event viewer.

    The description for Event ID 17052 from source MSSQLSERVER cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

    If the event originated on another computer, the display information had to be saved with the event.

    The following information was included with the event:

    SQLServerAgent Monitor: SQLServerAgent has terminated unexpectedly.

    the message resource is present but the message is not found in the string/message table

    - System

       
    - Provider
          [ Name] MSSQLSERVER
       
    - EventID 17052
          [ Qualifiers] 16384
       
      Level 2
       
      Task 2
       
      Keywords 0x80000000000000
       
    - TimeCreated
          [ SystemTime] 2010-09-05T10:21:31.000Z
       
      EventRecordID 137838
       
      Channel Application
       
      Computer IPRDDB130.IKAPROD90.COM
       
      Security

    - EventData

        SQLServerAgent Monitor: SQLServerAgent has terminated unexpectedly.
        A80100000A0000001400000049005000520044004400420031003300300000000E0000006D00610073007400650072000000

    Binary data:

    In Words

    0000: 000001A8 0000000A 00000014 00500049
    0008: 00440052 00420044 00330031 00000030
    0010: 0000000E 0061006D 00740073 00720065
    0018: 0000

    In Bytes

    0000: A8 01 00 00 0A 00 00 00 ¨.......
    0008: 14 00 00 00 49 00 50 00 ....I.P.
    0010: 52 00 44 00 44 00 42 00 R.D.D.B.
    0018: 31 00 33 00 30 00 00 00 1.3.0...
    0020: 0E 00 00 00 6D 00 61 00 ....m.a.
    0028: 73 00 74 00 65 00 72 00 s.t.e.r.
    0030: 00 00 ..

    Let me know if I missed any information to provide. Your response will be very helpful to solve this problem.

     

    Thanks

    Madhu


    Pradeep
    Tuesday, September 7, 2010 3:18 PM

Answers

  • Hi MLPradeep ,

     

    From you post, we figured out that you have found one job is causing this issue and Agent crash is happening same time that job is scheduled. Since then, would you please kindly let us know what job it is?

    According to the error message below, it looks like there is some problem in the driver installation (resource?), the driver seemed to load fine, but it failed in the very first ODBC call from the ODBC admin tool - SQLAllocEnv.

     

           2010-09-05 06:21:47 - ! [364] The Messenger service has not been started - NetSend notifications will not be sent

           2010-09-05 06:21:47 - ? [129] SQLSERVERAGENT starting under Windows NT service control

           2010-09-05 06:21:47 - + [260] Unable to start mail session (reason: No mail profile defined)

           2010-09-05 06:21:47 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect

           2010-09-08 00:08:45 - ? [130] SQLSERVERAGENT stopping because of Windows shutdown...

           2010-09-08 00:08:46 - ! [165] ODBC Error: 0, Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]

           2010-09-08 00:08:46 - ! [382] Logon to server '(local)' failed (SaveAllSchedules)

           2010-09-08 00:08:49 - ! [165] ODBC Error: 0, Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]

           2010-09-08 00:08:49 - ! [382] Logon to server '(local)' failed (DisableAgentXPs)

           2010-09-08 00:08:50 - ? [098] SQLServerAgent terminated (normally)

     

     

    For further investigation, would you please provide the following error information:

     

          1. SQL MPS Reports

          2. Dump File generated in the LOG Directory, to generate a Full Dump in the event of SQL Server Agent stopping unexpectedly, we would need to follow the below steps:

                1> Please navigate to the registry key:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceID>\SQLServerAgent

                2> Create a REG_DWORD. Name it “FullDump” and set it to 1.

     

          3. SQL Server Agent Logs from the Log Directory.

           4. Windows Event log

    You can send the logs to sqltnsp AT Microsoft.com (Please replace AT with @) with this format:

     

    #Sample

     

    Subject: MSDN/TechNet forum question: <Thread Title>

    Attachments: <files>

    Hi Grace,

    ThreadURL:<URL>

    <Additional information>

     

    #End

     

    For more information about how to View and Read SQL Server Setup Log Files, please see http://msdn.microsoft.com/en-us/library/ms143702.aspx

     

     

    Information about the SQL Server edition of the MPS Reporting tool: http://support.microsoft.com/kb/883724

     

    If there are more questions, please feel free to ask.

     

    Thanks,

    Grace


    Please kindly mark the answer if it is a workaround. Thanks a lot *^_^*
    Thursday, September 9, 2010 9:24 AM

All replies

  • Does SQLAgent.out provide any details?

     




    Pradeep Adiga
    My blog: http://www.sqldbadiaries.com

    Recent posts on my blog
    Tuesday, September 7, 2010 4:47 PM
  • Pradeep,

        Thanks for your response. SQLAgent.OUT has following content.

     

    2010-09-05 06:21:32 - ? [100] Microsoft SQLServerAgent version 9.00.3042.00 ((Unknown) unicode retail build) : Process ID 11864
    2010-09-05 06:21:32 - ? [101] SQL Server IPRDDB130 version 9.00.3042 (0 connection limit)
    2010-09-05 06:21:32 - ? [102] SQL Server ODBC driver version 9.00.3042
    2010-09-05 06:21:32 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
    2010-09-05 06:21:32 - ? [310] 8 processor(s) and 16374 MB RAM detected
    2010-09-05 06:21:32 - ? [339] Local computer is IPRDDB130 running Windows NT 6.0 (6001) Service Pack 1
    2010-09-05 06:21:32 - ? [432] There are 11 subsystems in the subsystems cache
    2010-09-05 06:21:47 - ! [364] The Messenger service has not been started - NetSend notifications will not be sent
    2010-09-05 06:21:47 - ? [129] SQLSERVERAGENT starting under Windows NT service control
    2010-09-05 06:21:47 - + [260] Unable to start mail session (reason: No mail profile defined)
    2010-09-05 06:21:47 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect

    Thanks

     


    Pradeep
    Tuesday, September 7, 2010 7:09 PM
  • Hi LMPradeep,

    As per my suggestion, I will recommend you to check SQL Server Error Logs as well and see if we are getting some errors during this time or not.

    Further as you mentioned that as per your troubleshooting you found that one job which is scheduled at that time (some times) causes this issue. What I think is it might be because of that job something goes wrong and exception or some other problem comes into place. That is why I recommend to check SQL Error Logs.

    If nothing is there then I recommend you to open case with Microsoft CSS as they might take a dump during the problematic time and check what is happening.

     


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/
    Wednesday, September 8, 2010 4:11 AM
  • You gave us the output from SQLagent.out .Post the output of the sqlagent.1 log please.
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Wednesday, September 8, 2010 4:37 AM
  • Gursethi,

          Thanks for your response. I checked errorlog messages also for Error cause.But I don't find any information which is helpful. 

    Thanks

     


    Pradeep
    Wednesday, September 8, 2010 9:19 PM
  • Abhay,

         Thanks for your response. Here is SQLAGENT1.OUT messages. Please let me know if you need anymore information I can provide.

     

    2010-09-05 06:21:32 - ? [100] Microsoft SQLServerAgent version 9.00.3042.00 ((Unknown) unicode retail build) : Process ID 11864
    2010-09-05 06:21:32 - ? [101] SQL Server IPRDDB130 version 9.00.3042 (0 connection limit)
    2010-09-05 06:21:32 - ? [102] SQL Server ODBC driver version 9.00.3042
    2010-09-05 06:21:32 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
    2010-09-05 06:21:32 - ? [310] 8 processor(s) and 16374 MB RAM detected
    2010-09-05 06:21:32 - ? [339] Local computer is IPRDDB130 running Windows NT 6.0 (6001) Service Pack 1
    2010-09-05 06:21:32 - ? [432] There are 11 subsystems in the subsystems cache
    2010-09-05 06:21:47 - ! [364] The Messenger service has not been started - NetSend notifications will not be sent
    2010-09-05 06:21:47 - ? [129] SQLSERVERAGENT starting under Windows NT service control
    2010-09-05 06:21:47 - + [260] Unable to start mail session (reason: No mail profile defined)
    2010-09-05 06:21:47 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
    2010-09-08 00:08:45 - ? [130] SQLSERVERAGENT stopping because of Windows shutdown...
    2010-09-08 00:08:46 - ! [165] ODBC Error: 0, Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
    2010-09-08 00:08:46 - ! [382] Logon to server '(local)' failed (SaveAllSchedules)
    2010-09-08 00:08:49 - ! [165] ODBC Error: 0, Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
    2010-09-08 00:08:49 - ! [382] Logon to server '(local)' failed (DisableAgentXPs)
    2010-09-08 00:08:50 - ? [098] SQLServerAgent terminated (normally)

    FYI,  Last night we rebooted the server to complete maintenance.

     


    Thanks

    Madhu Pradeep


    Pradeep
    Wednesday, September 8, 2010 9:22 PM
  • Since you rebooted the server the errors might have moved to sqlagent.2 now ....anyway , find the agent log which has the time when you got the error in the system/app logs ....but I am not sure if the SQL error time and the System log time has any gap .

    Anyway, can you send me all the [agent logs , sql server logs , aplication & system logs (saved as text tab-delimited) ] zipped to abhay_c@hotmail.com . i will have a look today ..

    Regards


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Thursday, September 9, 2010 4:07 AM
  • Hi MLPradeep ,

     

    From you post, we figured out that you have found one job is causing this issue and Agent crash is happening same time that job is scheduled. Since then, would you please kindly let us know what job it is?

    According to the error message below, it looks like there is some problem in the driver installation (resource?), the driver seemed to load fine, but it failed in the very first ODBC call from the ODBC admin tool - SQLAllocEnv.

     

           2010-09-05 06:21:47 - ! [364] The Messenger service has not been started - NetSend notifications will not be sent

           2010-09-05 06:21:47 - ? [129] SQLSERVERAGENT starting under Windows NT service control

           2010-09-05 06:21:47 - + [260] Unable to start mail session (reason: No mail profile defined)

           2010-09-05 06:21:47 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect

           2010-09-08 00:08:45 - ? [130] SQLSERVERAGENT stopping because of Windows shutdown...

           2010-09-08 00:08:46 - ! [165] ODBC Error: 0, Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]

           2010-09-08 00:08:46 - ! [382] Logon to server '(local)' failed (SaveAllSchedules)

           2010-09-08 00:08:49 - ! [165] ODBC Error: 0, Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]

           2010-09-08 00:08:49 - ! [382] Logon to server '(local)' failed (DisableAgentXPs)

           2010-09-08 00:08:50 - ? [098] SQLServerAgent terminated (normally)

     

     

    For further investigation, would you please provide the following error information:

     

          1. SQL MPS Reports

          2. Dump File generated in the LOG Directory, to generate a Full Dump in the event of SQL Server Agent stopping unexpectedly, we would need to follow the below steps:

                1> Please navigate to the registry key:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceID>\SQLServerAgent

                2> Create a REG_DWORD. Name it “FullDump” and set it to 1.

     

          3. SQL Server Agent Logs from the Log Directory.

           4. Windows Event log

    You can send the logs to sqltnsp AT Microsoft.com (Please replace AT with @) with this format:

     

    #Sample

     

    Subject: MSDN/TechNet forum question: <Thread Title>

    Attachments: <files>

    Hi Grace,

    ThreadURL:<URL>

    <Additional information>

     

    #End

     

    For more information about how to View and Read SQL Server Setup Log Files, please see http://msdn.microsoft.com/en-us/library/ms143702.aspx

     

     

    Information about the SQL Server edition of the MPS Reporting tool: http://support.microsoft.com/kb/883724

     

    If there are more questions, please feel free to ask.

     

    Thanks,

    Grace


    Please kindly mark the answer if it is a workaround. Thanks a lot *^_^*
    Thursday, September 9, 2010 9:24 AM
  • Hi Pradeep ,

    what I found was that this happens at a fixed time i.e. 6:21 AM in the morning .

    7/5/2010 6:21:23 AM
    8/8/2010 6:21:10 AM
    8/23/2010 6:21:00 AM
    9/5/2010 6:21:31 AM

    also there are these errors always with agent termination :Faulting application cscript.exe

    Looks like something related to VB is causing this error .and this might be causing this issue ...

    Regards


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Monday, September 13, 2010 7:12 PM