December 21, 2004 at 8:52 am
I have a database on sql 2000 which is being written to 24/7 that has a log which continually grows. I have a stored procedure that checks all log sizes and % usage each night and emails me each with the ones where the usage % is very low compared to its size. This morning the database in question had a log size of 2GB but a usage % of 1. What I do for these databases is issue a shrinkfile, then do a log backup, then another shrinkfile to get the file size down where it should be. My question is this: Is there a better way to handle this operation? I feel like I'm missing something, and that there is an easier way to take care of this situation. Let me know what you think.
December 21, 2004 at 11:16 am
The reason your log file grew to 2 GB might be that there was a huge transaction which was going on which resulted in the log file to be increased. Later on , when the tran log was backed up, all the space was released and you saw that only 1 % is used. If you had simple recovery mode, space will be released on checkpoint. I won't be concerned if you are not running out of space. Infact, i would prefer allocating 2 GB as this will involve less overhead in growing the log file if a big transaction is run against the database.
December 21, 2004 at 11:27 am
The database is constantly being written to with many small transactions, and it is set to full recovery mode. This log file does this every week, continues to grow until shrunk. Shouldn't the normal backups free up space in the log? I could understand the large log size if it was being utilized, but the usage % is very low.
December 21, 2004 at 11:53 am
How often do you backup the log file ? Full backups will not truncate the transaction log file.
December 21, 2004 at 11:53 am
Maybe your log grows so much, because you create some database maintanaice like update statistics or regenerate index. What is your datbase data size?
December 21, 2004 at 12:05 pm
database size = 2.5 GB
log is backed up every hour through a maintenance plan.
There is also a maintenance plan that does a full backup nightly of all user databases. Here are the settings for that maintenance plan:
Optimizations:
Reorganize data and index pages = checked
change free space to 10% option is selected
Integrity:
Check database integrity = checked
include indexes option selected
Attempt to repair minor problems = unchecked
December 21, 2004 at 12:11 pm
that's why your log is 2GB and 1% used. When the maintenance plan runs, reorganize data and index use a los of log space, so you log grow and when the plan finish the log is cleaned.
December 21, 2004 at 1:23 pm
If you wish to continue to do the maintenance, it shouldn't be a problem to leave the file large, as long as you can accept the large log size. It should expand to the required size (apparently 2gb), and then stay at that size. Its generally not good to continually grow and shrink your database files. In your case, the main issue would be that the maintenance takes longer because it has to expand the file. However, when files are expanding during production transactions, it creates an obvious extra workload. Also, when database files are continually expanded and shrunk, operating system level fragmentation can become an issue.
If you have the disk space, your best bet would be to just let the log file stay at that 2gb level.
Steve
December 21, 2004 at 1:26 pm
In that case let me pose this question--How often do you guys propose reindexing?
December 22, 2004 at 11:57 am
Depends on how often you do your dataloads and fragmentation. We do ours once a week. You can check your fragmentation by running DBCC SHOWCONTIG.
December 22, 2004 at 12:01 pm
can you change your recovery mode to simple when reindexing is being done?
December 22, 2004 at 12:05 pm
I wrote a stored proc that allows you to pass the database as a variable and it will check the frag level of all indexes on every table in the database, issuing a dbcc indexdefrag to any index above a specified fragmentation level. This should work much better than the complete rebuild which the maintenance plan does. I then have an agent job that creates a cursor of all database names and calls the SP for each. If anyone wants the SP let me know and I'll post it.
December 22, 2004 at 12:18 pm
I don't believe that the recovery model change works. Check out this link: http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1956
It seems that the only way that changing the recovery model would work, and it's quite possible I was misreading the posts, is to change it, reindex, do a complete backup, then restore to full recovery. I'm going to try some testing with that and see if it works.
December 22, 2004 at 1:00 pm
If you do that, you'll need to change to full recovery model, then take your full backup, so that your tlog backups can associate with the full backup.
Steve
December 23, 2004 at 8:56 pm
Change a recovery mode to Simple, do a reindexing and change back to Full. Last step could be Backup and Verify. You could do that in a 4-Step Job. Very last step is a notification.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply