Start a new topic
Not Taken

Transaction Log Monitoring

Do you have any scripts for monitoring Azure SQL Transaction Log size? Since we are using Transparent Data Encryption (TDE), "dbcc sqlperf(logspace)" does not appear to be an option. 

1 Comment

Hi Scott,


Based on some google'ing, it appears that SQL Azure maintains a sys.dm_tran_database_transactions view that contains log of transactions and their associated sizes.  You should be able to define a metric of type DatabaseSqlNumeric that tracks a query such as the one specified below


 

select sum(t.database_transaction_log_bytes_used) from sys.dm_tran_database_transactions t inner join sys.databases d on t.database_id = d.database_id where d.name = 'XYZ'

 


If you named the metric LogSize, you can then use this metric as an alert condition:

 

LogSize > XXXX

 

Definately test out the SQL script to ensure that it captures correct size of your transaction log


1 person likes this
Login or Signup to post a comment