Reduce the size of Sql Server log file 1

[cmmrfrds]

I have Sql Server 2000 running in my home office and noticed my log file keeps growing. I read about truncating the log but want to make sure I do not screw anything up. It says in the help that the log file will shrink on a full database backup if the autoshrink option is set. When the backup was run my log file actually grew by about the size of the database. What processes/options do I need to set up to make sure my log file does not get too large and I can recover if necessary.

Thank you,
Jerry

 

[cmmrfrds]

Follow up question. If I set 'trunc. log on chkpt' will I still be able to forward recover my database from the last backup if necessary.

 

[tlbroadbent]

1) There is no reason for the database or log size to increase during a backup. Are you doing other maintenance at the same time - such as rebuilding indexes?

2) Shrinking a database will not necessarily shrink the files. Use dbcc shrinkfile to shrink database and log files. See faq183-345 and

http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_1uzr.asp

for more information.

3) If you truncate the transaction log, you'll only be able to recover from full backups and point-in-time recovery will not be possible. A lot of people in this forum recommend setting the Trunc. Log on Checkpoint option on. I recommend that it only be used on databases that will have NO transaction activity or if you don't care about recovering transaction activity. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[cmmrfrds]

Thank you, I need a recoverable database but also a reasonable size.

I want to be able to recover the database so that leaves out the 'trunc. log on chkpt'. I want to be able to set up a job or process on a cyclical bases to backup the database and log file. During this process I want to make sure the database and log file are kept slim and trim. What steps and options do I need for this process??

If I did a one time run of dbcc shrinkfile on my database to get it back to a reasonable size, would I lose forward recovery? If so, how do I get it back into a state to forward recover if necessary?

 

[tlbroadbent]

If you must truncate the log, you should immediately do a full backup to insure recoverability.

We run a nightly job to execute DBCC SHRINKDATABASE and DBCC SHRINKFILE on critical large databases. These runs do not afect recoverability. We run them after a full backup. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[cmmrfrds]

That leads me to the question. Do I need to truncate the log to release unneeded records form the log file or will the DBCC SHRINKDATABASE and DBCC SHRINKFILE take care of maintaining the file size.

Should my process be:
truncate log
backup database
DBCC SHRINKDATABASE
DBCC SHRINKFILE

Thank you,
Jerry

 

[tlbroadbent]

The full and transaction backups will truncate the inactive portion of the log. There is no need to explicitly truncate the log. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.