Checking out the transaction log in Azure SQL Database. If you are curious like me, you will want to know about what your transaction log is doing in the cloud. The following queries have been tested and run okay within Azure SQL Database. It gives you some great insight.

First up, the classic log_reuse_wait_desc.

 SELECT name, [log_reuse_wait_desc] FROM sys.databases WHERE name = 'customertwo'

I have no issues with VLF fragmentation. You can tell this is a small log file.

SELECT name AS 'Database Name', total_vlf_count AS 'VLF count'
FROM sys.databases AS s
CROSS APPLY sys.dm_db_log_stats(s.database_id)
WHERE name  'master'

How much space is used?

 DBCC SQLPERF(LOGSPACE)

So let’s get to work and start logging some information. As you can see below the space used % increases (and the actual file size).

The log grows and expect the VLF count to change too.

SELECT name AS 'Database Name', total_vlf_count AS 'VLF count'
FROM sys.databases AS s
CROSS APPLY sys.dm_db_log_stats(s.database_id)
WHERE name  'master'

The purpose of this blog is to show you that you can see what is going on but unlike the classic on-premises SQL servers you cannot ALTER DATABASE and change file sizes. Don’t forget, Microsoft are doing the transaction log backups to keep the size in check for reuse anyways.

 SELECT name AS 'Database Name', log_backup_time AS 'last log backup time'
FROM sys.databases AS s CROSS APPLY sys.dm_db_log_stats(s.database_id)
WHERE name  'master'

It seems to be roughly every 10 minutes that a log backup is taken – nicely managed.