Log file growing very large

[Luvsql]

I shrunk our log files last week to 25mb each. We have a database backup each morning that was setup using Enterprise Manager:

BACKUP DATABASE [x] TO [y] WITH INIT , NOUNLOAD , NAME = x backup', NOSKIP , STATS = 10, DESCRIPTION = N'x backup', NOFORMAT

I then have the log being backed up every 30 minutes all day, that was setup using Enterprise Manager:

BACKUP LOG [x] TO [y] WITH NOINIT , NOUNLOAD , NAME = N'x log backup', SKIP , STATS = 10, DESCRIPTION = N'x log backup', NOFORMAT


My issue is now the log size is well over 2Gb. We do not have that many transactions within a 30 minute period (our database size is only 5Gb). How can the log grow that fast (it is set to auto increment by 25% (default)), but even 25% 0f 5gb is only 1250mb and there's no way it could go over that in 30 minutes)).

When I setup the log backups in Enterprise Manager, I marked to clear transactions in the log. Is this not the same as truncating the log? Why does EM not add the truncate part to the backup. What syntax do I have to add to truncate the log AFTER each log backup, so it doesn't keep growing to this size. Our issue now is the backup file is now growing substancially as well (last week it was 5.2gb and now it is 7.2gb).

I would like to setup each database with an initial size of 500mb (will create new one when all users off), then set it to increment when needed, but if we are backing up the log every 30 minutes, it shouldn't need to grow that much.

 

[nigelrivett]

See

http://www.nigelrivett.net/TransactionLogFileGrows_1.html

Could be a number of things that are preventing entries from being released. An earlier open transaction, checkpoint not running, replication, ...

It's usually a rogue query that does a large update.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.