如何查找具有大量虛擬日誌文件的數據庫？

我在生產伺服器上有幾個數百 GB 的數據庫，每天有成千上萬的事務通過它們執行。

幾乎所有這些數據庫都使用 SQL Server 鏡像進行鏡像。

儘管我們已經仔細規劃了物理日誌文件的大小以匹配預期的日誌文件活動；偶爾會出現問題，日誌需要增長到超出我們預測的最大值。我們已將所有日誌文件設置為增長 8192MB，但是當數據庫面臨增長日誌文件的壓力時，它有時只會以非常小的塊增長日誌，因此在某些情況下會創建數十萬個虛擬日誌文件 (VLF) .

當我們的一個生產數據庫意外地進入超過 200,000 個 VLF 的恢復時，我開始理解保持低 VLF 數量的重要性。恢復需要 20 多個小時；在此期間，我們的部分業務無法運營。

我需要一個可以監控伺服器上所有數據庫的虛擬日誌文件數量的解決方案，如果任何特定日誌文件的 VLF 數量超過給定數量，則發送警報電子郵件。

我知道DBCC LOGINFO;返回 VLF 列表，但是，我不想手動執行它。

我創建了以下 SQL 語句，該語句創建了一個很好的表，列出了數據庫以及 VLF 的數量，但是，我不知道如何將其放入 SQL 代理作業中，以便在任何數據庫超過“x”數時向我們的團隊發送電子郵件的 VLF。

DECLARE @cmd_per_database_prefix nvarchar(max);
DECLARE @cmd_per_database nvarchar(max);
DECLARE @database_name nvarchar(255);
SET @cmd_per_database = '';
SET @cmd_per_database_prefix = 
'
   SET NOCOUNT ON;
   DECLARE @vlf_count_table TABLE (database_name nvarchar(255), vlf_count int);
   DECLARE @params nvarchar(max);
   DECLARE @db_name nvarchar(255);
   DECLARE @vlf_count int;
   SET @params = ''@db_name nvarchar(255) OUTPUT, @vlf_count int OUTPUT'';
   DECLARE @cmdGetVLFCount nvarchar(max);
   SET @cmdGetVLFCount = 
   ''
       DECLARE @tab TABLE 
       (
           FileId int
           , FileSize nvarchar(255)
           , StartOffset nvarchar(255)
           , FSeqNo nvarchar(255)
           , Status int
           , Parity int
           , CreateLSN nvarchar(255)
       );
       DECLARE @cmd nvarchar(max);
       SET @cmd = ''''DBCC LOGINFO;'''';
       INSERT INTO @tab
       EXEC sp_executesql @cmd;
       SET @db_name = db_name();
       SET @vlf_count = (select count(*) FROM @tab t);
   '';
';
DECLARE cur CURSOR FOR
SELECT NAME 
FROM sys.databases
WHERE database_id > 4 and state=0;
OPEN cur;
FETCH NEXT FROM cur INTO @database_name;
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @cmd_per_database = @cmd_per_database + 
   '
       EXEC ' + @database_name + '.sys.sp_executesql @cmdGetVLFCount, @params, @db_name OUTPUT, @vlf_count OUTPUT;
       INSERT INTO @vlf_count_table (database_name, vlf_count) VALUES (@db_name, @vlf_count);
   ';
   FETCH NEXT FROM cur INTO @database_name;
END
SET @cmd_per_database = @cmd_per_database_prefix + @cmd_per_database + char(13) + char(10) + 'select * from @vlf_count_table t;';
EXEC sp_executesql @cmd_per_database;
CLOSE cur;
DEALLOCATE cur;

嘗試將此使用輸出INSERT...EXEC到表格中以附加到電子郵件使用sp_send_dbmail被證明是徒勞的。

SQL Server 窒息：

An INSERT EXEC statement cannot be nested.
Msg 8164, Level 16, State 1, Line 5

這是避免游標和嵌套 exec 的稍微簡單的方法：

SET NOCOUNT ON;

CREATE TABLE #to
(
 DBName SYSNAME,
 FileCount INT
);

DECLARE @v INT;
SELECT @v = CONVERT(INT, PARSENAME(CONVERT(VARCHAR(32), 
 SERVERPROPERTY('ProductVersion')), 4));

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'CREATE TABLE #ti
 (
   ' + CASE WHEN @v >= 11 THEN 'RecoveryUnitId INT,' ELSE '' END + '    
   FileId int
   , FileSize nvarchar(255)
   , StartOffset nvarchar(255)
   , FSeqNo nvarchar(255)
   , Status int
   , Parity int
   , CreateLSN nvarchar(255)
);';

SELECT @sql = @sql + N'
 INSERT #ti EXEC ' + QUOTENAME(name) 
   + '.sys.sp_executesql N''DBCC LOGINFO WITH NO_INFOMSGS'';
 INSERT #to(DBName,FileCount) SELECT N''' + name + ''', COUNT(*) FROM #ti;
 TRUNCATE TABLE #ti;'
FROM sys.databases
WHERE database_id > 4 AND [state] = 0;

EXEC sp_executesql @sql;

SELECT DBName, FileCount FROM #to -- WHERE FileCount > [some threshold];

DROP TABLE #to;

引用自：https://dba.stackexchange.com/questions/25408