Transaction Log Backups
-
It seems that I am manually truncating my tran logs on a regular basis because they keep growing. I currently run a full backup every night, followed by differentials and tran log backups through out the day, however when the tran log is backed up it never seems to truncate.
What measures should I take to make sure the transaction log is kept to a minimum.
Thanks.
-
the full db backup won't clean your transaction log. Only the log backup. Did you try truncating the log with truncate_only option.
The backup will only truncate the finished transactions that where written to the log. Those that have a commit.
You should check for open transactions in the server if you have explicits transaction On.
Also checks the transactions with profiler. Maybe you are doing everything right but there is nothing to truncate.
Remeber to backup the database if you truncate the log with the backup option truncate_only
-
DBCC SQLPERF(LOGSPACE)
You can alos setup an alter to monitor your database transaction log and fire the backup log job once the log size excesses the threshold.
-
Run DBCC loginfo and check through the rows returned for status ID that are not zero(probably = 2).
If the non zero values are near the end of the log file and you are using SQL Server 7 then the log will only delete free space from the end so it cannot delete back pass the active part to free space near the start of the file.
If this is the case create a table in the db and write some SQL to insert 1000's of rows into it and then run DBCC loginfo again. You may notice the active part of the log has changed position. Keep doing this until the active part of the log moves to the start (it loops around to the start rather then expanding the log file if there is free space).
You should now be able to truncate and then shrink your log file.
Nigel Moore
======================
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply