none
SQL Server Scheduled Job fails RRS feed

  • Question

  • Recently our DPM server has been logging the following SQL job error.  This is the DPM dedicated SQL instance and the only SQL installed on this server.  There are no maintenance plans defined, and I couldn't find anything in Task Manager that looked like it related to this.  Can anyone point me to where this job is coming from and why it suddenly started failing?

    Log Name:      Application
    Source:        SQLAgent$MSDPM2012
    Date:          1/21/2014 7:30:04 AM
    Event ID:      208
    Task Category: Job Engine
    Level:         Warning
    Keywords:      Classic
    User:          N/A
    Computer:      DPM.internal.com
    Description:
    SQL Server Scheduled Job 'd17d4bf4-ec66-48f2-b4ba-1883dadf5216' (0x5DFD19CBCBB25740B8F242BADA1FA20C) - Status: Failed - Invoked on: 2014-01-21 07:30:00 - Message: The job failed.  The Job was invoked by Schedule 376 (Schedule 1).  The last step to run was step 1 (Default JobStep).
    Event Xml:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="SQLAgent$MSDPM2012" />
        <EventID Qualifiers="16384">208</EventID>
        <Level>3</Level>
        <Task>3</Task>
        <Keywords>0x80000000000000</Keywords>
        <TimeCreated SystemTime="2014-01-21T13:30:04.000000000Z" />
        <EventRecordID>11403</EventRecordID>
        <Channel>Application</Channel>
        <Computer>DPM.internal.com</Computer>
        <Security />
      </System>
      <EventData>
        <Data>d17d4bf4-ec66-48f2-b4ba-1883dadf5216</Data>
        <Data>0x5DFD19CBCBB25740B8F242BADA1FA20C</Data>
        <Data>Failed</Data>
        <Data>2014-01-21 07:30:00</Data>
        <Data>The job failed.  The Job was invoked by Schedule 376 (Schedule 1).  The last step to run was step 1 (Default JobStep).</Data>
      </EventData>
    </Event>

    Thanks,
    Joe

    Tuesday, January 21, 2014 1:54 PM

Answers

  • Hi,

    Ok - then go ahead and disable those two offending jobs.

    Under SQL Server Agent - Jobs, right-click the offending job and disable it.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by JWD2 Tuesday, February 18, 2014 3:34 PM
    Thursday, February 13, 2014 10:05 PM
    Moderator

