May 19, 2008 at 10:51 am
I work for a small company (10-15 users) that crawls data from the internet and does reporting. The databases currently store approximately 100-200GB of data. Besides a full nightly backup, what maintenance tasks should be performed?
Possible options include:
Full database backup
Shrink database
Rebuild index
Reorganize index
Update statistics
Check Database integrity
History cleanup
Maintenance cleanup
Additionally, what order should these tasks be performed (i.e., perform a db backup first, or after I perform other tasks) and with what frequency?
Thanks,
Jon
May 19, 2008 at 11:58 am
You can use Rebuild index / Reorganize index say once in week or monthbased on Insert/updates to your DB.
If you are using Sql 2005 there is script in MSDN which you can use as is that is something like Reorganize if fragmentation is
upto 10 % and Rebuild indexes when fragmentation > 30 % , you can change this parameters as well.
Update statistics to can be scheduled on daily/weekly sch
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
May 19, 2008 at 12:11 pm
Sameer,
I had a couple of follow up questions:
1. For a daily maintenance routine, should I do anything besides just the full db backup? Check db integrity?
2. Is updating statistics an "independent task"? In other words, can it be done before or after a full backup? Can it be done before or after a db integrity check?
3. If you rebuild/reorganize an index, is it necessary to update stats? Or is that done inherently?
4. If you rebuild/reorganize the index, should that be done before or after the full db backup? Or does it not matter (i.e., it's indepedent of the db backup task)?
Thanks again for your help,
Jon
May 19, 2008 at 1:04 pm
With regard to backups, the key question is how much data can your business stand to lose?
If one week worth of data could be lost without difficulty, then weekly full backups would be enough. If one day would be okay, then just a daily full backup can be enough.
In most cases, losing a full day's data would be really quite bad. In that case, try a nightly full backup and then run differential backups and log backups throughout the day. (Make sure the database is in full recovery mode first.) Currently, I'm dealing with a large number of small databases, where any data loss at all is quite bad. For those, it's nightly full, hourly diff, and a log backup every 15 minutes. (We're supposed to be setting up server mirroring, but IT is behind deadline on that.)
I highly recommend that you do NOT set up automatic database shrinking. Instead, work out how much the database should grow over a reasonable amount of time, and make it that big, then give it a fixed amount to grow when it needs to. For example, if you find that it's growing by 1 Meg per day, then 6 months of growth would be about 180 Meg. Take the current file size, increase it by 200 Meg (to give some margin for error), and set the auto-grow to 200 Meg. Defrag the hard drive periodically to deal with these file fragments. Shrinking the database (or even worse, turning autoshrink on) usually just results in a slower database, because it constantly has to stop in the middle of stuff and grow itself.
When it comes to rebuilding and reorganizing indexes, you'll need to look at your fragmentation rates. I have a lot of insert/update/delete activity in my current databases, so I reorganize indexes frequently, and rebuild monthly. (I schedule each table to get a reorg once per week, with some tables getting reorganized on Sunday night, some on Monday, and so on. Each weekend, it rebuilds a certain set of indexes, on the same sort of pattern. 1st weekend of the month is tables A, B, C; 2nd weekend of month is D, E, F; etc.) The basic rule of thumb is reorganize below 30% fragmentation, rebuild at 30% or higher. I defragment tables on a similar, parallel schedule.
On statistics, I rebuild those right after I defragment tables. Full scan. Reorganizing indexes will rebuild their statistics for you, so you only have to worry about tables on this.
I check database integrity before I do each backup, and abort the backup if there's a problem. Hasn't come up yet for me, but if it does, I'm pretty sure I don't want to back up a database that I might have to overwrite anyway.
For history cleanup, I keep backup files for two days. They go onto tape the day after they are created, stay on the disk for one day after that (in case I need them for anything), then remove them from the disk. That's worked out well for me.
Others will have other schedules and possibly other needs. These schedules are working out well for me. I've done recovery tests, and so long as I can recover from disk (the backups are on a separate disk from the databases and log files), it takes me about 8 minutes to recover a database to the point of failure, in case of a disk loss. If I lose the database and log disks, but still have the backup disks, I can recover to a maximum loss of 15 minutes. (If I somehow lose all three, like to a fire or something, I'm stuck with recovering from the prior night's offsite tape backup, which means I lose the data changes since midnight. I'd do more with offsite backups, but I don't currently have the option.)
With those index and table maintenance schedules, I end up with pretty clean tables that don't get too badly fragmented, and performance is quite good.
- 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
May 19, 2008 at 2:15 pm
I have a stored procedure that could help you with this. It is using the DMV
sys.dm_db_index_physical_stats to dynamically rebuild / reorganize indexes based on the fragmentation levels. It could also be used to update the statistics.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Ola Hallengren
May 19, 2008 at 2:36 pm
I'll reinforce GSquared's opinion of not shrinking. In general, if the db needed it, it will need it again. If your drive is tight on space, you should consider an upgrade over trying to keep file sizes down... you'll never win that battle.
Knowing your applications and their requirements will help you create your schedules. The functional users will likely dictate your data loss intervals and acceptable recovery windows. If you start asking these questions, however, be prepared for the "why do you want to know" responses. 🙂
As a side note -- one thing that is not advertised very well is that the checkdb task in SP2 does not work properly. You will end up with a checkdb of the master database for each of your databases on the machine, regardless of what you pick in the task.
If you have to or decide to keep the checkdb separate from the backup, you could try the fix @ http://support.microsoft.com/kb/934458/, install the cumulative update, or work around it by switching context within a single sql execution string.
Kyle
May 20, 2008 at 6:48 am
Thanks everyone for your input.
I still had a couple of unanswered questions:
1. Is updating statistics an "independent task"? In other words, can it be done before or after a full backup? Can it be done before or after a db integrity check?
2. If you rebuild/reorganize the index, should that be done before or after the full db backup? Or does it not matter (i.e., is it indepedent of the db backup task)?
Thanks again for your help,
Jon
May 20, 2008 at 6:57 am
The answer to both of those questions is you can do them whenever, in whatever sequence.
- 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
May 20, 2008 at 10:26 am
>2. If you rebuild/reorganize the index, should that be done before or after the full db backup? Or does it not matter (i.e., is it indepedent of the db backup task)?
If you're doing differential backups it could be a good thing to do a full backup after you have rebuilt or reorganized your indexes. Otherwise the size of your differential backups will be very large.
Ola Hallengren
May 20, 2008 at 10:38 am
One question you need to consider also is how large is your maintenance window. In other words, how long do you have each night to perform maintenance?
If you have a very tight window - then, of course, you cannot perform all maintenance tasks and will need to schedule each one appropriately.
Some things you can do to reduce the time each step takes:
1) Compress your backups with one of the various products available:
a) SQL Backup from Redgate
b) SQL Litespeed from Quest
c) Idera's product
d) Hyperbac
2) Perform daily integrity checks using the PHYSICAL_ONLY option
a) Example: DBCC CHECKDB(database) WITH PHYSICAL_ONLY
b) Schedule a full integrity check weekly or monthly
3) Use sp_updatestats to update statistics
a) This will only update statistics that need to be updated
4) Use a smart reindexing scheme
a) BOL has an example script in the help for sys.dm_db_index_physical_stats
Also, consider what your recovery requirements are - which will impact when/how you perform the maintenance tasks. For example, if the requirement is to be able to recover with a minimal amount of loss and be back up and running in one hour - you will need to make sure you can restore the database to a state that is fully functional for the application. That might mean you need to run hourly incrementals (differentials), tlogs every fifteen minutes and a full backup daily (or more often) - and use one of the above utilities to improve the backup/restore processing time, making sure that you have rebuilt/reorganized indexes and updated statistics prior to taking the backup (so you don't have to perform those after a restore).
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply