Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2005
    Posts
    45

    Question Unanswered: Database Maintenance Plans

    Hello everyone,
    I'm new to DB Maint Plans, so let me apologize upfront. I've taken over a system from a DBA who is no longer working here, and he set up Maint Plans for all of the existing DBs. The plans show up in the Enterprise Manager under "Management->Database Maintenance Plans" like they should, but there are also entries in the "Management->SQL Server Agent->Jobs" area. When I set up a new DB Maint Plan for a new DB, it seems to be working fine, but I don't have any corresponding entries in Jobs. Did the other DBA set these up manually? Does anyone know why he might have done this? Is it needed? The jobs and job steps look like the following:

    [DBName]Full:
    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "W:\sqldata\MSSQL$P001\Backup" -DelBkUps 1WEEKS -CrBkSubDir -BkExt "BAK"'

    [DBName]Maint Integrity Checks:
    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -CkDB '

    [DBName]Maint Optimizations:
    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '

    Any help or insight would be greatly appreciated!
    Thanks in advance,
    Cat

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    When you set up a plan, it should set up a job. In Enterprise Manager folders window, right-click on Jobs and select "Refresh". I'm betting the GUI just isn't aware of the change you made...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would suggest that you start with BOL for Database Maintenance Plans, and go from there.

    -PatP

  4. #4
    Join Date
    Jun 2005
    Posts
    45
    Quote Originally Posted by blindman
    When you set up a plan, it should set up a job. In Enterprise Manager folders window, right-click on Jobs and select "Refresh". I'm betting the GUI just isn't aware of the change you made...
    Hi Blindman,
    Thanks for your reply. I refreshed the GUI for Jobs, but I still don't see my maint plan tasks. Strange! I figured that it must have set up the jobs automatically, but since it didn't set up anything for mine, I didn't know what to think. Do you know if I might have done something incorrectly in setting up my plan? They appear to be working fine.

    Well, any further insight would be appreciated.
    Thanks for your time.
    Cat

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry. I'm not sure why they aren't showing up in your job list. I don't use the Maintenance Plans anyway.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2005
    Posts
    45
    Quote Originally Posted by Pat Phelan
    I would suggest that you start with BOL for Database Maintenance Plans, and go from there.

    -PatP
    I have looked there (if I hadn't I would not have been able to set up a plan in the first place). If I had found the answers to my specific questions, I would not have posted them here. I realize that there are a lot of people who post questions on these boards without taking any time to research the answers themselves, but... 1) I am not one of them, and 2) Sometimes it is easier and less frustrating to ask a human for a quick answer to a question than to try and weed through the enormous amount of information contained in the BOL. If you are going to take the time to respond to a post, it would be more helpful if you would offer more specific areas or keywords to search on to reduce the amount of time spent pouring over information that does not apply.

    Thank you for your time.
    Cat

  7. #7
    Join Date
    Jun 2005
    Posts
    45
    Quote Originally Posted by blindman
    Sorry. I'm not sure why they aren't showing up in your job list. I don't use the Maintenance Plans anyway.
    Well, thank you! At least I know that the previous DBA did not manually set up the jobs. That's one mystery solved.

    Thanks again, you're a sweetheart!
    Cat

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not necessarily. I set up jobs manually. The Maintenance Plan wizard sucks.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by catdavis67
    I have If you are going to take the time to respond to a post, it would be more helpful if you would offer more specific areas or keywords to search on to reduce the amount of time spent pouring over information that does not apply.

    Thank you for your time.
    Cat
    Hi Cat,
    I think you have meet with some of the best and wonderful people of this forum.They are gurus in this field and they spend their valuable and busy time to answer questions from the most difficult one to the most silliest one.I bet that their suggestions are most helpful and have a deep insight.They have a huge patience and delicate word style to deal with everybody with extreme politeness.I suggest you to follow them without any hesitation,bcoz they have the best intention that you could think of. I am saying this not only they are my favourite but they are really a nice human being.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, I'm not always delicate...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As Blindman noted, the Maintenance Plan Wizard is better than nothing, but to a DBA that's been around for a while it isn't a lot better than nothing. I'm one of the "crusty old pharts" that still prefers the level of control that comes from writing the maintenance scripts myself at the Transact-SQL level.

    Usually when I see what you've decribed (Maintenance plans set up, then jobs set up to further customize the plan), it usually means that whoever set them up originally figured out they needed more, so they manually scheduled the job to allow them to customize the existing setup using parameters.

    Sorry if I was a bit short up front. I've been running into a lot of posters/users complaining something to the effect of: "My toy is broke, you need to fix it for me, NOW" and that apparently carried forward into my response to your original question. I should have read it more closely before I responded.

    -PatP

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Delicately handled, Pat.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Jun 2005
    Posts
    45
    Quote Originally Posted by Pat Phelan
    Sorry if I was a bit short up front. I've been running into a lot of posters/users complaining something to the effect of: "My toy is broke, you need to fix it for me, NOW" and that apparently carried forward into my response to your original question. I should have read it more closely before I responded.

    -PatP
    Thank you for that! And I'm sorry too if my tone was a bit harsh, I had been having a frustrating day. I have seen many of the posts on this board which you describe and I know that it can be frustrating.

    I suspect that the previous DBA either did not set up the jobs himself and they were set up by SQL Server (like blindman suggested), OR he did set them up but only because he thought he had to rather than because he wanted to tweek them. I have come to find many of his DBA practises quite suspect to say the least.

    Thanks again to everyone for their responses!
    Cat

Posting Permissions

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