April 7, 2008 at 9:16 am
I would like to know what would be a typical maintenance plan if all my db's were set to simple recovery mode.
I would like to know which tasks to include and the recommended order.
Currently I do a check db and backup of all dbs daily.
Then on Sunday, in an addition to the above I also do a shrink, reorganize index, rebuild index, Update statistics, Clean Up history and Maintenance Cleanup task.
Are all the tasks that I do on Sunday necessary?
From other threads I've read here, shrinking is not recommended because it would cause fragmentation.
I will remove the shrinking task, but is there something that will unfragment the dbs?
What would be a typical plan if all the db's were set to full recovery mode?
April 7, 2008 at 2:49 pm
1) shrink - no, not recommended, you're right
2) reorganize index - not sure what this is so, no
3) rebuild index - yes
4) Update statistics - no. taken care of in step 3
5) Clean Up history - sure
6) Maintenance Cleanup task - sure
7) CheckDB - yes
8) backups - yes
I, personally, would do it in this order. Then your backup is most up-to-date with respect to all your maintenance tasks.
If the recovery was set to full, you'd need to do log backups at some interval that is appropriate for your business and application. I had one application where we did log backups every 30 minutes and another application where it was hourly. It depends on your needs and how much data is being added/modified. Also, your index rebuild is going to add to your log files as well.
-- You can't be late until you show up.
April 7, 2008 at 3:21 pm
Agree with above.
April 8, 2008 at 8:56 am
If the recovery was set to full, you'd need to do log backups at some interval that is appropriate for your business and application. I had one application where we did log backups every 30 minutes and another application where it was hourly. It depends on your needs and how much data is being added/modified. Also, your index rebuild is going to add to your log files as well.
I need some more clarification on this topic...
Doing Log Backups at some interval, should clear up the log file, but not shrink the physical file, right? So after a full cycle of transactions (Application + Index rebuilt + maintenance) log file sizes should basically remain the same?
After runing maintenance plan on one of our user DB, the log file is huge (12 GB) and has a performace impact on the application. (The DB file is about 8GB).
Please explain... I am a newby DBA.
Thanks.
April 8, 2008 at 9:02 am
If backups of the log have never been done (or very infrequently) the log is going to continue to grow. Once you backup the log, it removes the unneeded transactions from the log, freeing up the space but it doesn't (or shouldn't) autoshrink. You can shrink the size using DBCC Shrinkfile (look at BOL). The size of the file shouldn't have an impact on your performance, unless it's too small and auto-grows. Is there a need to reduce the size? If your application truly needs the space, leave it alone (increasing the size will impact performance). If it grew because of a rare, infrequent process, then shrink it.
-- You can't be late until you show up.
April 8, 2008 at 11:02 am
OK. Here is my scenario. I am doing this (as a test) on a DB of about 8 GB with Full recovery model.
1) Log files were never backed up until yesterday.
2) Yesterday I backed up and shrink them up manually (initial size aprox 400 MB)
3) I set up a Maintenance plan which runs every 2 hours and backs up the log file and cleans up old trn files.
4) Every night another maintenace plan runs, which reindexes, cleans history, clean old backup files, and backups all DB
Today my log file was about 12 GB right before log backup run. After log backup it is a tiny 200 MB. Is it normal to fluctuate as much?
How do I find how many transactions/ min(sec), on average on thet DB? There is formula/good practice between number of transactions/minute and frequency of bakup logs?
April 8, 2008 at 11:30 am
I don't know that transactions per minute is going to indicate the frequency of log backups. Why not schedule them for every 30 minutes? It'll keep the log file size down. You should be able to tell what's going on within your environment. The reindex is definitely increasing your log size. Is nightly necessary for that task? Might be overkill but you would know that better than I. You may want to add CHECKDB to your nightly or weekly maintenance tasks (again, whatever is right for YOU). Also, I wouldn't necessarily shrink the log to 200 (or 400) MB. As stated, when it needs to grow, you're going to impact performance. Why not put it to 1 GB and watch the growth? Trying to size it properly will take a little time as you watch patterns but it'll be worth it in the long run when you can disable the shrinkfile!
-- You can't be late until you show up.
April 9, 2008 at 7:15 am
Thanks a lot for your help. Hope you can help me to figure this out.
I created a usp(sp1) to track the log file size during 1 day. The stored procedures runs DBCC sqlperf( logspace) and DBCC LOGINFO(DBName) and puts the info into a tracking table, t1.
My log Maintenance plan for the log file runs every 2(between 6 AM and 10PM) hours and has the following steps:
1) Run sp1
2) Backup Log File
3) Maintenance Cleanup task (clean trn)
4) Run sp1
This is what I got for yesterday afternoon/today.
Size (MB) PctUsedTime Action
37.55 24.444/8/08 15:59Before Log Backup
37.55 22.524/8/08 15:59After Log Backup
25.93 46.774/8/08 18:00Before Log Backup
25.93 25.684/8/08 18:00After Log Backup
25.93 26.324/8/08 20:00Before Log Backup
25.93 24.084/8/08 20:00After Log Backup
16.37 25.854/8/08 22:00Before Log Backup
16.37 23.624/8/08 22:00After Log Backup
12,507.12 95.244/9/08 6:00Before Log Backup
12,507.12 95.254/9/08 6:00After Log Backup
1) I don't see any change in the log file size before and after log backup
2) Pct used is not changed to much either
3) The big jump at 8:00 AM is normal, because I have another plan which is running during the night(reidexes, backups and shrinks the DB) -- but why at least pct used is not getting significantly smaller? :unsure:
4) If I look at the virtual log files,(6:00 AM) I see a lot of them in use (Status = 2) Why is that? At 6:00 AM not a lot of users/transactions are going on.
August 13, 2008 at 2:43 pm
If your system is 24/7, you may not be able to do index rebuilds, at least not on large tables.
Index rebuilds take the table off-line - not a good thing for 24/7 shops.
(if you have SQL 05 enterprise, you can do online index rebuilds)
That is where the reorganize issue comes into play.
on SQL 05, you can use the ALTER INDEX REORGANIZE command, and on prior version, you can use the DBCC INDEXDEFRAG command.
this site has a good summary of the differences:
http://www.mssqltips.com/tip.asp?tip=1165
The more you are prepared, the less you need it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply