October 14, 2008 at 7:10 am
Hiya all,
New here, new to maintenance plans and hoping to learn a lot. I have worked with SQL Server 2000 and 2005 for a while (development), but never really touched the maintenance (backups, restoring and optimising) side of things. I have just recently got my MCIPT certification so have now been put in charge of our company's SQL Server instance. 😛
Ok, so I need to know if Im on the right track here? I searched the forum, and couldnt find much on a simialr setup or the hows to and best practices with a rough step by step guide, so Im posting this here. Gonna give details on our SQL Server setup and then my maintenance plan so far.
SQL Server instance details:
1. Its made up of 12 databases, varying from small to large in size (max 3GB for largest database).
2. Some tables are updated frequently and others not so much, so its a real mix of everything in terms of size and activity.
3. There are at most 100 users connecting to a database at a time.
4. Running SQL Server 2005 Enterprise.
5. All databases are on Full recovery model.
Maintenance Plan (so far):
Full backup - Occurs at 00:00:00 every Sunday night on all databases
Diff backup - Occurs every night at 00:00:00 on all databases
Trans log backup - Occurs every 15 minutes on all databases
Rebuild & Reorganize Indexes - Occurs after full backup every Sunday. Only rebuild if frag percent > 30, otherwise reorganise indexes. Occurs on all talbles in all databases.
Check DB Integrity - Occurs at 00:00:00 every Sunday just before the full backup runs
Some questions:
1. When do I truncate the log files, is it better to do this after each Differentail backup or each Full backup?
2. Do I need to run the Update Statistics SP, as the index rebuild updates the stats automatically. Have read on the net that most ppl leave this out as the rebuild covers this fine?
So thats it, cant think of ore questions right now?
Is my maintenance plan addequate? Have I missed out something, or should I change something? Any help appreciated. I have set up most of this via jobs and SSIS packages through BIDS.
Will really appreciate all your input as I want to do this correctly from the beginning. Thanks.:D
CF 😉
October 14, 2008 at 2:09 pm
You mentioned that you have 12 databases with the largest being 3 Gig. Were you expecting these to grow much larger, say 10 times as big? How long are your full backups currently taking? I guess I'm not sure why you'd need to do incremental backups each night instead of full backups each night. Also, you shouldn't be manually truncating the transaction logs, since you're in full recovery mode and performing trasnaction log backups. The transaction log backup will free space within the log file for reuse for you. Also as you mentioned, the index rebuild will update your statistics for you. You may want to force updating statistics after a reorganize though.
A couple of things you may have missed, based on what you posted above, there are separate tasks in 2005 called "Maintenance Cleanup Task" that will clean up old files from backups and maintenance plan text reports, and "History Cleanup Task" that will clean up old job execution history entries for SQL Agent, backups, and maintenance plans.
October 15, 2008 at 12:59 am
Hi Chris,
Thanks for the reply. No...I dont imagine it will grow much bigger than a gig a year.
Thought doing Diff backups each night is better in terms of disk space usage, instead of full backups each night. Disk space isnt critical, but always nice to reduce this if possible.
Trans logs...yeah, ok...that slipped my mind. Forgot about that...thanks for the reminder 😉
Will add the update stats after the reorganise
I saw those 2 tasks, wasnt sure if they were needed. Will check them out, thanks.
So you think a full backup each night is beter than Diff? I know the databases are not very big etc, so a full backup can be done, but just thought it would be better practice using Diff, as its quicker and takes less space?
Thanks for the input 😉
October 15, 2008 at 2:35 am
Quicker to back up, yes, but not quicker to restore. You'll need your full backup from last Sunday and your latest diff backup - as opposed to just a full backup. Then your transaction logs.
I generally recommend diff backups on busy production databases when I believe it can save time vs. having to restore transaction logs over long periods of time. For instance, let's assume your full backup runs at midnight and your database gets screwed up at 15:39 (3:39 pm) the next day. Now, if you ran a diff backup at 15:00, you'll need to restore your full backup, your diff backup from 15:00 and any log backups after that. If you don't have any diff backups, you'll have to restore from transaction log backups the entire period from midnight until 15:30-something - basically rerunning every single transaction that's been processed that day. Such an operation can take a lot longer than just doing a full + diff + transaction logs from the last half-hour restore. This is especially true if you've had multiple updates to the same data (or actually within the same pages) during the day, since with a transaction log restore, you'll have to rerun every one of these updates, whereas your diff at 15:00 allows you to jump directly up to that point in time without having to rerun any transactions at all.
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
October 15, 2008 at 3:58 am
Thanks for the reply, and I see your point. I think that makes sense though....the databases are not super busy. So then maybe scrap the Diff backups and do full backups maybe every 12 hours, with tran logs in between?
I think the databases run full backups every 6 hours currently as was setup by my boss. But this is not ideal as the backups run midday while people are using the systems etc. I think 12 hours full backups and trans log backups between will work fine then?
October 15, 2008 at 4:51 am
Yes, it should, although I generally don't run full backups more often than once a day myself.
Basically, as long as it meets your needs, it's fine. 😉
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
October 15, 2008 at 5:42 am
Ok great, gonna give that a try then and see how it goes 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply