Web Development

Ask something
Discussion Locked

SQL Transaction Log Not Truncating

0 helpful
3 replies

by info 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

SQL 2000 SP3a, Database backup model is FULL: A full backup happens every sunday, Differential backups happens 1 time a day AM. Transaction Log happens 1 time a day PM. The Transaction log is 13GB, the transaction backup log is 15GB, everytime I run the transaction log backukp, the trans log backup grows(It should) but the transaction log does not truncate????
'here is the backup log script
BACKUP LOG [NCSCabling]
TO DISK = N'E:\SQL\MSSQL\BACKUP\NCSCabling_Trans.bak'
WITH NOINIT , NOUNLOAD , NAME = N'NCSCabling backup', NOSKIP , STATS = 10, NOFORMAT

3 replies

by info

By the way the server is SQL 2000, and there are NO open transactions in the database.
The database transaction Log was newely created backed up 2 times. First time I backed up the Transaction log, the trans log backup was 13GB (It should be, but at that point I thought the Transaction Log itself would purge all closed transactions that were backed up by Backup Log command) and the second backup it grew to 15GB.

by info

If there is more info you would like, just ask, if you want more points, just ask. or maybe I am talking to a wall.

by tony hopkinson

Do the above and then backup again with truncate_only option. That will clear off any unused transactions. Of course you only want to do that when your sure you have got a copy of it. Might be safer if you've got the space to no_truncate the log after the full dump on a sunday as you don't need it then.

This discussion is closed to new replies.