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. You can’t exactly do much with this output, more so, just to fulfill curiosity.

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

TLog

Using the code below 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'

vlfs

How much space is used?

DBCC SQLPERF(LOGSPACE)
 

LogSpace1

So let’s get to work and start writing to the transaction log. As you can see below the space used % increases and log size increases too.

lodperce

DBCC SQLPERF(LOGSPACE)

  

loggrow

Log growth 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'
  

vlfnumber

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.

logbaktime

Nicely managed.

 

1 thought on “Azure SQL Database and Transaction Log

  1. Pingback: Dew Drop – February 13, 2019 (#2898) | 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