Azure SQL Database and Transaction Log

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'

nothingsql

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'

vlfcount

How much space is used?

 DBCC SQLPERF(LOGSPACE)

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).

griowth

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'

vlf12

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.

logbak

Advertisements

1 thought on “Azure SQL Database and Transaction Log

  1. Pingback: Dew Drop - January 23, 2019 (#2883) - Morning Dew

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s