August 29, 2008 at 7:44 am
I have been working on my maintenance plans as of late and I am wondering how people are doing maintenance (outside of backup methodologies). Essentially I was curious if people are using the standard SSIS mainatenance tasks in SSMS. Are you using script tasks to determine index rebuild vs reorg? Do you clean up history?
Additionally I am interested in the sequence. Do you backup first, last or both? Do you run update statistics before or after your index rebuild/reorgs? Do you always do you DBCC CHECKs first? Do you use conditional logic for any automated rules or fixes (ie: if check fails, don't reindex...).
Edit: I thought I would clarify, this is for minor databases. This maintenance plan is for about 50+ databases of small sizes, low use, etc. Mission critical high end systems obviously require their own individual plans. So general practice here for std dbs.
Thanks!
August 29, 2008 at 8:15 am
I run checkDB before I do backups. Can't see the point to backing up a corrupted file.
No reason to update stats before/after an index rebuild, since it does that for you. A reorg can, to my knowledge, benefit from a stat rebuild.
On a small database, I don't bother with checking indexes for fragmentation first, since I can just simply rebuild during nightly down-time and it won't hurt anything. It's overkill, but it works.
On small databases that might grow, I assume they will grow, and build individual maintenance plans that take into account growth and more complex criteria for index rebuild/reorg, etc. I'd rather not discover some day that a plan for a small database is in use on a big database and causing problems. It's something that's likely to be overlooked when debugging poor performance, kind of like triggers.
Of course, I often have to compromise on this. On a long-neglected database, I'll often throw together something that does shotgun maintenance, and then refine it when I have time to get back to it. A generic maintenance plan is better than no plan at all.
- 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
August 29, 2008 at 9:42 am
DMC
we dont use maint plans (they generally are a pain). All our maint\backup jobs are scripted using T-SQL.
Any database maintenance ops should be carried out before a backup. As Gsquared says, no point backing up a db until you know its had maintenance applied. We run DBCC before backup too and rebuild indexes overnight (most of our databses are small and it doesnt hurt as no one uses the databases this late at night). Obviously 24\7 environments are different and if you have one of those then it pays to fully review and sanitise your strategy to suit the environment 😎
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 29, 2008 at 12:50 pm
The interesting thing for me so far is I do a backup first and then run all my maintenance. I understand your points. I have always been of the thought that I wanted to backup the db prior to all my "changes" from maintenance. I can always restore the previous days backup and transaction logs I perform. But it is a valid point about backing up an already bad database.
August 29, 2008 at 1:12 pm
Last point. Don't put a shrink database/file task in the maintenance plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2008 at 4:27 pm
You can put a shrinking log file as your maintenance plan but never put shrinking database in your maintenance plan.
August 30, 2008 at 1:58 am
balbirsinghsodhi (8/29/2008)
You can put a shrinking log file as your maintenance plan but never put shrinking database in your maintenance plan.
It's strongly recommended that neither logs nor data files are shrunk on a regular basis.
If you're shrinking a log on a regular basis, then it implies that the log is just going to grow again after the shrink. The growth of the tran log can potentially bring all data changes in the database to a halt while the log grows. Also, repeated shrink and grow operations can cause file-level fragmentation, as well as increasin the number of virtual log files within the transaction log, which can slow down backups and database recovery.
The usual recomendation for the transaction log is to find the size that it needs to be, depending on the amount of database activity and the frequency of log backups, and then leave the file alone.
The time you should be considering shrinking a database file is after a large once-off load has grown the transaction log far beyond what it normally needs to be, or after a data archive operation has left a data file with a massive amount of free space that isn't likely to be reused.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2008 at 9:20 pm
GilaMonster (8/30/2008)
balbirsinghsodhi (8/29/2008)
You can put a shrinking log file as your maintenance plan but never put shrinking database in your maintenance plan.It's strongly recommended that neither logs nor data files are shrunk on a regular basis.
If you're shrinking a log on a regular basis, then it implies that the log is just going to grow again after the shrink. The growth of the tran log can potentially bring all data changes in the database to a halt while the log grows. Also, repeated shrink and grow operations can cause file-level fragmentation, as well as increasin the number of virtual log files within the transaction log, which can slow down backups and database recovery.
The usual recomendation for the transaction log is to find the size that it needs to be, depending on the amount of database activity and the frequency of log backups, and then leave the file alone.
The time you should be considering shrinking a database file is after a large once-off load has grown the transaction log far beyond what it normally needs to be, or after a data archive operation has left a data file with a massive amount of free space that isn't likely to be reused.
Spot on... except for one thing... I'd consider making the MDF and the LDF larger than they were at the time of backup if the space used were within, say 90 or 95% of the total reserved space.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 3, 2008 at 6:25 pm
GSquared (8/29/2008)
I run checkDB before I do backups. Can't see the point to backing up a corrupted file.No reason to update stats before/after an index rebuild, since it does that for you. A reorg can, to my knowledge, benefit from a stat rebuild.
On a small database, I don't bother with checking indexes for fragmentation first, since I can just simply rebuild during nightly down-time and it won't hurt anything. It's overkill, but it works.
On small databases that might grow, I assume they will grow, and build individual maintenance plans that take into account growth and more complex criteria for index rebuild/reorg, etc. I'd rather not discover some day that a plan for a small database is in use on a big database and causing problems. It's something that's likely to be overlooked when debugging poor performance, kind of like triggers.
Of course, I often have to compromise on this. On a long-neglected database, I'll often throw together something that does shotgun maintenance, and then refine it when I have time to get back to it. A generic maintenance plan is better than no plan at all.
---
Being the paranoid freak that I am, and fortunately dealing in relatively small dbs (5-8GB in size); I backup to 1 folder location on a separate physical drive; run my maintenance plans; and then do a second backup to a second folder. The backups are overwrites, not appends; and it gives me sleepy-time happiness knowing my a$$ is covered in case one of the maintenance plans blow up...:D
September 3, 2008 at 8:00 pm
Go thru stored procedure posted at:
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization .
MJ
September 9, 2008 at 3:13 am
If you run through the Maintenance Plan Wizard, which I'm sure a lot of DBAs use, then the default order of tasks is:
Check Database Integrity
Shrink Database
Reorganize Index
Rebuild Index
Update Statistics
Clean Up History
Execute SQL Server Agent Job
Backup Up Database (Full)
Backup Up Database (Differential)
Backup Database (Transaction Log)
Maintenance Cleanup Task
I guess that the order of the first 5 tasks is the most important, plus the Backup. This thread has already talked about when to run the Backup; but how about the order of the first 5 tasks? Is the default order optimal?
September 9, 2008 at 6:19 am
The first 5 there shouldn't be run as is.
Shrink database should not be a regular task ever.
If the indexes are rebuilt, then reorganise is redundant, as is update statistics. If the indexes are reorganised and not rebuilt, then the update stats is recommended.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2008 at 8:47 am
Thanks Gail, sorry I completely didn't realise that shrink database was one of the options! Absolutely agree, don't include it.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply