I have several Microsoft SQL 2000 Servers.

On each servers I notice that Transaction log files for database keeps
growing.

We also have Symantec Backup Exec 10d with SQL Server Agent.
The backup runs every night and it does a full backup of SQL Server
databases.

I don't understand why the the mydatbasename_log.ldf file does not
shrink. At the rate this file is growing, hard disk will run out of
free space.

I have to manually shrink the log file using query analyzer and the
following SQL statements.

USE databaseName

BACKUP LOG databaseName WITH TRUNCATE_ONLY

DBCC SHRINKFILE(databaseName_log,300).


Why doesn't the backup shrink the file.

On Feb 23, 11:03 am, "nygiantswin2005" <nygiantswin2...@hotmail.com>
[quoted text, click to view]

Does your backup process backup the log as well as the mdf?

When you added WITH TRUNCATE_ONLY you broke the log chain and cannot
use the log to restore the database.

A log of SHRINKFILEs cause file fragmentation

On Feb 23, 11:03 am, "nygiantswin2005" <nygiantswin2...@hotmail.com>
[quoted text, click to view]

Log file will grow if transactions are not explicitly committed or
rolled back. Massive log file growth is often times a sign of
incomplete transactions...

[quoted text, click to view]

You have to "shrink" the log to recover the disk space...

[quoted text, click to view]

Not true. All transactions sit in the log file, committed or
otherwise. They remain in the log until they are TRUNCATED via a
checkpoint (automatically as in Simple mode, or via a log backup in
Bulk/Full modes). The OP's problem is that he's not doing log
backups.

OP, I would suggest that you setup normal SQL database and log
backups, writing to disk files, and use Backup Exec to backup those
disk files instead of the databases themselves. If you ever have to
recover a failed database, or recover to a point in time, you'll find
it difficult to do without true database/log backups.

If you're NOT going to do regular log backups, then put the databases
into Simple recovery mode, which will help minimize (but won't 100%
prevent) log file growth.