April 26, 2004 at 9:53 am
Hi all,
I recently inherited a (couple of) SQL databases that had been setup by another person. Everything ran ok for about 3 weeks, and then I came in and the server was down because everything blew up. No disc space left, transaction logs need to be truncated etc.
Having resolved the immediate crisis, I am trying to setup a viable maintenance plan. Can anyone provide any examples of a maintenance plan that properly takes care of backups while managing the transaction log etc.?
I appreciate any assistance you can provide.
Bob.
April 27, 2004 at 5:28 am
I generally split the maintenance plan into separate jobs. As for settings you will really need to check BOL to see what is appropriate for your system. The plan I take is as follows:
HOURLY
Backup transaction log
DAILY
1. Back up the transaction log
2. Check the database integrity and include indexes
3. Update statistics. I check 100% to achieve best performance as it takes under an hour; I would reduce it if took a lot longer.
4. Back up the databases including master and msdb and model.
ONCE PER WEEK
I reorganize data and index pages. I always reorganize with the original amount of free space as I know that I have set the fill factor at an appropriate setting. You may want to set a percentage value of free space if you do not have a policy set. Be careful with this one though, it can hammer your transaction log. I have seen databases with a transaction log quadruple the size of the database. Additionally this job effectively runs the DBCC DBREINDEX command which will lock the tables from users as it hits them. Run this out of hours or very quiet times.
See link for more info : http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp
Always back up the transaction log and database after this job to be on the safe side. This is the only job that takes transaction log space as well as the backups of course.
Remember to tick boxes to write back to the sql server log file. It makes it a lot easier to see what has happened.
Hope this gets you started in the right direction.
April 27, 2004 at 8:14 am
I think the previous Transaction Log is very good ...
but if your Data is very important, then Backup transaction log every 20 or 30 minute.
Alamir Mohamed
Alamir_mohamed@yahoo.com
April 27, 2004 at 8:25 am
Thank you for your suggestions. I was very surprised to see that my database was readily available this morning, without having to incorporate some draconian script to overcome whatever was ailing it. Although, the final configuration I have there, does not come close to your suggestions. At this point though, I don't want to touch it (it's working) so I am implementing your suggestions on a dev box to make sure I put it together the right way.
Couple of questions though, you mentioned you have the "fill factor" set appropriately: I don't know what that is, so I set a value of 10% free space.
Also, If I am reading this correctly, these settings really can't be achieved from a single maintenance plan effectively can they? I put several in place to accomplish your recommendations.
Well, I will watch it and see what develops, thanks again for responding. It is appreciated.
April 27, 2004 at 8:27 am
Thanks, I am thinking the hourly backup should be sufficient. So far my experience has been that the backups create problems for me with respect to drive space etc. so I don't want to overdo them.
April 27, 2004 at 9:10 am
10% is about the ballpark figure to get you started. You can find some scripts around that give you statistics to monitor the performance.
I did the same in respect of multiple plans. You can also see what is scheduled more clearly when looking at the database diagram in Enterprise Manager if you name the plans well. Lot easier than reading db maintenance plan1 , db maintenance plan 2 .......
Don't believe what you hear. Looks are everything nowadays!
April 28, 2004 at 10:35 am
Jonathan makes a great point. We always rename the plans with what they do and what database(s) are being maintained. I don't really understand why MS chose to default to 'db maintenance plan 1', etc. which is not intuitive at all. Also if you start each job name with the DB name, the plans for each DB will display together like: Prod Integrity checks, Prod Log Backups, Prod Optimizations, and so on. It makes things much easier especially when managing remotely. Hope this helps.
My hovercraft is full of eels.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply