Transaction Logs - Full disk problem

[Former Member]

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.

[Matt_Fraser]

Hi Samid,

From your description I suppose that you aren't regularly backing up your transaction logs, only doing full backups of the database?  If so, you will certainly have this problem you describe.  It is very important that you also schedule frequent transaction log backups.  For most production systems, you should have transaction log backups run several times per day, perhaps hourly, perhaps even more frequently if it's a very busy system.  When you backup a transaction log, the backup job will automatically truncate the log, thus keeping it from filling and thus keeping the file from growing.

You can schedule transaction log backups as well as full backups from DB13, DBACOCKPIT, or from the SQL Server Management Studio.

You should not need (nor is it recommended) to autoshrink your logfiles.  Instead, it's best to find an appropriate size such that between backups it will never fill completely.  Finding that size may take some trial and error, and of course it's a balance between making the file bigger vs making the log backup job more frequent.

You can also consider setting up database alerts in the Management Studio that will automatically kick off the log backup if the log reaches a certain percentage of full (say 80%).

Best regards,

Matt

[Former Member]

Hello Matt,

First of all thank you for your answer and my apologies for the delayed answer.

As I understand when I read your message, it´s possible to set an autoshrink to Transaction Log files if I set the backup of Transaction Logs in DB13, am I correct? Which option, at DB13, I must choose for this?

I only had set the activity "Full Backup" in DB13! The backup always ends with success, the problem is not the backup of database, the problem here are the transaction logs files that grows day by day til the end of the disk space and whenever this happens the SAP system stops or shows several short dumps errors!

Regards,

samid raif

[Matt_Fraser]

Samid,

It is not an autoshrink you are looking for; it is a transaction log backup.  In DB13, you will schedule this just like you did your full database backup.

It doesn't shrink the file, it truncates the file, meaning it empties it, but the file remains.  You may need to shrink it once, since you've allowed it to grow out of proportion due to never backing it up, but after that you won't need to shrink it again, just back it up.  The transaction log backup job will back it up to your backup device and then truncate the backed-up entries out of the file.

Full database backups are not enough to protect your data.  You must also schedule regular, frequent transaction log backups.  You might additionally want to schedule differential database backups as an intermediate step, but those will not truncate the transaction log -- only transaction log backups will do that (or deliberate truncation, but that's not where you want to go).

You should probably consult with a database administrator about setting up a proper database backup strategy for your system, but as a starting point I would suggest something like this (for a production system):

Full database backups weekly, during a time of least activity (i.e. 2am Sunday, or something that is a time of lowest activity for your system).

Differential database backups daily, again at a time of low user activity, perhaps 1am every day except the day of the full backup (i.e. 1am Monday through Saturday).

Transaction log backups hourly, every day, except when the other backups are running (i.e. from 4am to Midnight, daily, or something like that).

You can set these up either in DB13/DBACOCKPIT or via the SQL Server Management Studio -- it doesn't matter.

If you don't use transaction log backups, then in the event of a database failure (for whatever reason) you run the risk of losing all data since the previous full backup.  For most production SAP systems, this would be unacceptable.

If you do use transaction log backups, in addition to full backups, then you would lose at most any changes since the most recent transaction log backup (in the scenario above, 1 hour), and if your database's transaction log survives, not even that much, since the transaction log retains up-to-the-minute activity, giving you point-in-time restore capability.

Regards,

Matt