Maintenance
-
I need help and experts thoughts to setup a very good maintenance plann.
We have 20 databases on sql server. out of 20 one of the database is very critical and important.
I do a full backup of each and every database everyday.
I also do maintenance using maintenance wizard. My question is what is the sequence I need to follow while doing maintenance. what comes first?
Shrink,statistic, indexing, re-indexing etc? Is it better to use GUI wizard or script is there any difference if I use either one.
does some one have a script I can use for a very good maintanece plann where it does maintenance only when it's required,
notifies administrator status of database,
notifes admin status when sql server services or any other services are stopped. notification when harddrive size gets full etc.
-
You may get 20 responses and 20 different answers and all of them will be right. It is really situation dependant. The first thing to note is that until sp2, the SQL2005 maintenance wizard is a less than fantastic piece of technology - especially with everything else you got with SQL 2005.
Beyond that, keep it simple - don't try to automate the world with this. Make sure the backups are working and do other maintenance in a time with the lowest use on the database.
-
I did start with a simple maintenance plann as follow!
For the biggest database here's the plann
Full Database Backup everyday ,
Full Transaction log backup everyday,
Re-indexing, update Statistic etc every month.
but for some reason logs get bigger and bigger (log backups are done everynight)still truncation is not taking place. Am I doing something wrong?
-
Hi,
If it is an extremely critical DB u can configure Logshipping or mirroring for that DB alone.Without SP2 SQL 2005 Maintenance plans have lots of problems.
Rgds
Deepak
[font="Verdana"]- Deepak[/font]
-
If I install SP2 will anything go wrong? do I have to recreate maintenance plann?
-
For the Critical database, you need to be backing up (either T-Log or Full) more often than once a day, I'd suggest anywhere from 15 minute to 4-hour cycle for T-Log backups, depending on your tolerance for potential data loss.
T-Log backups dont truncate the file, just mark some of the space as available for re-use. Generally speaking the Log files shouldn't grow continually if you are doing regular T-Log backups, you should also note that Full DB backups dont free up Log space, only a T-Log backup does that.
-
I do a full backup of T.Log and full backup of database every night but for some reason my T.logs grows unless I manually free up the space.
-
we use scripts and do indexing and statistics in that order
-
Have you tried not shrinking the Log file? If so, does the file size stabilize? If the file size stabilizes, you should leave it at that size and not shrink it. Every time you shrink the file, SQL has to re-grow the file, which is a very expensive operation for SQL.
In reply to SQL Noob, remember that Re-Indexing is a logged operation that can seriously bloat your log files. We have some 200GB-350GB + databases that I have to Re-Index in Simple Recovery mode (with a script of backups around it to minimize exposure) so that we dont run out of HDD space.
-
Through trial and error this is what my solution is at our location. DB's are required 24/7 availablity. 27 databases on one server range from 2GB to 40GB so this is the solution that works for me. I am on SQL2005 sp2 with the hot fix up to 9.0.3050. This is the minimum release pack to be on to use the Maint plans properly.
hourly tran log backups all dbs, nightly at 10 pm central diff backups, weekly on sunday at 10 pm full backups. Sunday is know to be the slowest point for most of the users of the databases so weekly maint plans are scheduled.
at 9:45 I have written a job to truncate all the log files. This is prior to the full backup and after all reindexing and optimization routines are complete. I vary the schedules of the rebuilding of the indexes and the optimizations. The rebuilding of the indexes run before the optimization routines. The largest database that has the most rebuilding and longest time is run as early as possible to make sure it doesnt interfere with the full backup or the truncate. If any of these routines are running at that time then the truncation will not occur. Do not schedule rebuilding of the indexes all at the same time.
Sunday Maint schedule is as follows for a random database and times vary depending on scheduling of prior jobs
1. Rebuild index 6:30 pm 2. Optimize(check database, reindex, update statistics) 7:30. 3. truncate logs 9:45pm 4. Full backup. (transation backups are also running as scheduled during this process)
Some of the ideas I got on this plan come from this great resource so I hope this also helps you.
-
Thanks everyone,
This will be very helpful. But key here would be to install sp2 to make use of Maint Plans.
-
I have been thoroughly frustrated using the maintenance plans in 2005...they were so much easier in 2000. Nothing seems to work right and I am constantly chasing down errors and failures. It should be smart enough to skip simple DBs in a transaction log backup, but in the end it alerts me that their is a failure if I select all user databases.
I had to write a custom script to shrink the log files...anyone else doing that? Or having any success with new service packs? Took us the whole week to patch our server....it is getting painful you can't do this during production hours!!!!
-
I had to write a custom script to shrink the log files also. The only options in the maint plans was to shrinkdatabase! No Thanks!
-
I installed SP2,
now how can i clean up old transaction and backup files both using same maintenance cleap plann, I only have option to enter one extension?
I have read that it's good to change database to simple from full before executing maintenance plann (re-build indexes, update statistic etc)?is this right?
-
refer
http://www.sql-server-performance.com/ss_creating_backup_jobs.asp.
You can perform both by creating a new maintenance clean up task to remove obsolete files.Having applied SP2 everything shud be fine.
Well i dont think so, for the purpose of shrinking u can set recovery as simple if it is full and after shrinking completes revert to full again
[font="Verdana"]- Deepak[/font]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply