Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: maintenance plan maybe not working

  1. #1
    Join Date
    Jul 2005
    Posts
    32

    Cool maintenance plan maybe not working

    I have a weird one. I have 3 maintenance plans that were working for months and now suddenly are sort of not working. The jobs start and then take forever. I stop them from QA (EM doesn't appear to stop them). When I look at the Application log in the event viewer there are no errors because the jobs appeared to freeze. When I look at the maintenenace plan history it says the jobs completed in the usual short amount of time. When I look at the application log it says it did the backups of the data and transaction logs, etc. When I look on the server in the d:\SQL Server\MSSQL\Log directory for that maintenance plan, it says it completed the backups. So, essentially, EM is telling me the jobs haven't completed and everything else tells me the jobs did complete. But I won't feel secure until EM tells me the jobs have actually finished.

    I did do a manual backup of one of the databases and then ran that maintenance plan for that database. I canceled the maintenance plan job after a few minutes (how long the job would usually take) and then looked at the size of the .BAK files. They were both exactly the same size.

    I'd greatly appreciate any help!

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Tried recreate job?

  3. #3
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    Change the job step properties in advanced tab to write to a log file, this will give you more information.

    To find out if the backup file created is actually useful read the header by running

    restore headeronly from disk='fil.bak'

  4. #4
    Join Date
    Jul 2005
    Posts
    32

    maintenance plan maybe not working

    I used this command on master to try to view the header:
    restore headeronly from disk='d:\database\MSSQL\Backup\CustomerSurveys\Cus tomerSurveys_db_200508171107.BAK'

    I got this error:
    Cannot open backup device 'd:\database\MSSQL\Backup\CustomerSurveys\Customer Surveys_db_200508171107.bak'. Device error or device off-line. See the SQL Server error log for more details.

    Did I use the command wrong?

    I set the properties of the job to write to a log and the log said it created the backup fine.

  5. #5
    Join Date
    Jul 2005
    Posts
    32
    I did recreate the maintenance plans and have the same problem. It looks like either EM is doing odd things or the extended stored procedure is not working. The backups work fine if I do them with a wizard. I wanted to use a maintenance plan so I didn't have to create the jobs manually.

    Do you know what commands I'd use for integrity checks if I can't make the maintenance plans work?

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Did you see the file in windows explorer? Check sql server log for detailed reason. And you can use 'dbcc checkdb' for integrity checks.

  7. #7
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187
    is the server in general, in good health - esp disk space? i have had odd results on back up jobs where disk space is low. i think the .bak file gets created at the full size (ie the space is reserved) and then the back up continues to run and actually fills it up...

  8. #8
    Join Date
    Jul 2005
    Posts
    32
    Thank you all for your suggestions. I'm still stuck and I'm going somewhat insane. It appears that EM is acting strangely. I can stop a job in QA but can't stop it in EM. There's 36G free on the drive used for backups (C is for the op sys and D is for the data). The server seems to be fine and backup jobs that weren't created using a maintenance plan work fine.

    Facts that point to the maintenance APPARENTLY not working but actually working:
    1) No errors in event viewer in System or Application
    2) In Log folder for the maintenance plans, all the text files for those jobs say they worked quickly
    3) I checked 2 .BAK files with Beyond Compare, which compares 2 files to see where they are different. One .BAK was made with a wizard and one was made with the maintenance plan job that seemed to take forever. The files were the same except for the header and the footer. All the data in between was the same
    4) No errors were found the the SQLAgent file
    5) The backup files were created and apparently the right size

    Facts to point to EM and maintenance plans behaving oddly:
    1) Unable to stop jobs from EM
    2) After stop job from QA, it doesn't show up in EM
    3) Jobs created with maintenance plan seem to hang, while jobs created manually work fine.

    Question:
    Is there any way to create a backup job in TSQL for more than one database? I'm beginning to give up on maintenance plans but they're nice because then you don't have zillions of jobs.

    Thanks again to all.

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    Did you create plan on server directly or via em on client machine? If from client machine, does it has same sql sp as on server?

  10. #10
    Join Date
    Jul 2005
    Posts
    32
    Using select @@version I get these results for both machines:
    SQL Server 2000 8.00.760

  11. #11
    Join Date
    Sep 2002
    Posts
    5,938
    So tried create plan on the server directly?

  12. #12
    Join Date
    Jul 2005
    Posts
    32
    Yes, unfortunately the maintenance plans behave the same way when created on the server. EM says the job is still executing, but I can open up the text file in Log to look at the maint. plan history and it says the job is done. The Application Log in the Event Viewer says it backed up the databases.

  13. #13
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187
    I know it isnt the solution to the problem but, would trying a restore of one of the databases to a test copy give you the confidence in the jobs working that you need? Surely if it restores with no issues then you are safe?

    where are you using EM? I have EM installed on my notebook and use it to manage the dozen or so servers we have but, sometimes i have to remote desktop to the actual server as some things dont seem to work without being 'on the console'.

  14. #14
    Join Date
    Jul 2005
    Posts
    18
    "I can stop a job in QA but can't stop it in EM"
    "1) Unable to stop jobs from EM
    3) Jobs created with maintenance plan seem to hang, while jobs created manually work fine."

    I'd say you've got problems with your SQL Server Agent. What's it configured to run as? Check through the Agent properties for any complicating factors (Mail/Alerts/Error logging).

    "2) After stop job from QA, it doesn't show up in EM"

    Press F5, and it should come up stopped.

  15. #15
    Join Date
    Sep 2002
    Posts
    5,938
    What's sql agent service account? Does it have following user rights in windows?

    Act as part of the operating system
    Increase quotas
    Log on as a batch job
    Log on as a service
    Replace a process level token
    Bypass traverse checking (that is usually set for Everyone anyway)
    Lock pages in memory
    access this computer from the network
    backup files
    restore files
    increase scheduling priority
    load & unload device drivers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •