March 10, 2005 at 3:10 am
I am defining maintence plans for some dbs.I am wondering how much MB should I put in the box referring to "when it grows beyond' and "amount of free space to remain after shrink" under removed unused space from datafiles.I have 5 dbs of 10MB ,25MB, 50 MB, 250mb and 300MB resp in accending order of db size.
March 10, 2005 at 3:11 am
Is there any thumb rule which can be used to decide about this selection.Pls suggest
March 10, 2005 at 9:10 am
This is kind of a tough question, because the answer is - "It depends."
But the first thing that I would suggest is forget about shrinking (removing unused space from the database). Just don't do it as part of a maintenance plan. Its not necessary, and will only hurt performance. I'm pasting in my response to another person in another forum on the topic of shrinking...
If you have to, you have to. But shrinking the transaction log every week, much less every day, is a really bad idea. If you shrink it every day, its obviously growing every day. Why does it grow? Because it doesn't have sufficient space to perform the operation that it has been asked to perform. When does it grow? When users update or insert records, and when you are running certain maintenance processes. Well, if the transaction log has to expand in order for a user's update to process, the user sits and waits while the log file expands. This is NOT a good thing! If the transaction log has to expand to complete your maintenance routine, your maintenance routine runs much longer as well. You may not care about that one...unless of course, users are in the system...in which case they are being impacted by both the log expansion AND your maintenance routine. Finally, constant expansion and shrinking of the log file leads to increased fragmentation, which results in...yep, decreased performance. In short, shrinking the transaction log every day will impact the performance of your database dramatically!
If you are having difficulty controlling log growth, the best thing that you can do is increase the frequency of your log backups. Many shops (including mine) backup logs as frequently as every 15 minutes. That won't cost you much more disk space for the backups, and will allow your transaction log to stay much, much smaller. You're just splitting up those transactions among more backup files. You'll find that when you arrive at that magic log backup frequency, your log file will stabilize at a size that you are satisfied with and will quit expanding.
Sometimes, it may be necessary to shrink a data file or log file due to unusual circumstances, like deleting a large amount of data from a database, or because of some maintenance task that causes the log to grow dramatically (reindexing). But as a general rule, you should size the database so that it has room to grow, and as part of your administration of that database, watch the size/utilization, and expand it when necessary. If you manage it this way, you shouldn't need to shrink it.
Steve
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply