Log File Size vs. Data Size ?

I am creating maintenance plans in SQL Server 2005 to manage the backups of a
database. The Databasename_Data.MDF file is approx. 234 MB in size. The
Databasename_Log.LDF is approx. 3.5 GB. Does this ratio of log size to data
size
sound "normal" ? If not, any thoughts or advice ?
If I want to try shrinking the log file, do I need to use a combination of
'BACKUP LOG' and 'DBCC SHRINKFILE' commands ? Or is there a more
automated way using maintenance plans?
Thanks in advance!
TomTom Glasser wrote:
> I am creating maintenance plans in SQL Server 2005 to manage the backups of a
> database. The Databasename_Data.MDF file is approx. 234 MB in size. The
> Databasename_Log.LDF is approx. 3.5 GB. Does this ratio of log size to data
> size
> sound "normal" ? If not, any thoughts or advice ?
> If I want to try shrinking the log file, do I need to use a combination of
> 'BACKUP LOG' and 'DBCC SHRINKFILE' commands ? Or is there a more
> automated way using maintenance plans?
> Thanks in advance!
> Tom
Hi Tom
It sounds like your logfile is much bigger than it needs to be, but it
could also be right. If you are running in FULL recovery mode and you're
not backing up your logfile, it will just grow and grow and grow. When
you backup the logfile, the backup commands will truncate you log which
means that old transactions can be overwritten. In this way the space
will be re-used so the file doesn't need to grow.
If you find out that you have to shrink the logfile, you'll have to run
DBCC SHRINKFILE.
Be carefull about shrinking the log though. A logfile should only be
shrunk in cases where there has been extraordinary activity in the
database which has made the logfile grow. This could e.g. be if you have
imported or deleted a lot of data as a "one time" operation. You
shouldn't shrink a logfile on a regular basis - that will only lead to
poor performance because it will have to grow again, and the file will
most likely be fragmented as well.
Try to have a look at -
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator