September 30, 2004 at 7:06 am
I'm trying to make recomendations to someone about DB maintenance and I don't really have very much information about their application nor do I have access to all of their Databases.
When using Maintenance Plans to do Backups, what other switches should or shouldn't be used and why????
(ie: Reorganize data and index pages, changing free space per page percentage to 10%, Remove unused space from DB files, setting free space to 25%, Check database integrity, including indexes, prior to backing up the DB or transaction log)
This is more of a general question. I believe that the DBs being maintained by these maintenance plans are not static.. they grow quite frequently. I just don't want to end up with a problem later on such as transaction file growing out of proportion.. etc....
October 1, 2004 at 2:07 am
I'd really recommend you don't use main plans and use t-sql within a scheduled job.
I think there's already been a thread on shrinking databases, but I generally don't do this as a regular job as it will lead to increased o/s fragmentation.
updating stats is one thing you should arrange every day for a database. Rebuilding indexes is good but if you don't know the databases ( or their usage ) you may cause problems - e.g. if the dbs are log shipping index rebuilds can create large transaction logs
I'd also be very careful on making any changes unless you know the application and database well.
I'd try to find out when there is a quiet time, weekends, sundays ? to schedule integrity checks, again you don't want these kicking in when users are in the dbs.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 1, 2004 at 9:51 am
Thanks much!
October 4, 2004 at 8:59 am
I think maintenance plans are a very useful tool. I'm sure that they have their limitations and many prefer to write their own t-sql. One thing I really like is the ability to keep multiple copies of the backups and delete old ones that are older than a specific time frame. One limitation is not being able to schedule differential backups.
One setting that should never be used is "Attempt to repair any minor problems" on the Integrity tab. Running in single user is required for this to work which of course causes an error.
Again, I can understand that maintenance plans will be disdained by some but they can be a quick and useful tool to make sure backups and some other checks are being run.
Steve
October 4, 2004 at 9:11 am
One of the downsides of the maint plans is when they fail, which they often will, the best error you tend to get is "sysmaint.exe failed" not very helpful !
It's pretty easy to manage backups, I'm sure there will be scripts on the site which do all that's needed.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 5, 2004 at 6:32 am
Thank you everyone!
June 29, 2006 at 9:28 am
I've encountered my first main problem with maintenance plans. The optimization part is now failing because I added a database which has a table with a computed column. I understand that this has been an issue with maintenance plans for some time now and I am looking for workarounds. We are running SQL 2k on Windows 2K3. Can anyone tell me exactly what optimization does? Does it only re-index (dbcc reindex) or is there more involved? Any help is GREATLY appreciated.
Thanks...Chris
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply