SQL Server Performance

Package failed but the scheduled Job failed...

Discussion in 'General DBA Questions' started by ramkumar.mu, Jan 22, 2007.

  1. ramkumar.mu New Member

    We have a scheduled job that calls a package. At times this job fails but not at all times. but the package runs successfully. when we checked with the error in the event viewer, it showed a "General network failure" error. Any idea what it could be?

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  2. MohammedU New Member

    What the package is doing? Is it accessing some other server?
    Add the output file in the job to get complete failure message...


    Mohammed U.
  3. ramkumar.mu New Member

    Yes, The package loads data from some other server. But the package completes successfully. only the scheduled job fails.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  4. ramkumar.mu New Member

    The Failure message in the job is...

    Description : MSHSRMNSUKP0056 (SQLSERVERAGENT[208]) - Action=Please see Ops Doc - Section 3.3,Msg=SQL Server Scheduled Job 'dmPkgExtractSupplierProductStoreDayFactWeekly' (0x9B9B951C2239BC4AB84D0BF1A38E3875) - Status: Failed - Invoked on: 2007-01-21 04:19:00 - Message: The job failed. The Job was invoked by Schedule 113 (dmPkgExtractSupplierProductStoreDayFactWeekly2). The last step to run was step 1 (dmPkgExtractSupplierProductStoreDayFactWeekly).

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  5. ramkumar.mu New Member

    This error is from job history...

    Executed as user: MNSUKy0124052. The step did not generate any output. Process Exit Code -1073741819. The step failed.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  6. satya Moderator

    Can you check the access privileges for that user mnsuky0124052 on SQL Server and also ensure the SQLAgent has requried privileges to complete this schedule djob.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  7. ramkumar.mu New Member

    yes! i am very much sure about the access permissions. because the scheduled job doesnt fail always. it fails 1 in 10 times. not sure why it is so inconsistent.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  8. satya Moderator

    When it fails check what other jobs are scheduled or any other processes running at same time.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  9. dhilditch New Member

    We've had this in the past with dodgy network connections. You're running it at nighttime - 4am - and guess what - nighttime is when anyone that wants to pull a plug to do maintenance or whatever will do it. You could try changing the time you have the job scheduled for and see if that makes a difference, or if you can't do that you might want to BCP the data that you're loading out to file on the Source Server, then copy the file across using a reliable Copy program (one that automatically resumes the copy if the network connection drops/reconnects) and then perform the import from that file on the target server.

    I guess an other option for what is happening is that maybe your query is quite intensive on the remote server. Maybe another administrator uses that server at that time and sees the slowdown, finds the offending process and kills it - but most likely it's just network problems and can be resolved by one of the two options above.
  10. ramkumar.mu New Member

    Since we have to meet the SLA, we dont schedule any other job at this time...


    quote:Originally posted by satya

    When it fails check what other jobs are scheduled or any other processes running at same time.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  11. satya Moderator

    Check what Dave has mentioned above too, even we get such errors some times and we ask Network team to confirm there isn't such a blip in network. Also you can also restart the SQLAgent periodically as the contention with job history might have such downtrend of jobs performance within the server.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  12. ramkumar.mu New Member

    I guess we might not able to change the timing of the job since we will miss the SLA. The second option will not help as there are business logic involved that might involve lot of coding change. Any more ideas???


    quote:Originally posted by dhilditch

    We've had this in the past with dodgy network connections. You're running it at nighttime - 4am - and guess what - nighttime is when anyone that wants to pull a plug to do maintenance or whatever will do it. You could try changing the time you have the job scheduled for and see if that makes a difference, or if you can't do that you might want to BCP the data that you're loading out to file on the Source Server, then copy the file across using a reliable Copy program (one that automatically resumes the copy if the network connection drops/reconnects) and then perform the import from that file on the target server.

    I guess an other option for what is happening is that maybe your query is quite intensive on the remote server. Maybe another administrator uses that server at that time and sees the slowdown, finds the offending process and kills it - but most likely it's just network problems and can be resolved by one of the two options above.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  13. dhilditch New Member

    Oh - you want the SIMPLE solution!? Who'd have thought. ;D

    Open up your Job properties, go to the STEP that does the business, open up properties of that, go to Advanced - on that page there is a RETRY ATTEMPTS number - change that up to 10 or whatever you like.


    Add flight search to your own site for free
    www.skyscanner.net
  14. ramkumar.mu New Member

    I think i would try those two options. Thanks Satya and David.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  15. ramkumar.mu New Member

    here is the list of failures for that job. the job is scheduled to run twice a day and it failed at various times... so David's point might not be valid here...

    Failed - Invoked on: 2007-01-19 21:35:30
    Failed - Invoked on: 2007-01-21 04:19:00
    Failed - Invoked on: 2007-01-23 10:15:30
    Failed - Invoked on: 2007-01-23 23:39:00

    Any idea why this failure is happening. i checked some forums, KB's at microsoft etc., from which i could jot down 3 reasons

    1) lssass.exe failing
    2) sqlmaint.exe running a set quoted_identifier
    3) Any Backup job running at the time of failure.

    none of the above mentioned reasons convinces me. can you please share your thoughts???


    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  16. dhilditch New Member

    When you look at the job history, ensure to open up the tree in the history view and then click on the last step to run - that should show you a detailed error about precisely why it failed.

    The 4 error messages you've provided above only show times of failure - not why they failed.

    Add flight search to your own site for free
    www.skyscanner.net
  17. satya Moderator

    What is the service pack level on SQL & WIndows?
    Also any recent changes to the environment such as driver upgrade or hardware upgrade.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  18. MohammedU New Member

    Is job failing immediately after start or after some time.
    Why don't you run the sql trace on the destination server during the job times to see this job is reaching the destination server or not...and make sure there are no blockings...



    MohammedU.
    Moderator
    SQL-Server-Performance.com
  19. ramkumar.mu New Member

    That was a good clue Satya. We have SQL Server 2000 and SP1 running on Windows Server 2003. I guess this should be the problem. I recently read an article from Microsoft saying SQL Server 2000 SP2 and lower versions are not compatible with Windows Server 2003.

    for those who didnt know about this, have a look at this link

    http://support.microsoft.com/kb/329329/EN-US


    quote:Originally posted by satya

    What is the service pack level on SQL & WIndows?
    Also any recent changes to the environment such as driver upgrade or hardware upgrade.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  20. ramkumar.mu New Member

    The problem is that this job is scheduled to run every 2 minutes and we really dont know when the job would fail. it suddenly fails and in the next pickup, it runs successfully and so we didnt really have a chance to look at the job when it failed.

    Implementing the profiler could be a good idea...


    quote:Originally posted by MohammedU

    Is job failing immediately after start or after some time.
    Why don't you run the sql trace on the destination server during the job times to see this job is reaching the destination server or not...and make sure there are no blockings...



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  21. vermamunshi New Member

    Dear Ramkumar,

    Could you please provide me the following information:
    Is the so called DTS package which is being invoked by the scheduled job, saved locally in SQL Server or as a Structured Storage File on the disk?

    If it is stored as a Structured Storage File, then do as follows:
    1. Right-click Data Transformation Services folder in Enterprise Manager
    2. Select Open Package.
    3. Open the package from it's current location on the disk.
    4. Save it on SQL Server (by clicking on Package/Save As...)
    5. Close the package.
    6. Click on Local Packages under DTS folder in Enterprise Manager.
    7. Right-click the package and click on Versions...
    8. Keep the latest version of the package and delete older versions, if there are more than one versions of the same package found.
    9. Now open this package from which we deleted the older versions of it.
    10. MAKE SURE YOU MOVE THE OLD PACKAGE (i.e. the one which is stored as a Structured Storage File on the disk) TO A DIFFERENT LOCATION, OUTSIDE IT'S ORIGINAL FOLDER. This is required because overwriting the same file doesn't help.
    11. After moving the old package to a different folder, now save the package that we opened in Enterprise Manager, as a Structured Storage File, with the same name, in it's original folder, where our old package was lying.
    11. Now close the package from SQL Server and also delete the local copy that we saved in SQL Server.
    12. Now run that job manually. If it succeeds, watch it once as to whether it was successful or not. If it succeeds manually then I am sure your problem is resolved.

    If already a local copy of the package is saved on the SQL Server, then you just need to delete older versions of the package and run the job manually once to see whether it the job ran successfully or not.

    Hope this solves your problem.

    Regards,
    Munshi Verma.
    verma_munshi@yahoo.com



    Munshi Verma
  22. satya Moderator

    Ram
    Though it says not supported you will be able to connect and will get the issues like these.
    Anyway it must be upgraded to latest service pack by testing the implementation, to fix all the bugs & issues prior to SP3a.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  23. ramkumar.mu New Member

    Satya,

    I think this compatibility might be the reason for that error as I dont have any strong evidence on other suspects.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  24. ramkumar.mu New Member

    Hi Munshi,

    First, Thanks for that detailed description.
    When the package failed first, i tried saving the package in a different location and dropped the schedule and recreated it again. And as i dont have more than one version, i didnt have the difficulty of deleting the older versions. But, this didnt work for me and then i posted it in SSP.
    Also, the frequency of the failure is just 2/10 times a week in no particular order.


    quote:Originally posted by vermamunshi

    Dear Ramkumar,

    Could you please provide me the following information:
    Is the so called DTS package which is being invoked by the scheduled job, saved locally in SQL Server or as a Structured Storage File on the disk?

    If it is stored as a Structured Storage File, then do as follows:
    1. Right-click Data Transformation Services folder in Enterprise Manager
    2. Select Open Package.
    3. Open the package from it's current location on the disk.
    4. Save it on SQL Server (by clicking on Package/Save As...)
    5. Close the package.
    6. Click on Local Packages under DTS folder in Enterprise Manager.
    7. Right-click the package and click on Versions...
    8. Keep the latest version of the package and delete older versions, if there are more than one versions of the same package found.
    9. Now open this package from which we deleted the older versions of it.
    10. MAKE SURE YOU MOVE THE OLD PACKAGE (i.e. the one which is stored as a Structured Storage File on the disk) TO A DIFFERENT LOCATION, OUTSIDE IT'S ORIGINAL FOLDER. This is required because overwriting the same file doesn't help.
    11. After moving the old package to a different folder, now save the package that we opened in Enterprise Manager, as a Structured Storage File, with the same name, in it's original folder, where our old package was lying.
    11. Now close the package from SQL Server and also delete the local copy that we saved in SQL Server.
    12. Now run that job manually. If it succeeds, watch it once as to whether it was successful or not. If it succeeds manually then I am sure your problem is resolved.

    If already a local copy of the package is saved on the SQL Server, then you just need to delete older versions of the package and run the job manually once to see whether it the job ran successfully or not.

    Hope this solves your problem.

    Regards,
    Munshi Verma.
    verma_munshi@yahoo.com



    Munshi Verma


    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  25. satya Moderator

    What is the problem in applying the latest service pack on SQL Server?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  26. ramkumar.mu New Member

    No problem. I have raised a change request to install the SP 4.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."

Share This Page