Sunday, March 11, 2012

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,
CatWhen 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...|||I would suggest that you start with BOL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/entrmgr/em_6x7w.asp) for Database Maintenance Plans, and go from there.

-PatP|||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|||Sorry. I'm not sure why they aren't showing up in your job list. I don't use the Maintenance Plans anyway.|||I would suggest that you start with BOL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/entrmgr/em_6x7w.asp) 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|||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|||Not necessarily. I set up jobs manually. The Maintenance Plan wizard sucks.|||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.|||Well, I'm not always delicate... ;)|||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|||Delicately handled, Pat.|||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

No comments:

Post a Comment