{"name":"Large transaction log files","description":"This metric measures the number of transaction log files that are greater than 10 GB. The associated alert is raised when the number of files exceeds the specified threshold.\r\n\r\nIf the transaction log autogrows rapidly, it can suggest that log backups are not being carried out frequently enough, or another resource may be preventing the log from truncating.\r\n\r\nFor more information about autogrow settings, including details on how to check what your current setting is, see <a href=http:\/\/support.microsoft.com\/kb\/315512> Considerations for the \"autogrow\" and \"autoshrink\" settings in SQL Server (MSDN)<\/a>.","tsql":"DECLARE @Max_log INT;\r\nSET @Max_log =  10240; -- size of the transaction log in MB\r\nSELECT  COUNT(*) AS Cnt\r\nFROM    master.sys.master_files\r\nWHERE   type_desc = 'LOG'\r\n        AND ( ( [size] * 8 ) \/ 1024 ) > @Max_log\r\n        AND database_id = DB_ID();","instances":true,"frequency":1800,"databases":{"mode":0},"rateofchange":false,"metricenabled":true,"alertname":"Number of transaction log files increased","alertdescription":"This alert is raised when the number of log files greater than 10 GB goes above the defined thresholds.","aboveorbelow":"above","collections":1,"alertenabled":true,"_thresholds_high":{"selected":false},"_thresholds_medium":{"selected":true,"value":0},"_thresholds_low":{"selected":false},"thresholds_comment":"Note: These thresholds are intended as guideline values. If they seem too high or too low, replace them with values more suited to your environment.","tsql_comment":"Note: By default, this T-SQL counts the number of transaction log files that are greater than 10 GB. If this file size seems too high or too low for your environment, replace the SET @Max_log =  10240 value.","alertdetailquery":"DECLARE @Max_log INT;\r\nSET @Max_log = 10240;\r\nSELECT CAST(STRING_AGG(\r\n                          DB_NAME(database_id) + ': ' + name + ' ('\r\n                          + CAST(CAST((CAST(size AS BIGINT) * 8) \/ 1024 AS INT) AS VARCHAR(20)) + ' MB, '\r\n                          + physical_name + ')',\r\n                          ', '\r\n                      ) AS NVARCHAR(MAX))\r\nFROM master.sys.master_files\r\nWHERE type_desc = 'LOG'\r\n      AND ((size * 8) \/ 1024) > @Max_log\r\n      AND database_id = DB_ID();","targetentitytype":0}