Truncating Transaction Logs
-
The database is transactional and I don't mind truncating the older transactions as we have it all backed up but I don't want to truncate recent transactions in the log.
So can you delete lets say transactions in the log that are older than 3 months?
Thanks,
Ninel
-
Per BOL(backup)
LOG
Specifies a backup of the transaction log only. The log is backed up from the last successfully executed LOG backup to the current end of the log. Once the log is backed up, the space may be truncated when no longer required by replication or active transactions.
Note If backing up the log does not appear to truncate most of the log, an old open transaction may exist in the log. Log space can be monitored with DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log Backups.
-
Excuse me if I sound stupid, but I'm not a big expert on this subject.
I am using the following statements to reduce the log:
BACKUP LOG [dbname] WITH TRUNCATE_ONLY
DBCC SHRINKFILE (logname_log)
Can I get rid of data that is lets say 3 months and older?
-
When you truncate the transaction log, you are deleting all commited transactions that have been checkpointed (written to the database).
If you are no doing transaction log backups, and only truncating the transaction log, it would probably be better to set your database to the simple recovery mode. This will eliminate the need for you to run periodic backup log [dbname] with truncate_only and dbcc shrinkfile (logname_log) statements.
Be sure you are making regular full backups of your database in case of a hardware failure.
-
Here's my feeling on transaction logs and there are those out there who may disagree...
I do full, differential and transaction log backups at regular intervals...
If I go through the trouble of shrinking my transaction log file over night for instance, it will only grow again the next day, which will cause a performance hit everytime it has to increase it's size. I do transaction log backups at regular intervals to keep my transaction log from growing absurdly large, that way I don't have to worry too much about the log size as it stays somewhat static.
If your log is way larger than it nornmally is and you find yourself running out of disk space I'd suggest a full backup followed by a dbcc shrinkfille. Then pay attention to how large it becomes. If you find it keeps growing too large and running you out of disk space, you really need to get autorization to purchase more.
When you talk about transaction logs, you really don't have anything in the log that would be three months old. It gets cleared out when you do your full and transaction log backups. The only thing that remains are un-committed transactions, those transactions that have been written to the log but not the database yet.
-
As lynn stated, you can change the recovery level of your database, just know that if you are not using full recovery you lose the ability to do a point in time recovery. you will only be able to recover to the last full backup plus differential backups you may have.
-
Luke L,
One thing in your explaination about backups: full and differential backups do not truncate the transaction log. That is only done by the transaction log backup. Full and Differential backups only backup enough of the transaction log to ensure a consistant backup/restore.
Lynn
-
To expand a bit on what other people have said, it's best to take periodic transaction log backups, at least in synch with your full backups, if not more often. When you have the transaction log backup, there's no reason to keep that data in the log file, so truncate the logs (which I believe the backup does anyway), then shrink the file. When you shrink the file, it returns the file to it's initial creation size. So make the initial file size the size that you want the transaction log to hover around, and you won't get the performance hit when it grows. This way the log will remain around the usual every day size, and might only grow when you do something significant like a software release on the database.
Cheers,
Rick
Rick Todd -
Don't do an explicit log truncate after a transaction log backup. You may truncate a transaction that completes after the transaction log backup. Let the transaction log backup handle deleting of inactive transactions that have been backed up. Also, I would not run scheduled shrinks of the transaction log during the day either. If you are constantly shrinking it and then it has to grow to log new transactions, you will have some performance issues.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply