SQL Server Performance

DB maintenance plan

Discussion in 'General DBA Questions' started by suneelmehra, Aug 2, 2006.

  1. suneelmehra New Member

    One of our production cluster server having problem with DB maintenance backup job problem. From last 3 days it running continuously (stunk up). I have stopped the job and rerun, but again same problem. I restarted the SQL agent too. But it didn#%92t resolve the problem. I don#%92t know why it is happening. Could anyone please help me?
  2. deepakontheweb New Member

    What is the error message you are getting?

    check the latest file from, installation path may vary:

    C:program FilesMicrosoft SQL ServerMSSQLLOGDB Maintenance Plan01.txt

    Deepak Kumar

    --This posting is provided “AS IS” with NO rights for the sake of knowledge sharing.
  3. satya Moderator

  4. suneelmehra New Member

    It didn't give any error, It just running from last 12 hours. error Log file is 0 KB.
  5. satya Moderator

    But the log is stored on the defined path, anyway make sure the hardware has no problems in writing or storing files with appropriate privileges for SQL server service account.

    As a test try to scheudle a backup job seperately.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  6. suneelmehra New Member

    DB maintenance plan is created for 4 databases and It backuping up other databases successfully except one. I am able to take backup of that particular db manually. But It stuck up for that db when its executed by backup job.
  7. satya Moderator

    Then bin the maintenance plan for this database, recreate a fresh plan in that case.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  8. suneelmehra New Member

    Nope, Its not working. I recreated the plan, but again its stuck up and still running...
  9. suneelmehra New Member

    I am waiting for response.
  10. satya Moderator

    What is the size of the database?
    Any disk space issues on the drive where the backup and data files are stored?
    Check the maintenance plan log for the error information.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  11. suneelmehra New Member

    Database size 17 gb. There is no error log in maint log. I am not getting any error, Job is running continously. I ran the update stat last night.
  12. deepakontheweb New Member

    There must be information for error in SQL Errorlog/ SQL Maintenance Plan log or in Windows events logs:

    Check the recent errorlog file, at the time when you started plan for backup to when killed the job
    C:program FilesMicrosoft SQL ServerMSSQLLOGErrorLog

    Can you try some alternatives for testing:

    Change the backup file path to some other drive and test to run the backup job?
    try taking database backup manually and test if it run successfully or not?
    check if transaction log backup for this db is getting executed or not?
    check disk(where backup is going on), if this got some hardware error?
    Can you see if backup file is being created or not, if file is there when it was last modified?

    the list may seem you long enough, but its amazing that there is no error logs.. is 17GB total db size including mdf/ndf and ldf files?

    Deepak Kumar
    MVP, MCDBA - SQL Server

    Disclaimer: This post is provided as is with no rights & warranty for accuracy, for the sake of knowledge sharing only.
  13. satya Moderator

    I ran the update stat last night.
    That has nothing to do with the backup.

    As you are getting issues with this particular database, I suggest to create a fresh plan and define a different backup path in order to check the process.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  14. suneelmehra New Member

    Change the backup file path to some other drive and test to run the backup job?
    I changed and tried, but its not working.

    try taking database backup manually and test if it run successfully or not?
    Yes, I am able to take backup manually.

    check if transaction log backup for this db is getting executed or not?
    The same problem with Transaction log bakup.

    check disk(where backup is going on), if this got some hardware error?
    No hardware error.

    Can you see if backup file is being created or not, if file is there when it was last modified?
    Once the job executing, no file created by the job, even 0KB file. Surprizing.

    Total DB size is 22 gb including mdf, ndf and ldf.
  15. satya Moderator

    When the backup is scheduled using a MP for this database, what do you see from SP_WHO2 results.
    Just an off-topic, are there any recent changes on this SQL server or for this database?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  16. suneelmehra New Member

    From Application side, they installed some upgrades. This could be cause. But we need to rectify.
  17. suneelmehra New Member

  18. suneelmehra New Member

    http://www.developmentnow.com/g/118_2003_10_0_0_464322/Backup-jobs-hang-.htm<br /><img src='/community/emoticons/emotion-6.gif' alt=':(' />
  19. suneelmehra New Member

    Problem got resolved by rebooting the server. I don't know why the most of the problems gets ressolved by reboot the server in Microsoft plateform. <img src='/community/emoticons/emotion-6.gif' alt=':(' />
  20. satya Moderator

    It might be during previous application of hotfix or Service pack might not performed.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.

Share This Page