Log File Size Management

[dzdncnfsd]

All of the database log files on my SQL server are set for unrestricted growth. I am assuming that is the default at time of creation. Does backing up,truncating the log, and shrinking the database on a regular schedule keep the size small, or should I set a size limit for all new databases? How can I tell what size increments to use? Most of the activity is bulk copy (in), so that is non-logged, right?
Thanks,
Gladys

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[foxdev]

Backing up the database on a regular basis should keep the log file size within a normal and predictable range.

I usually set maximum sizes on my database and log files, because too many times I've experienced (or caused) bizarre processes gone awry, such as a loading program that gets caught in an infinite loop.

I don't have a magic formula for what to set these at; you'll have to consult the users of the databases to see what growth they expect. I would then set the max size at something like annual volume X 3. For general use, I set my log file max size to half of the database size, but that is just a WAG and may not be appropriate to your situation.
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[dzdncnfsd]

Thanks, that helps a lot.

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[dzdncnfsd]

I have decided to backup one of the transaction logs, however, it currently has 9GB allocated. 3GB are in use. Do I need 9GB of empty space in order to do this, or will the backup file only be 3GB in size???
Thanks,
Gladys

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[dzdncnfsd]

Manual transaction log backups cannot be done if "truncate log on checkpoint" is on - does that mean that the log gets automatically truncated whenever its database is backed up?
Thanks,
Gladys

Gladys Clemmer
gladys.clemmer@fifsg.com