November 15, 2010 at 12:27 pm
Recently, I arrived at a new job where Sql 2005 is the primary version. Backup and Database maintenance are all done by maintenance plans generated by wizards. As a result there are variations between instances.
Historically, I haven’t trusted maintenance plans because of bad experiences in earlier versions. Instead I’ve written my own scripts and jobs. As I get ready for moving the shop to Sql 2008 R2, I’m debating about developing standardized maintenance plans or writing my own Tsql based jobs again. The plans in R2 look pretty reliable.
So my question is, have maintenance plans improved enough you trust them on your servers or are they still a tool for the accidental DBA?
November 15, 2010 at 12:35 pm
I've used maintenance plans in SQL 2005 and 2008. Haven't used them in 2008 R2 yet. They work well enough for shotgun maintenance. They do backups well enough, unless you have different databases with different recovery needs, beyond those implicit to Simple vs Full vs Bulk Logged.
They also work for integrity checks, so long as you set them up to simply scream when there's a problem as opposed to trying to auto-fix something. But here, custom screaming based on what's found and where is something you can set up better in a script that you craft yourself for your company's needs. Some "screaming" should be in terms of a light throat clearing and "if it's not a bother, sir, Server #3 would like your attention when you have a minute", and some should be, "BATTLE STATIONS!!!! THIS IS NOT A DRILL!!!" A maintenance plan set up by a wizard really isn't going to know the difference.
I wouldn't do index maintenance through them. Not smart enough about fragmentation levels and scans vs seeks to know what to defrag and how often.
They're a great way to get something up and running while you analyze the real needs of the system, if you inherit a system that just hasn't had anything at all set up or might as well not have.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 16, 2010 at 8:15 pm
Interesting question. In my shop, we've taken a hybrid approach. On most of our servers we're using maintenance plans for backups, but index maintenance is using a customized script, in order to get really tight control. On a few servers, where the business needs are much more stricly defined we're also using scripts for backups. But, for the most part, our DBCC & backups are all handled by maintenance plans.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 17, 2010 at 9:47 am
Nope.
Mainly because it's easy to write scripts to do it.
If people use maintenance plans then they tend not to understand what is actually being done and get into trouble when something goes wrong or just doesn't do what they expect.
In fact I have one at a company I'm visiting at the moment which has been failing for over a month. It's not getting to the cleanup task so most of the databases have about 35 backup files - no tr log backups are probably being taken either.
Can't get to the reporting log file - but I doubt whether that task is being executed anyway.
Error isn't logged to the job history due to truncation.
No one here has anything set up to monitor and assumed that it would work.
Does an existing maintenance plan cope with a database being dropped?
I've seen this sort of thing a lot at companies that use the wizard to do this. They don't understand what it is doing or how to use SSIS.
Cursors never.
DTS - only when needed and never to control.
November 17, 2010 at 11:15 am
We have come to trust Maintenance Plans since 2005 SP2 (there was a bug prior to this release having to do with the cleanup of backups). Currently we are utilizing a hybrid approach as well - Maintenance Plans for the simple things like backups and integrety checks, but scripting for Index Rebuild
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
November 17, 2010 at 3:15 pm
I use maintenance plans also - and also use a hybrid approach. Including using the Execute SQL Task in the maintenance plans to replace tasks as needed.
For example, on systems where I need a customized approach to index maintenance I will use the Execute SQL Task to call out to my stored procedure to perform that maintenance. On a couple of systems, I will use this to perform integrity checks where I only have time to perform a physical_only check.
I tend to favor the maintenance plans because it gives me built-in logging. Anytime I want to review how long each task took - it's right there in the history, which I can also query directly if needed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 18, 2010 at 11:31 am
I'm pretty much stuck with maintenance plans since I'm the only DBA in the shop and backed up by non-DBAs. In SQL 2008 SP1 (not R2), running in VM with a NAS, I have intermittent failures in the reporting step of the MPs. The job fails, though the task actually succeeds.
This seems very rare in the community, though others have seen it. I don't have the problem in my servers running 2000 SP4 or 2005 SP3.
My two cents is to avoid the 2008 MPs if it makes sense to do so.
Beth Richards
Sybase, Oracle and MSSQL DBA
November 18, 2010 at 2:24 pm
bethrich (11/18/2010)
I'm pretty much stuck with maintenance plans since I'm the only DBA in the shop and backed up by non-DBAs. In SQL 2008 SP1 (not R2), running in VM with a NAS, I have intermittent failures in the reporting step of the MPs. The job fails, though the task actually succeeds.This seems very rare in the community, though others have seen it. I don't have the problem in my servers running 2000 SP4 or 2005 SP3.
My two cents is to avoid the 2008 MPs if it makes sense to do so.
If you don't mind - what kind of intermittent failures in the reporting step? Not sure what that would be, since I always disable the text reports feature of the maintenance plans.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 19, 2010 at 11:06 am
Jeffrey,
It's frequently the transaction log backups that fail, but of course that's because they are run far more often than other tasks. We've tried excluding the reports from virus scan and I've moved the timing around. Nothing has resolved the failures.
One of my DMZ servers has the daily maintenance clean up fail one or two times each week. Here's a recent failure. The task succeeded, the reporting *failed*, yet it really didn't fail.
Step details:
Executed as user: NT AUTHORITY\LOCAL SERVICE. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:55:00 PM Progress: 2010-11-12 17:55:01.31 Source: {366AF24C-C675-4488-9657-75E09B0CD481} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2010-11-12 17:55:01.77 Source: Maintenance Cleanup Task Executing query "EXECUTE master.dbo.xp_delete_file 0,N'E:\SQLBAK',N...".: 100% complete End Progress Progress: 2010-11-12 17:55:04.17 Source: Maintenance Cleanup Task 1 Executing query "EXECUTE master.dbo.xp_delete_file 0,N'E:\SQLBAK',N...".: 100% complete End Progress Error: 2010-11-12 17:55:04.46 Code: 0xC0024104 Source: Reporting Task for subplan-{F07974F1-461E-44CA-9623-DE23641B3812} Description: The Execute method on the task returned error code 0x80131500 (Failed to retrieve data for this request.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Warning: 2010-11-12 17:55:04.46 Code: 0x80019002 Source: System Maintenance Plan Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:55:00 PM Finished: 5:55:04 PM Elapsed: 3.797 seconds. The package execution failed. The step failed.
Text file report:
Microsoft(R) Server Maintenance Utility (Unicode) Version 10.0.2531
Report was generated on "<server omitted>".
Maintenance Plan: System Maintenance Plan
Duration: 00:00:02
Status: Succeeded.
Details:
Maintenance Cleanup Task (<server>)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 4 Days
Task start: 2010-11-12T17:55:01.
Task end: 2010-11-12T17:55:01.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''E:\SQLBAK'',N''BAK'',N''2010-11-08T17:55:01'',1
GO
Maintenance Cleanup Task 1 (<server>)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 4 Days
Task start: 2010-11-12T17:55:01.
Task end: 2010-11-12T17:55:04.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''E:\SQLBAK'',N''trn'',N''2010-11-08T17:55:01'',1
GO
Beth Richards
Sybase, Oracle and MSSQL DBA
November 19, 2010 at 1:00 pm
Beth,
One of the things about maintenance plans is that they are not agent jobs. They are in fact SSIS packages that are run from an agent job. What this really means is that the agent job history really doesn't give you the information you need.
To get that information, you need to view the maintenance plan history instead. This history will show each task in the maintenance plan - and if the task failed, will have that information in the Error Number and Error Message boxes.
You should be able to identify why the task is failing from there.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 19, 2010 at 2:04 pm
Jeffrey,
Good point. This is in the MP history:
The requested operation cannot be performed on a file with a user-mapped section open.
I knew that in my past research, file contention had come up, which is why we excluded the files from AV active scans. I was under the impression that had not solved the problem. However, there are no similar job failures in the past week on the other five SQL 2008 servers.
The AV guy thinks the logs on the DMZ servers were not excluded. He's going to double check on Monday.
Thanks for sending me back to check!
Beth Richards
Sybase, Oracle and MSSQL DBA
November 19, 2010 at 2:21 pm
Yeah, that could do it - or any other processes accessing the files when you try to delete them. Hopefully that will solve the problem.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 24, 2010 at 12:24 pm
We are a SQL 2008 R2 shop and, like others, use a hybrid approach. Maintenance plan(s) for backups and integrity checks and custom jobs for index maintenance.
November 26, 2010 at 12:38 pm
I don't think it's a question of trusting maintenance plans vs trusting third party apps or your own scripts. Any of these things can fail for many reasons. What you need to be able to trust is your monitors. I think you should go with the solution that the monitors available to you work best with.
-Kim
December 2, 2010 at 12:16 pm
Revisiting the intermittent failure of maintenance plans and trust or no trust: I still vote no confidence.
A few days ago the AV guy forced updates on all the DMZ servers to exclude the maintenance report files from AV scans. Today a transaction log backup failed in the reporting step and the maintenance plan history showed no failure and no error message. However, the job status was failure and the job history showed the following error:
Code: 0xC0024104 Source: Reporting Task for subplan-{9F0FFAA9-D42E-465A-9EFB-F4BED29AECF2} Description: The Execute method on the task returned error code 0x80131500 (Failed to retrieve data for this request.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Warning: 2010-12-02 09:02:02.49 Code: 0x80019002
So I would continue to advise caution and if I ever figure this out, I'll update the thread.
Beth Richards
Sybase, Oracle and MSSQL DBA
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply