General discussion

Locked

SQL Transaction Log Not Truncating

By info ·
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

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by info In reply to SQL Transaction Log Not T ...

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.

Collapse -

by info In reply to SQL Transaction Log Not T ...

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.

Collapse -

by Tony Hopkinson In reply to SQL Transaction Log Not T ...

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.

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums