If you are not managing the transaction log space used by a database, the transaction log might grow uncontrollably. If your database is in FULL recovery mode, then you better take transaction log backups frequently enough to keep your transaction log from growing out of control. If you are not taking transaction log backups, or not taking them often enough then you run the risk of running out of disk space and causing your database to be unavailable, until you resolve the space issue.
With SQL Server 2017 there is a new dynamic management function (DMF) named “sys.dm_db_log_stats” that was introduced with SQL Server 2017 that exposes statistical information about a database transaction log. One of the things you can do with the information returned from this DMF is to determine how full a database transaction log file is. In order to use this new DMF you must have “VIEW DATABASE STATE” permissions.
Let me show you an example that will return all of the columns this DMF exposes for a database name “Demo”, which resides on one of my SQL Server 2017 instances:
SELECT * FROM sys.dm_db_log_stats (db_id('Demo'));
Below are the first few columns that come back when I run the above command:
DMF Column Results
There is a lot of information in this partial set of columns that are returned from this DMF. Let’s see what I can infer from just the columns displayed above:
- First if I look at the column named “recovery_mode” I can tell that my “Demo” database is in “FULL” recovery mode, therefore it will be important to back up the transaction log periodically on this database, to keep the transaction log from growing uncontrollable, if updates are performed against this database.
- I can tell which log sequence numbers (lsns) are contained in my transaction log file by using the “log_min_lsn” and “log_end_lsn” values.
- I can see information about the VLFs in my Demo database by reviewing the different VLF columns: current_vlf_sequence_number, current_vlf_size_mb, total_vlf_count, and active_vlf_count.
- If I look at the “total_log_size_mb” I can tell how much space is allocated to my “Demo” databases transaction log file. I can see the transaction log file size for my “Demo” database is just around 8 mbs in size. If I subtract the “active_log_size_mb” from “total_log_size_mb”, I can tell how much additional transaction log space is available before the transaction log becomes full. By using this calculated number, I can tell that I have a lot of free space in my transaction log. In my case there is a little more than 6.72 MB of space in my “Demo” database transaction log that is not active.
- By using the column “log_truncation_holdup_reason” I can find what is blocking my transaction log from being truncated. As you can see currently there is “NOTHING” keeping my transaction log from being truncated.
In this tip I’ve shown you a couple of things you could learn by using the “sys.dm_db_log_stats” DMF. There are a number of other columns that this DMF exposes that I have not shown. I encourage those of you exploring SQL Server 2017 to look at this DMF to see what it has to offer you. I’d be interesting to hear your opinions for how you could use this new DMF to improve or resolve issues you might be having with your SQL Server transaction log files.
