Useful T-SQL commands for Azure SQL

We have very often in our project the need to perform some system analysis of running databases. The Azure SQL PaaS service is doing almost all required we need, but sometimes you will need to grab some information on demand.

For this reason I collected few interesting commands that might be helpful.

The first command is about transaction log issues. The table [log_reuse_wait_desc] describes the reason why log truncation didn't happen the last time log truncation was attempted. This article provides more information about this.If you get "NOTHING" as result, consider you as happy.

The second one [total_vlf_count] gives a number of virtual log files (VLFs) in the transaction log.

The third one prints the used space of transaction logs.

Finally, the last one prints the last backup time.

DECLARE @dbname as NVARCHAR(20)
SET @dbname = 'YOURDATABASENAME'

-- Transaction log issues
SELECT [name], [log_reuse_wait_desc] FROM sys.databases WHERE name = @dbname

-- Log stats.
SELECT name AS 'cpdmdb', total_vlf_count, [total_log_size_mb], active_log_size_mb FROM sys.databases AS s CROSS APPLY sys.dm_db_log_stats(s.database_id)

-- The logspace usage og the db
DBCC SQLPERF(LOGSPACE)

-- Last backup time
SELECT [name] , log_backup_time AS 'Backup Time' FROM sys.databases AS s CROSS APPLY sys.dm_db_log_stats(s.database_id) WHERE [name] = @dbname