Transaction Logs - Full disk problem

[#1]

Hello,

I´m facing mouth to mouth an strange situation.

The Backup of our SAP systems are setup through the transaction DB13 and the SQL Server devices. This task is working without problems, the backup completes with success.

The problem is with the Transaction Logs (Tlogs). Every month the Tlogs disk fills and the system stops because of that and to solve this, every month, I have to go to SQL Management Studio, change the recovery model to "Simple" and perform a shrink task to the Tlog file as you can see in the following image, in this way it release unused space:

My question is... it´s possible to shrink automatically the Tlog files every time the task of full backup completes with success?

Can you help me please?

Thank you,

samid raif

[Former Member]

Hi,

TLog backup doesn't shrink the log file (It was  in older versions of SQL).

As suggested by Matt, have proper disk size for TLog.

If you have frequent full and TLog backups infact you can set the recovery mode to simple permanently (with accepting some risk).

Regards,

Nick Loy

[Former Member]

I have seen at some environments wherein they use SQL 2008 R2, which doesn't shrink the transactional log files (after the Tlog backup).

Does recovery mode has to be set to simple, so that the Tlog backup will shrink the Tlogs (after the backup)?

[Matt_Fraser]

Again, you should not be shrinking your transaction log as a part of normal operations. When you backup the transaction log, the process will truncate the log, which is not the same thing. That means it removes the committed transactions from it, leaving only the open, uncommitted transactions, thus mostly emptying the file, but not reducing its size on disk. New transactions will then start to populate the space in the file just emptied (this is simplified; the process is a little more complex than this, but for our purposes here, this is close enough to what happens).

If you set recovery mode to simple, you will not be able to run transaction log backups. Simple mode simply means that the transactions will be truncated out of the file as soon as they are committed.

SQL Server 2008 R2 will truncate the log after a successful log backup just as the same as earlier and later releases of SQL Server.