February 25, 2004 at 7:56 am
My first problem is that I am not a DBA and they won't listen to me and get one. So I have to learn everything the hard way! I have two questions:
I have a database with full recovery mode set. The log(ldf) just keeps on growing. What options need to be set for the log to automatically shrink?
I also have some dbs with simple recovery mode which I have turned on autoshrink and trunc log on chkpt. I have no problems with those logs. However, I did before I set those two options. In reading your forums, I keep seeing that autoshrink should not be on. Why?
February 25, 2004 at 8:03 am
autoshrink can activate at bad moments (when many people are using the database) and so they will experience degraded performance. We have autoshrink on for most of our production databases, but they are all 2GB or less and we see very little performance problems that we can link directly to autoshrink.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 26, 2004 at 7:38 am
If the database is set for "full recovery", then you need to have a job to dump the transaction log periodically. The autoshrink option only works if the database is set for "simple" recovery. If you do not wish to set up a job to dump the transaction logs at regular intervals, then put the database into "simple" mode. SQL Server will take care of the rest. After changing the recovery mode, however, you'll need to clear the logs the first time with (a) dump tran dbname with truncate_only, (b) and from within the database -- DBCC SHRINKFILE (log_file_name)
Regards, Melissa
February 26, 2004 at 7:58 am
AutoShrink is not a good choice because of the fact that both shrinking and growing of the log file will lower performance of the system. It is better to keep the log file big enough, so that it doesn't need to expand... and make sure log backups are frequent. As an example, we have 100GB database, with a 14 GB log file, and log backup every 15 minutes. Most of the time, only a small fraction of the log file contains data - but we can be sure that when certain processes start, there is enough free space in the file. Expanding the log file takes time and consumes resources, so we try to avoid it.
Of course it depends on the processes in your DB; on our system log size around 10-15% of the DB seems to be just right.
February 26, 2004 at 8:23 am
Interesting stuff. This database really belongs to another person in my organization. I checked and he is not backing up the log. I seem to remember reading somewhere that the log will shrink on backup. Is that so? I basically told him that if he is backing up the db and not the log with a full recovery mode, the backup really isn't much good without the log backup. Is this true?
February 26, 2004 at 8:35 am
The log will not shrink on backup - file remains the same size, but most contents are emptied - so when there are new data coming in, log doesn't have to grow.
Well, he can go back to the last full backup... which is mostly too old to be of any use. Or, in case of serious problem discovered too late, when DB already has been backed up with the error, s/he'll have hell of a time to fix it. With log backups, it is possible to go to the last log backup (or, if that already contains the error, to any prior log backup), or even restore to a defined point of time. As I said, we are backing up the log every 15 minutes during the workday, and every 60 minutes during night and weekends.
February 26, 2004 at 9:43 am
If this isn't a dev/text db, turn it off. The benfits are far outweighed by the headaches.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply