{"name":"Transaction log fragmentation","description":"<p><strong>Explanation: <\/strong>This custom metric measures the number of VLFs in the transaction log.  A high number of VLFs can affect write performance and impact restore and recovery times.<\/p>\r\n<p><strong>Guideline values:<\/strong> You can use the 50 VLF rule for a log file < 25GB. Anything larger than 25GB, a healthy log, in terms of VLF number, should be determined by log size \/ 512MB. <\/p>\r\n<p><strong>Possible solutions:<\/strong> If this metric indicates an increased number of VLFs in the transaction log, the transaction log should be rebuilt using the steps outlined in Kimberly Tripp\u2019s blog post, <a href=\"http:\/\/www.sqlskills.com\/blogs\/kimberly\/post\/8-Steps-to-better-Transaction-Log-throughput.aspx\"><strong>8 Steps to better Transaction Log throughput<\/strong><\/a>. Also see <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms179355%28v=sql.105%29.aspx\"><strong>Transaction Log Physical Architecture<\/strong><\/a>.<\/p>\r\n","tsql":"CREATE TABLE #tmp\r\n    (\r\n      RecoveryUnitID TINYINT ,\r\n      FileId TINYINT ,\r\n      FileSize BIGINT ,\r\n      StartOffset BIGINT ,\r\n      FSeqNo INT ,\r\n      Status TINYINT ,\r\n      Parity TINYINT ,\r\n      CreateLSN NUMERIC(25, 0)\r\n    );\r\n \r\n \r\n--determine version of SQL Server, and trim column from temp table if version less than SQL Server 2012 (11)\r\nDECLARE @Version VARCHAR(128);\r\nSET @Version = CONVERT(VARCHAR(128), SERVERPROPERTY('ProductVersion'));\r\nSET @Version = LEFT(@Version, CHARINDEX('.', @Version) - 1);\r\n \r\nIF CAST(@Version AS INTEGER) < 11 \r\n    BEGIN\r\n        ALTER TABLE #tmp\r\n        DROP COLUMN RecoveryUnitID\r\n \r\n    END;\r\n \r\n--get the results of DBCC LOGINFO\r\nINSERT  INTO #tmp\r\n        EXEC ( 'DBCC LOGINFO'\r\n            );\r\n \r\nSELECT  COUNT(*)\r\nFROM    #tmp;\r\n \r\nDROP TABLE #tmp;\r\n","instances":true,"frequency":86400,"databases":{"mode":0},"rateofchange":false,"metricenabled":true,"alertname":"High number of VLF in the transaction log","alertdescription":"<p>This alert is raised if excessive Virtual Log Files (VLF) counts occur. This can lead to performance problems with the transaction log, including restore and recovery times. Excessive VLFs can occur when a transaction log is resized many times, through autogrowth for example.<\/p>\r\n\r\n<p>You can rebuild the transaction log by following the steps outlined in Kimberly Tripp\u2019s blog post, <a href=\"http:\/\/www.sqlskills.com\/blogs\/kimberly\/post\/8-Steps-to-better-Transaction-Log-throughput.aspx\"><strong>8 Steps to better Transaction Log throughput<\/strong><\/a>. Also see <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms179355%28v=sql.105%29.aspx\"><strong>Transaction Log Physical Architecture<\/strong><\/a>.<\/p>","aboveorbelow":"above","collections":1,"alertenabled":true,"_thresholds_high":{"selected":false},"_thresholds_medium":{"selected":false},"_thresholds_low":{"selected":false},"thresholds_comment":"The value of the thresholds (too high or too low) need to be specified for your particular environment, use values suited to your server performance.","targetentitytype":0}