SQL Transaction Log Space Used

SQL Server Administration
1

I have been battling trying to understand how to maintain and resolve transaction log space usage.
I have a database and currently receiving alerts that the transaction logs for this database have reached almost maximum capacity.

When I run a query to check log space
e.g. DBCC SQLPERF (logspace)

I see the table for Log Size and Log Space Used

The log space used is 93 %

The** log size** is 36mb

The only way that I have ever been able to reduce the log space is to run a full backup of the actual DB than run a full transaction log backup. After doing so the log space used will be down around 3 - 9%

In doing so I am told you can't do this because you could break the chain. Okay so I am a novice and need to understand how this all works and what are the corrective actions to take.

Thank you

2

You don't need to backup the db itself, just the log file. If you are going to have the db in FULL or BULK-LOGGED, you will need to periodically back up the log file. Specifically how often you need to back it up just depends on how quickly it fills up.

But is that 36 mb or 36 gb?

Btw, for performance reasons, you should check to see how many VLFs (virtual logical files) are in the log. If it's too many it can dramatically hurt performance:
DBCC LOGINFO
If that returns more than, say, 150 rows you should ONE TIME shrink the log and then re-allocate it in larger chunks.

1 Like
3

The result of the LOGINFO is as follows

0 2 253952 8192 669 0 128 0
0 2 262144 262144 670 0 128 0
0 2 262144 524288 671 0 64 206000000042200005
0 2 262144 786432 672 2 64 207000000026500831
0 2 262144 1048576 673 2 64 207000000038500001
0 2 262144 1310720 666 0 128 207000000038700208
0 2 262144 1572864 667 0 128 207000000041900611
0 2 262144 1835008 668 0 128 208000000005100585

What exactly does this tell me?

4

It tells you have only 8 VLFs, one per row, which means absolutely no performance issue at all. We just wanted to see how many rows were returned by the command, you can ignore the actual data :-).

1 Like
5

When you take a Log Backup the space in the Log File can then be reused (slight over simplification, but in general "most" of the space will be immediately available for reuse)

A Log Backup can be used to restore to point-in-time - so if you have a Full backup at midnight, all the log backups after that, and you have a disaster at, say, 10:03 then if your last Log Backup was 10:00 then you can restore to 10:00 and only lose 3 minutes changes.

Furthermore, if you have a database corruption, or a hardware failure, it is often possible to take a "tail log backup". So at 10:03 you (successfully) manage to take a Tail Log Backup and in that case you will be able to restore the Full backup from midnight, all the Log Backups up to 10:00 and then you final, Tail Log Backup, and you will lose no data at all.

You should set the interval between log backups according to how much data you can afford to lose, and how big your Log File is growing. 10-minutes is a common interval, so starting with that might be a good compromise. More frequent Log Backups means you will have more backup files (i.e. in a 24 hours period). However, apart from a little overhead the total size, of all the backups, will be the same. So there is very little reason not to take frequent log backups.

For disaster recovery purposes you should also consider what happens if you have a total failure of your server - including any backup files on local disks. So, in addition to copying to tape (e.g. daily) I recommend that you copy all backup files onto A.N.Other server / storage device, perferably off-site if you can, so that in the event of a disaster you have additional copies of all the files both on the original server, and on A.N.Other server.

One other point to note: Log Backups and Full Backups are done using different methods. As such, if your Database becomes corrupted (e.g. disk corruption, disk controller failure/error etc., there there is a very good chance that your Log Backups will NOT also be corrupted. So you can take a final, tail, log backup, restore the most recent "known good" Full backup, and then ALL the Log Backups since then, including your final Tail Backup, and by that means there is a very good chance that the corruption, present in the original database, will no longer be present (in the restored, clean, database)

It is best practice to perform a Database Consistency Check regularly (say, once a week) as that will give you early warning if a corruption is detected in your database. If that finds a corruption, at some future date!, then you can restore from Full and Log backups to fix the problem.

6

Also note that if you are using replication, database mirroring, availability groups, or Change Data Capture, a log backup may not immediately clear the transaction log file. This is true if your replicas/subscribers are no longer synchronizing, the log records have to be transmitted before they can be removed from the principal/primary.

You can query the sys.databases DMV and check the log_reuse_wait_desc column to see if one of these reasons is causing your transaction log to grow. There's a good article here with some additional links:

2 Likes