All replies

  • Hi,

    Is the remote SQL server hosting DPM 2012 or 2012 Sp1 DB's and you did upgrade or install of DPM 2012 R2 that uses the same SQL Server ?


    If so DPM2012 R2 SQL Prep overwrites the triggerjob.exe path on remote SQL server for DPM2012 Sp1 and changes the path from %DPMInstall%\Program files\Microsoft Data Protection Manager\DPM2012\SQLPrep to %DPMInstall%\Program files\Microsoft Data Protection Manager\DPM2012R2\SQLPrep. This causes SQL not to be able to find the triggerjob.exe for DPM2012 Sp1 jobs for scheduled jobs to run.

    Solution is to re-run the SQLPREP from DPM 2012 Sp1 installation media.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.


    Tuesday, January 21, 2014 4:50 PM
    Moderator
  • Mike,

    Sorry for the delay in responding.  I think your diagnosis is correct, as I did attempt to upgrade DPM 2012 SP1 to R2 before finding that Windows 2003 is no longer supported.  So I went back to SP1.  My configuration is using the default DPM SQL instance on the local server.  When I tried to run SQLPrepInstall_x64.exe, I got a dialog box saying to run setup.exe.  When I ran setup I found an option for "DPM Remote SQL Prep" but I received an error that "You cannot install DPM Support Files on a computer that has Data Protection Manager."  Is there another way to approach this?

    Thanks,

    Joe

    Tuesday, January 28, 2014 3:43 PM
  • Hi,

    SQLPrep is only required for remote sql server hosting DPMDB.

    If you re-installed DPM, most likely the path to triggerjob.exe changed.  Look at the properties of a SQL job and look at the path to triggerjob.exe, then make that path and copy the files into it from the new installations bin folder.  Also make sure NTFS permissions are correct and match the new location permissions.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, January 28, 2014 10:46 PM
    Moderator
  • Mike,

    The two jobs in question point to the correct location for triggerjob.exe.  When I manually run the jobs, they fail just like when they run on the schedule.  There is nothing in the errorlog file or any of the SQL logs.  When I view the Job History, I see the following.  Does this tell you anything?

    Thanks!
    Joe

    Date  1/29/2014 7:30:00 AM
    Log  Job History (01dfc617-5764-44bc-9b86-e96f64074720)

    Step ID  1
    Server  DPMVM\MSDPM2012
    Job Name  01dfc617-5764-44bc-9b86-e96f64074720
    Step Name  Default JobStep
    Duration  00:00:02
    Sql Severity  0
    Sql Message ID  0
    Operator Emailed  
    Operator Net sent  
    Operator Paged  
    Retries Attempted  0

    Message
    Executed as user: DPMVM\MICROSOFT$DPM$Acct. The step did not generate any output.  Process Exit Code -1.  The step failed.

    Wednesday, January 29, 2014 1:35 PM
  • HI,

    If you copy / paste the job step details into an administrative command prompt, does it run or get an error ?

    IE: C:\>"C:\Program Files\Microsoft DPM\DPM\bin\TriggerJob.exe" 3d702296-0d68-4e7d-af27-ac7e8a64eac4 93b7d948-5819-4596-b805-2d70e7e268e2 DPMLIB1.contoso.com


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Monday, February 3, 2014 6:00 PM
    Moderator
  • Mike,

    When I run the job in a command line, nothing was displayed and no errors are returned in the command shell.  No errors are logged in the event viewer.  I could also find no errors or any reference to the manual run of the job in the SQL logs.  I'm so confused it feels like Monday again.

    Thanks,
    Joe

    Tuesday, February 4, 2014 4:07 PM
  • Hi,

    OK - it seems that we have scheduled jobs but the job definition is no longer valid.

    Try running these two queries and see what we get back.

    1) make a backup of the DPMDB
    2) Start SQL management studio and connect to DPM instance, then start a new query against the DPMDB.
    3) Paste the first two queries into the query window and execute.

    See if Isdeleted = 0 here.

    select ScheduleId, JobDefinitionId, IsDeleted from tbl_SCH_ScheduleDefinition where ScheduleId in
    (
       'd17d4bf4-ec66-48f2-b4ba-1883dadf5216'
    )
    


    See if isdeleted = 1 here.

    select JobDefinitionId, FriendlyName, servername, jd.IsDeleted from tbl_JM_JobDefinition jd
    join tbl_IM_ProtectedGroup pg
    on jd.ProtectedGroupId = pg.ProtectedGroupId
    join tbl_AM_Server srv
    on jd.ServerId = srv.ServerId
    where JobDefinitionId in
    (
       select JobDefinitionId from tbl_SCH_ScheduleDefinition where ScheduleId in
       (
          'd17d4bf4-ec66-48f2-b4ba-1883dadf5216'
       )
    )
    

    If that is the problem, then the below query should fix it.

    update tbl_SCH_ScheduleDefinition
    set IsDeleted = 1
    where ScheduleId in 
    (
         select ScheduleId 
         from tbl_JM_JobDefinition jd
         join tbl_SCH_ScheduleDefinition sd
         on jd.JobDefinitionId = sd.JobDefinitionId
         where jd.JobDefinitionId in 
         (
               select  JobDefinitionId 
               from tbl_SCH_ScheduleDefinition 
               where IsDeleted = 0
         )
         and jd.IsDeleted = 1
    )
    


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, February 4, 2014 4:47 PM
    Moderator
  • Mike,

    Not sure how to read the results of the queries.  They didn't fail but it doesn't appear to have produced any results.  I've included two screen captures below.  I did not run the third query yet.

    Thanks!
    Joe

    Wednesday, February 5, 2014 1:29 PM
  • On a possibly related note, when I run a backup from Management Studio, I get an error when I start to browse for the backup destination.  It says:

    I found the directory structure was correct except that the Backup directory did not exist.  I created that directory but still get this error, even after restarting Management Studio.  Don't know if this is in any way related to the other errors.

    Joe

    Wednesday, February 5, 2014 1:33 PM
  • Ok - took a different approach to see if any jobs that are scheduled have deleted job definitions. If this returns any entries, then run the 3rd script from previous post.

    select * from tbl_SCH_ScheduleDefinition
    where ScheduleId in 
    (
         select ScheduleId 
         from tbl_JM_JobDefinition jd
         join tbl_SCH_ScheduleDefinition sd
         on jd.JobDefinitionId = sd.JobDefinitionId
         where jd.JobDefinitionId in 
         (
               select  JobDefinitionId 
               from tbl_SCH_ScheduleDefinition 
               where IsDeleted = 0
         )
         and jd.IsDeleted = 1
    )


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, February 5, 2014 10:19 PM
    Moderator
  • Mike,

    Sorry for the delay in responding.  I've been away from this site for the past week.  I just ran your latest script and it's not liking the first line:

    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'tbl_SCH_ScheduleDefinition'.

    It did not error out on this object name in your original scripts.  Not sure what is going on.

    Joe

    Tuesday, February 11, 2014 2:07 PM
  • HI,

    You need to make sure the database selected is the DPM database and not master.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, February 11, 2014 3:02 PM
    Moderator
  • Duh.  Please excuse me while I give myself a dozen lashes with a stiff noodle. 

    I ran the script and nothing was returned.  "(0 row(s) affected)"

    Any other ideas?  I greatly appreciate your help on this.

    Joe

    Wednesday, February 12, 2014 1:15 PM
  • Hi,

    Ok - let's re-scope the issue - are other dpm jobs running and you just get nag messages about two jobs - or no DPM jobs run and result in the errors ?

    See if the below Query lists the jobs logging the error.

    use DPMDB --change DB name accordingly
    select 
          sche.ScheduleId as 'SQL agent Schedule Job Name', 
          sche.JobDefinitionId,
          prot.FriendlyName as 'Protection Group' ,
         case 
                when jobd.type = 'C9B259D2-6402-486D-8E36-C6C1ADAE0912' then 'Maintenance job that runs @ midnight'
                when jobd.Type = '3D859D8C-D0BB-4142-8696-C0D215203E0D' then 'Synchronization (file/volume) / Express Full (application)'
                when jobd.Type = '84021B5E-B4DC-9B27-2B7E-3B99BB1225FF' then 'Volume/Share/System State Recovery Point'
                when jobd.Type = '913afd2d-ed74-47bd-b7ea-d42055e5c2f1' then 'Backup to tape (D-T)'
                when jobd.Type = 'B5A3D25C-8EB2-4032-9428-C852DA5CE2C5' then 'Backup to tape (D-D-T)'
                when jobd.Type = 'C4CAE2F7-F068-4A37-914E-9F02991868DA' then 'Consistency Check'
    			when jobd.Type = '5ECC82D0-3475-4E81-8ADD-55B1C1D23DB1' then 'Sharepoint catalog generation'
    			when jobd.Type = '6E7C76F4-A832-4418-A772-8E58FD7466CB' then 'Azure Online backup'
         end
           as Operation
    from tbl_SCH_ScheduleDefinition sche
    left join dbo.tbl_JM_JobDefinition jobd
    join tbl_IM_ProtectedGroup prot
    on jobd.ProtectedGroupId = prot.ProtectedGroupId
    on sche.JobDefinitionId = jobd.JobDefinitionId
    where sche.IsDeleted = '0' and jobd.ProtectedGroupId is not null
    order by prot.FriendlyName
    


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, February 12, 2014 4:08 PM
    Moderator
  • Mike,

    There are a number of other jobs that run without errors.  I'm just facing two that don't want to run.  Your script produced 22 results (below) but the two problem jobs are not listed.

    Joe

    SQL agent Schedule Job Name JobDefinitionId Protection Group Operation
    5A04D881-EAF8-4EB5-8718-0E8E59B0F91B 14D6C077-2793-4EF4-88EC-1BB107C49566 Application Servers Synchronization (file/volume) / Express Full (application)
    3F883E2A-2FE1-4B41-952E-1E180A3E79A5 B27038CF-0365-40F2-A987-69732FF590FF Application Servers Synchronization (file/volume) / Express Full (application)
    A7F037EE-BD58-46F8-B28A-2FAC6A31780D B5F81D3D-A3ED-4984-AFF0-9A5C2E647F16 Application Servers Volume/Share/System State Recovery Point
    A0F07C72-12E4-4063-AF42-3A234F58B5DA B6BA0B58-FF37-4D53-AF2C-236CD5864A84 Application Servers Volume/Share/System State Recovery Point
    DAA14CEC-E6F7-463D-9753-40D036AA7027 AE74094E-3D68-41DB-A77F-E87046C42A86 Application Servers Maintenance job that runs @ midnight
    730FE0B5-CE37-4FF1-A0C2-4250AF91AB16 0DC54933-2A9C-46E6-AFCA-269BE089C8AF Application Servers Synchronization (file/volume) / Express Full (application)
    79EA4C62-631C-4F5C-AC4A-438F23562B78 245842B1-B8DD-4A9D-B738-B34A2F8C2E00 Application Servers Synchronization (file/volume) / Express Full (application)
    497EE843-0CAF-4C91-A17B-706F4FEFD468 FF749D39-B397-479E-8DA2-AD1BBC3A2BF2 Application Servers Volume/Share/System State Recovery Point
    AA48F2C5-FDFC-4693-B1D0-B4D28CFFDF9F 6812A27A-A113-4F81-8401-EB72CADE9E88 Application Servers Sharepoint catalog generation
    E4C09E8F-7A8E-4D26-A73C-E770D879BC60 4DE7E006-C04D-4BE4-9E0F-10AE0E7C6560 Application Servers Synchronization (file/volume) / Express Full (application)
    82C9F150-CF0F-4106-920F-EB44A9B1DD4B 89D298BC-EECD-4113-8587-E0AB7551B5E3 Application Servers Synchronization (file/volume) / Express Full (application)
    00AFF4C8-CB80-483C-94B2-F352E0FE993F 19706ACA-0353-46A8-BC13-6D63FFA3385E Application Servers Volume/Share/System State Recovery Point
    3E1535E9-76A9-45BD-9231-FC39553DF48F 4579E398-56A5-45AE-BB79-33FAFE9C57CD Application Servers Synchronization (file/volume) / Express Full (application)
    2F238631-61E6-4E2E-8281-08FBD65DB975 5905892E-22BF-49E6-B2E2-323C127C487B Domain Controllers Synchronization (file/volume) / Express Full (application)
    78207524-CE9E-4BA4-9D47-F0AFC9D345D2 81981B7F-E0C9-4AF4-AFAF-1D1AFBAF6097 Domain Controllers Maintenance job that runs @ midnight
    0643F8C7-8B71-4126-B192-263E680C7CFE 1C8563C4-1E74-4D34-9C6C-7FEE50E08DF4 Domain Controllers Synchronization (file/volume) / Express Full (application)
    7518CE03-2732-4409-97BE-122A5BABD7F1 CEBC332F-ADD3-4561-B7D9-74B138B1616B File/Print/Web Servers Volume/Share/System State Recovery Point
    3EED7E4A-B802-4E8E-982D-13F55AB9A9D6 6DAC1E1F-4F2C-46F9-9CFD-A3DA1058455C File/Print/Web Servers Synchronization (file/volume) / Express Full (application)
    F4F607AC-BED2-4AA8-92FB-425BDAC151AF 70E4ACF1-20D1-4D33-BA39-3116CEC95DC9 File/Print/Web Servers Volume/Share/System State Recovery Point
    B44A18D6-355B-49A0-8634-7A8042AED17C D28B889C-903E-4501-9592-1E8686D46C78 File/Print/Web Servers Synchronization (file/volume) / Express Full (application)
    ECBF57E2-7358-4E42-89D5-AAB22F99DC66 3586CDB9-5E76-4C46-BCAC-08B0CF6D7D55 File/Print/Web Servers Maintenance job that runs @ midnight
    6D39CCEE-2B7A-4981-A39A-686AB79E14D7 5EBA6734-D95C-40A2-AFEA-FE30FE004A1C File/Print/Web Servers Synchronization (file/volume) / Express Full (application)

    Thursday, February 13, 2014 1:18 PM
  • Hi,

    Ok - then go ahead and disable those two offending jobs.

    Under SQL Server Agent - Jobs, right-click the offending job and disable it.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by JWD2 Tuesday, February 18, 2014 3:34 PM
    Thursday, February 13, 2014 10:05 PM
    Moderator
  • Done.  I kept an eye on the logs to ensure I didn't miss anything and the errors did not appear again.  Thank you very much for all your help on this one.  I may have ended up in the same place on my own but not with the same level of confidence that everything was checked in advance.

    Thanks!!

    Joe

    Tuesday, February 18, 2014 3:34 PM
  • Ive been having a similar problem with DPM and SQL. Jobs will randomly start to fail. What i did to fix this originallywas to open the SQL management studio, drill down to SQL Server Agent and open the properties of the failing job. From there I would click on Steps and then edit. I would notice that path to triggerjob.exe did not have quotes around it. It would say C:\Program Files\Microsoft System Center 2012 R2\DPM\DPM\bin\TriggerJob.exe not "C:\Program Files\Microsoft System Center 2012 R2\DPM\DPM\bin\TriggerJob.exe" As soon as i put the quotes around the path the jobs start to function normally but then around sometime after midnight something happens and the paths get re-written to C:\Program Files\Microsoft System Center 2012 R2\DPM\DPM\bin\TriggerJob.exe (notice the missing quotes). How can i fix this. I know the problem is due to the missing quotes but how can i prevent these from getting stripped off?
    Monday, November 9, 2015 2:01 PM
  • I m having this exact same problem. The jobs will work after I correct the path but then sometime around midnight the jobs start to fail again. I'll investigate the failing job and notice the path is missing quotes.
    Monday, November 9, 2015 2:09 PM
  • Hi all for the server we have was a permissions issue on the backup drive, so this event is not that informative not even a hint of the actual root cause, this event is more like generic one


    • Edited by E Sa Wednesday, April 25, 2018 7:02 AM
    Wednesday, April 25, 2018 7:01 AM