locked
SQL Server 2008 R2 maintenance plan issue RRS feed

  • Question

  • (SQL Server 2008 R2 Ent. Edition RTM no SP1 installed)

     

    Maintenance Plan in my cluster environment for one particular node on SQL Instance is not working. it is not giving any error & at the same time when I create new maint plan no issue or error. backup from store proc is working fine within SSMS, problem is in maint plan.

     

    when I run backup job created by that maint plan, job is running & no backups happens. I did test with 600MB database & waited for 30mins to see if I can get some lead, but no luck.

     

    after stopping that job, I look into sql agent error logs, sql server error logs, Windows Apps log, no events or error logged for this job.

     

    I don't know if there is a way that I can run this job in debug mode or something that will give me lead or pointer.

     

    if this is not getting resolved, then I think last option is to run setup.exe in REPAIR mode & see if that helps.

     

    once again, any help or pointer will be great.

     

    Thanking in advance.

    Wednesday, August 17, 2011 7:51 PM

Answers

  • Hello:

     

    thanks for replies. issue got resolved after rebooting the server. seems like there was an glitch.

    To my knowledge when server starts & we load any Apps, along with it, it also loads DLL in memory and due to what ever reason if Apps\SSIS .exe not able to talk to that particular DLL, thats were teh problem arises.

    in my question. rebooting was easy fix & never saw that issue after that.

    Thanks for all helps from SQL community.

    Thanks

    • Marked as answer by aaditya2000 Thursday, September 1, 2011 10:10 PM
    Thursday, September 1, 2011 10:09 PM

All replies

  • Check the history of the job.

    Also verify whether you are creating the maintenance plan properly:

    Refer the links to create Maintenance Plan:

    http://weblogs.asp.net/sreejukg/archive/2010/01/20/scheduling-automated-backup-using-sql-server-2008.aspx

    http://sql-articles.com/articles/dba/backup-maintenance-plan-with-cleanup-task/

    -Please mark it as answer if it resolves your problem.

    Thursday, August 18, 2011 1:20 AM
  • Hi Aditya,

    >>last option is to run setup.exe in REPAIR mode & see if that helps

    That is a bit extreme IMO. Is this maintenance plan exported from another server? There are NOT many folks in the community who rely on using Maintenance Plans and most of them I know use SQL Scripts. It is much better than the vanilla Maintenance plans out of the box. If you are writing them using SSIS then that is another story.

    Once the MP is ran, can you go to the default trace and see if it is issued on the right server and check the location of the backups specified in the MP.

    Search for Ola Hallengren's scripts and they are much more reliable than MP in my experience.

     



    --Sankar Reddy

    Blog: http://SankarReddy.com/
    Twitter: http://twitter.com/SankarReddy13/
    • Proposed as answer by Stephanie Lv Wednesday, August 24, 2011 8:18 AM
    Thursday, August 18, 2011 3:11 AM
  • Hello:

     

    thanks for replies. issue got resolved after rebooting the server. seems like there was an glitch.

    To my knowledge when server starts & we load any Apps, along with it, it also loads DLL in memory and due to what ever reason if Apps\SSIS .exe not able to talk to that particular DLL, thats were teh problem arises.

    in my question. rebooting was easy fix & never saw that issue after that.

    Thanks for all helps from SQL community.

    Thanks

    • Marked as answer by aaditya2000 Thursday, September 1, 2011 10:10 PM
    Thursday, September 1, 2011 10:09 PM