Detect Databases with High VLF Count

2019-04-09 · by Hannah Vernon · in recovery, tools

Introduction

Broadly speaking, the recovery process for SQL Server consists of reading the transaction log, rolling forward committed transactions, and rolling back uncommitted transactions, since the last checkpoint. The SQL Server Transaction Log is segmented into a varying number of virtual log files, or VLFs. Reading log files in preparation for recovery is time-consuming on databases with high VLF counts. This post provides a T-SQL script for detecting databases with more than 2,000 Virtual Log Files, and shows how to automate that script with SQL Server Agent for ongoing monitoring.

The USS Monitor – not the kind of monitoring in this post,
but still interesting enough!

One-off Script

First, the script itself. Use this on an ad-hoc basis to check for databases with more than 2,000 VLFs, when you suspect you have mis-configured databases.

The script runs on SQL Server 2008 and higher, and is compatible with case-sensitive servers. For SQL Server 2016 SP2+, the script uses the sys.dm_db_log_info Dynamic Management Function. On earlier versions of SQL Server, the script uses the DBCC LOGINFO database console command.

/*
    Summary:        Detects databases with VLF count greater than 2,000
    By:             Hannah Vernon
    Source:         https://www.sqlserverscience.com/tools/detect-databases-high-vlf-count
    Compatibility:  SQL Server 2008+
*/
SET NOCOUNT ON;
DECLARE @VersionMajor int;
DECLARE @VersionMinor int;
SET @VersionMajor = CONVERT(int, SERVERPROPERTY('ProductMajorVersion'));
SET @VersionMinor = CONVERT(int, SERVERPROPERTY('ProductMinorVersion'));
DECLARE @cmd nvarchar(max);
DECLARE @dbID int;
SET @cmd = '';
IF OBJECT_ID(N'tempdb..#SummaryInfo', N'U') IS NOT NULL
BEGIN
    DROP TABLE #SummaryInfo;
END
CREATE TABLE #SummaryInfo
(
    DatabaseName       sysname       NOT NULL
        PRIMARY KEY CLUSTERED
    , VLFCount         int           NOT NULL
    , AverageVLFSizeMB decimal(10,2) NOT NULL
    , MinVLFSizeMB     decimal(10,2) NOT NULL
    , MaxVLFSizeMB     decimal(10,2) NOT NULL
);
IF @VersionMajor >= 13 AND @VersionMinor >= 5026
BEGIN

    IF EXISTS (SELECT 1 FROM sys.system_objects o WHERE o.name = 'dm_db_log_space_usage')
    BEGIN
        --sys.dm_db_log_info in SQL Server 2016 SP2+
        IF OBJECT_ID(N'tempdb..#dm_db_log_info', N'U') IS NOT NULL
        BEGIN
            DROP TABLE #dm_db_log_info;
        END
        CREATE TABLE #dm_db_log_info
        (
              database_id         int          NOT NULL
            , file_id             smallint     NOT NULL
            , vlf_begin_offset    bigint       NOT NULL
            , vlf_size_mb         float        NOT NULL
            , vlf_sequence_number bigint       NOT NULL
            , vlf_active          bit          NOT NULL
            , vlf_status          int          NOT NULL
            , vlf_parity          tinyint      NOT NULL
            , vlf_first_lsn       nvarchar(24) NOT NULL
            , vlf_create_lsn      nvarchar(24) NOT NULL
            , CONSTRAINT dm_db_log_info_pk
                PRIMARY KEY CLUSTERED
                (database_id, file_id, vlf_sequence_number)
        );

        DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY 
        FOR
        SELECT d.database_id
        FROM sys.databases d
        WHERE d.database_id >= 4
            AND d.state_desc = N'ONLINE'
            AND d.user_access_desc = N'MULTI_USER';
        OPEN cur;
        FETCH NEXT FROM cur INTO @dbID;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            INSERT INTO #dm_db_log_info WITH (TABLOCKX)
            SELECT *
            FROM sys.dm_db_log_info(@dbID) ddli;
            FETCH NEXT FROM cur INTO @dbID;
        END
        CLOSE cur;
        DEALLOCATE cur;

        INSERT INTO #SummaryInfo WITH (TABLOCKX)
        SELECT DatabaseName = d.name
            , VLFCount = COUNT(1)
            , AverageVLFSize = AVG(ddli.vlf_size_mb)
            , MinVLFSize = MIN(ddli.vlf_size_mb)
            , MaxVLFSize = MAX(ddli.vlf_size_mb)
        FROM #dm_db_log_info ddli
            INNER JOIN sys.databases d ON ddli.database_id = d.database_id
        GROUP BY d.name;
    END
END
ELSE
BEGIN --use DBCC LOGINFO instead
    IF OBJECT_ID(N'tempdb..##LogInfo', N'U') IS NOT NULL
    BEGIN
        DROP TABLE ##LogInfo;
    END
    CREATE TABLE ##LogInfo 
    (
        DatabaseId    int          NULL
    );
    IF @VersionMajor >= 11
    BEGIN
        ALTER TABLE ##LogInfo
        ADD RecoveryUnitId int     NOT NULL --available in SQL Server 2012+;
    END
    ALTER TABLE ##LogInfo
    ADD
          FileId      smallint     NOT NULL
        , FileSize    float        NOT NULL
        , StartOffset bigint       NOT NULL
        , FSeqNo      bigint       NOT NULL
        , Status      int          NOT NULL
        , Parity      tinyint      NOT NULL
        , CreateLSN   nvarchar(24) NOT NULL;

    CREATE CLUSTERED INDEX LogInfo_pk
    ON ##LogInfo (FileId, FSeqNo);

    DECLARE @cmdi nvarchar(max);
    DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY 
    FOR
    SELECT d.database_id
    FROM sys.databases d
    WHERE d.database_id >= 4
        AND d.state_desc = N'ONLINE'
        AND d.user_access_desc = N'MULTI_USER';
    OPEN cur;
    FETCH NEXT FROM cur INTO @dbID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @cmd = N'DBCC LOGINFO(' + CONVERT(nvarchar(11), @dbID) + N') WITH NO_INFOMSGS;'
        IF @VersionMajor >= 11 
        BEGIN
            SET @cmdi = N'INSERT INTO ##LogInfo WITH (TABLOCKX) (RecoveryUnitId, FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)
EXEC (''' + @cmd + N''');'
            EXEC sys.sp_executesql @cmdi;
        END
        ELSE
        BEGIN
            SET @cmdi = N'INSERT INTO ##LogInfo WITH (TABLOCKX) (FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)
EXEC (''' + @cmd + N''');'
            EXEC sys.sp_executesql @cmdi;
        END
        UPDATE ##LogInfo SET DatabaseId = @dbID WHERE DatabaseId IS NULL;
        FETCH NEXT FROM cur INTO @dbID;
    END
    CLOSE cur;
    DEALLOCATE cur;

    INSERT INTO #SummaryInfo WITH (TABLOCKX)
    SELECT DatabaseName = d.name
        , VLFCount = COUNT(1)
        , AverageVLFSize = AVG(li.FileSize / 1048576.0)
        , MinVLFSize = MIN(li.FileSize / 1048576.0)
        , MaxVLFSize = MAX(li.FileSize / 1048576.0)
    FROM ##LogInfo li
        INNER JOIN sys.databases d ON li.DatabaseId = d.database_id
    GROUP BY d.name;
END

SELECT ServerName = @@SERVERNAME
    , si.*
FROM #SummaryInfo si
WHERE si.VLFCount > 2000
ORDER BY si.DatabaseName;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

Summary: Detects databases with VLF count greater than 2,000

By: Hannah Vernon

Source: https://www.sqlserverscience.com/tools/detect-databases-high-vlf-count

Compatibility: SQL Server 2008+

SET NOCOUNT ON;

DECLARE @VersionMajor int;

DECLARE @VersionMinor int;

SET @VersionMajor = CONVERT(int, SERVERPROPERTY('ProductMajorVersion'));

SET @VersionMinor = CONVERT(int, SERVERPROPERTY('ProductMinorVersion'));

DECLARE @cmd nvarchar(max);

DECLARE @dbID int;

SET @cmd = '';

IF OBJECT_ID(N'tempdb..#SummaryInfo', N'U') IS NOT NULL

BEGIN

DROP TABLE #SummaryInfo;

END

CREATE TABLE #SummaryInfo

(

DatabaseName sysname NOT NULL

PRIMARY KEY CLUSTERED

, VLFCount int NOT NULL

, AverageVLFSizeMB decimal(10,2) NOT NULL

, MinVLFSizeMB decimal(10,2) NOT NULL

, MaxVLFSizeMB decimal(10,2) NOT NULL

);

IF @VersionMajor >= 13 AND @VersionMinor >= 5026

BEGIN

IF EXISTS (SELECT 1 FROM sys.system_objects o WHERE o.name = 'dm_db_log_space_usage')

BEGIN

--sys.dm_db_log_info in SQL Server 2016 SP2+

IF OBJECT_ID(N'tempdb..#dm_db_log_info', N'U') IS NOT NULL

BEGIN

DROP TABLE #dm_db_log_info;

END

CREATE TABLE #dm_db_log_info

(

database_id int NOT NULL

, file_id smallint NOT NULL

, vlf_begin_offset bigint NOT NULL

, vlf_size_mb float NOT NULL

, vlf_sequence_number bigint NOT NULL

, vlf_active bit NOT NULL

, vlf_status int NOT NULL

, vlf_parity tinyint NOT NULL

, vlf_first_lsn nvarchar(24) NOT NULL

, vlf_create_lsn nvarchar(24) NOT NULL

, CONSTRAINT dm_db_log_info_pk

PRIMARY KEY CLUSTERED

(database_id, file_id, vlf_sequence_number)

);

DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY

FOR

SELECT d.database_id

FROM sys.databases d

WHERE d.database_id >= 4

AND d.state_desc = N'ONLINE'

AND d.user_access_desc = N'MULTI_USER';

OPEN cur;

FETCH NEXT FROM cur INTO @dbID;

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO #dm_db_log_info WITH (TABLOCKX)

SELECT *

FROM sys.dm_db_log_info(@dbID) ddli;

FETCH NEXT FROM cur INTO @dbID;

END

CLOSE cur;

DEALLOCATE cur;

INSERT INTO #SummaryInfo WITH (TABLOCKX)

SELECT DatabaseName = d.name

, VLFCount = COUNT(1)

, AverageVLFSize = AVG(ddli.vlf_size_mb)

, MinVLFSize = MIN(ddli.vlf_size_mb)

, MaxVLFSize = MAX(ddli.vlf_size_mb)

FROM #dm_db_log_info ddli

INNER JOIN sys.databases d ON ddli.database_id = d.database_id

GROUP BY d.name;

END

ELSE

BEGIN --use DBCC LOGINFO instead

IF OBJECT_ID(N'tempdb..##LogInfo', N'U') IS NOT NULL

BEGIN

DROP TABLE ##LogInfo;

END

CREATE TABLE ##LogInfo

(

DatabaseId int NULL

);

IF @VersionMajor >= 11

BEGIN

ALTER TABLE ##LogInfo

ADD RecoveryUnitId int NOT NULL --available in SQL Server 2012+;

END

ALTER TABLE ##LogInfo

ADD

FileId smallint NOT NULL

, FileSize float NOT NULL

, StartOffset bigint NOT NULL

, FSeqNo bigint NOT NULL

, Status int NOT NULL

, Parity tinyint NOT NULL

, CreateLSN nvarchar(24) NOT NULL;

CREATE CLUSTERED INDEX LogInfo_pk

ON ##LogInfo (FileId, FSeqNo);

DECLARE @cmdi nvarchar(max);

DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY

FOR

SELECT d.database_id

FROM sys.databases d

WHERE d.database_id >= 4

AND d.state_desc = N'ONLINE'

AND d.user_access_desc = N'MULTI_USER';

OPEN cur;

FETCH NEXT FROM cur INTO @dbID;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = N'DBCC LOGINFO(' + CONVERT(nvarchar(11), @dbID) + N') WITH NO_INFOMSGS;'

IF @VersionMajor >= 11

BEGIN

SET @cmdi = N'INSERT INTO ##LogInfo WITH (TABLOCKX) (RecoveryUnitId, FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)

EXEC (''' + @cmd + N''');'

EXEC sys.sp_executesql @cmdi;

END

ELSE

BEGIN

SET @cmdi = N'INSERT INTO ##LogInfo WITH (TABLOCKX) (FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)

EXEC (''' + @cmd + N''');'

EXEC sys.sp_executesql @cmdi;

END

UPDATE ##LogInfo SET DatabaseId = @dbID WHERE DatabaseId IS NULL;

FETCH NEXT FROM cur INTO @dbID;

END

CLOSE cur;

DEALLOCATE cur;

INSERT INTO #SummaryInfo WITH (TABLOCKX)

SELECT DatabaseName = d.name

, VLFCount = COUNT(1)

, AverageVLFSize = AVG(li.FileSize / 1048576.0)

, MinVLFSize = MIN(li.FileSize / 1048576.0)

, MaxVLFSize = MAX(li.FileSize / 1048576.0)

FROM ##LogInfo li

INNER JOIN sys.databases d ON li.DatabaseId = d.database_id

GROUP BY d.name;

END

SELECT ServerName = @@SERVERNAME

, si.*

FROM #SummaryInfo si

WHERE si.VLFCount > 2000

ORDER BY si.DatabaseName;

Output for the above script will look like this:

╔════════════╦══════════════╦══════════╦══════════════════╦══════════════╦══════════════╗
║ ServerName ║ DatabaseName ║ VLFCount ║ AverageVLFSizeMB ║ MinVLFSizeMB ║ MaxVLFSizeMB ║
╠════════════╬══════════════╬══════════╬══════════════════╬══════════════╬══════════════╣
║ MyServer   ║ vlf_test     ║     2970 ║             9.91 ║         2.44 ║        10.00 ║
╚════════════╩══════════════╩══════════╩══════════════════╩══════════════╩══════════════╝

SQL Server Agent Job containing the Above Script

The following script incorporates the above script into a SQL Server Agent job scheduled to run once daily. This job sends an email listing any databases with more than 2,000 VLFs. Edit the script to include a valid email address and Database Mail Profile Name.

BEGIN TRANSACTION
DECLARE @jobId binary(16);
DECLARE @rc int;
    IF EXISTS (
    SELECT 1
    FROM msdb.dbo.sysjobs sj
    WHERE sj.name = 'High VLF Count Alert'
    )
BEGIN
    EXEC @rc = msdb.dbo.sp_delete_job @job_name = N'High VLF Count Alert';
END
IF (@@ERROR <> 0 OR @rc <> 0) GOTO rollback_script;

EXEC @rc = msdb.dbo.sp_add_job @job_name = N'High VLF Count Alert'
    , @enabled = 1
    , @notify_level_eventlog = 0
    , @notify_level_email = 2
    , @notify_level_page = 2
    , @delete_level = 0
    , @category_name = N'[Uncategorized (Local)]'
    , @owner_login_name = N'sa'
    , @notify_email_operator_name = N'DBA'
    , @job_id = @jobId OUTPUT;

IF (@@ERROR <> 0 OR @rc <> 0) GOTO rollback_script;

EXEC @rc = msdb.dbo.sp_add_jobserver @job_name = N'High VLF Count Alert', @server_name = N'(local)';

EXEC @rc = msdb.dbo.sp_add_jobstep @job_name = N'High VLF Count Alert', @step_name = N'High VLF Count Alert Step 1'
    , @step_id = 1
    , @cmdexec_success_code = 0
    , @on_success_action = 1
    , @on_fail_action = 2
    , @retry_attempts = 0
    , @retry_interval = 0
    , @os_run_priority = 0, @subsystem = N'TSQL'
    , @command = N'/*
    Summary:        Detects databases with VLF count greater than 2,000
    By:             Hannah Vernon
    Source:         https://www.sqlserverscience.com/tools/detect-databases-high-vlf-count
    Compatibility:  SQL Server 2008+
*/
SET NOCOUNT ON;
DECLARE @VersionMajor int;
DECLARE @VersionMinor int;
SET @VersionMajor = CONVERT(int, SERVERPROPERTY(''ProductMajorVersion''));
SET @VersionMinor = CONVERT(int, SERVERPROPERTY(''ProductMinorVersion''));
DECLARE @cmd nvarchar(max);
DECLARE @dbID int;
SET @cmd = '''';
IF OBJECT_ID(N''tempdb..#SummaryInfo'', N''U'') IS NOT NULL
BEGIN
    DROP TABLE #SummaryInfo;
END
CREATE TABLE #SummaryInfo
(
    DatabaseName       sysname       NOT NULL
        PRIMARY KEY CLUSTERED
    , VLFCount         int           NOT NULL
    , AverageVLFSizeMB decimal(10,2) NOT NULL
    , MinVLFSizeMB     decimal(10,2) NOT NULL
    , MaxVLFSizeMB     decimal(10,2) NOT NULL
);
IF @VersionMajor >= 13 AND @VersionMinor >= 5026
BEGIN

    IF EXISTS (SELECT 1 FROM sys.system_objects o WHERE o.name = ''dm_db_log_space_usage'')
    BEGIN
        --sys.dm_db_log_info in SQL Server 2016 SP2+
        IF OBJECT_ID(N''tempdb..#dm_db_log_info'', N''U'') IS NOT NULL
        BEGIN
            DROP TABLE #dm_db_log_info;
        END
        CREATE TABLE #dm_db_log_info
        (
              database_id         int          NOT NULL
            , file_id             smallint     NOT NULL
            , vlf_begin_offset    bigint       NOT NULL
            , vlf_size_mb         float        NOT NULL
            , vlf_sequence_number bigint       NOT NULL
            , vlf_active          bit          NOT NULL
            , vlf_status          int          NOT NULL
            , vlf_parity          tinyint      NOT NULL
            , vlf_first_lsn       nvarchar(24) NOT NULL
            , vlf_create_lsn      nvarchar(24) NOT NULL
            , CONSTRAINT dm_db_log_info_pk
                PRIMARY KEY CLUSTERED
                (database_id, file_id, vlf_sequence_number)
        );

        DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY 
        FOR
        SELECT d.database_id
        FROM sys.databases d
        WHERE d.database_id >= 4
            AND d.state_desc = N''ONLINE''
            AND d.user_access_desc = N''MULTI_USER'';
        OPEN cur;
        FETCH NEXT FROM cur INTO @dbID;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            INSERT INTO #dm_db_log_info WITH (TABLOCKX)
            SELECT *
            FROM sys.dm_db_log_info(@dbID) ddli;
            FETCH NEXT FROM cur INTO @dbID;
        END
        CLOSE cur;
        DEALLOCATE cur;

        INSERT INTO #SummaryInfo WITH (TABLOCKX)
        SELECT DatabaseName = d.name
            , VLFCount = COUNT(1)
            , AverageVLFSize = AVG(ddli.vlf_size_mb)
            , MinVLFSize = MIN(ddli.vlf_size_mb)
            , MaxVLFSize = MAX(ddli.vlf_size_mb)
        FROM #dm_db_log_info ddli
            INNER JOIN sys.databases d ON ddli.database_id = d.database_id
        GROUP BY d.name;
    END
END
ELSE
BEGIN --use DBCC LOGINFO instead
    IF OBJECT_ID(N''tempdb..##LogInfo'', N''U'') IS NOT NULL
    BEGIN
        DROP TABLE ##LogInfo;
    END
    CREATE TABLE ##LogInfo 
    (
        DatabaseId    int          NULL
    );
    IF @VersionMajor >= 11
    BEGIN
        ALTER TABLE ##LogInfo
        ADD RecoveryUnitId int     NOT NULL --available in SQL Server 2012+;
    END
    ALTER TABLE ##LogInfo
    ADD
          FileId      smallint     NOT NULL
        , FileSize    float        NOT NULL
        , StartOffset bigint       NOT NULL
        , FSeqNo      bigint       NOT NULL
        , Status      int          NOT NULL
        , Parity      tinyint      NOT NULL
        , CreateLSN   nvarchar(24) NOT NULL;

    CREATE CLUSTERED INDEX LogInfo_pk
    ON ##LogInfo (FileId, FSeqNo);

    DECLARE @cmdi nvarchar(max);
    DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY 
    FOR
    SELECT d.database_id
    FROM sys.databases d
    WHERE d.database_id >= 4
        AND d.state_desc = N''ONLINE''
        AND d.user_access_desc = N''MULTI_USER'';
    OPEN cur;
    FETCH NEXT FROM cur INTO @dbID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @cmd = N''DBCC LOGINFO('' + CONVERT(nvarchar(11), @dbID) + N'') WITH NO_INFOMSGS;''
        IF @VersionMajor >= 11 
        BEGIN
            SET @cmdi = N''INSERT INTO ##LogInfo WITH (TABLOCKX) (RecoveryUnitId, FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)
EXEC ('''''' + @cmd + N'''''');''
            EXEC sys.sp_executesql @cmdi;
        END
        ELSE
        BEGIN
            SET @cmdi = N''INSERT INTO ##LogInfo WITH (TABLOCKX) (FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)
EXEC ('''''' + @cmd + N'''''');''
            EXEC sys.sp_executesql @cmdi;
        END
        UPDATE ##LogInfo SET DatabaseId = @dbID WHERE DatabaseId IS NULL;
        FETCH NEXT FROM cur INTO @dbID;
    END
    CLOSE cur;
    DEALLOCATE cur;

    INSERT INTO #SummaryInfo WITH (TABLOCKX)
    SELECT DatabaseName = d.name
        , VLFCount = COUNT(1)
        , AverageVLFSize = AVG(li.FileSize / 1048576.0)
        , MinVLFSize = MIN(li.FileSize / 1048576.0)
        , MaxVLFSize = MAX(li.FileSize / 1048576.0)
    FROM ##LogInfo li
        INNER JOIN sys.databases d ON li.DatabaseId = d.database_id
    GROUP BY d.name;
END

DECLARE @msg_body nvarchar(max);
SET @msg_body = N'''';
SELECT @msg_body = @msg_body + CASE WHEN @msg_body = N'''' THEN N'''' ELSE N''
'' END + N''<tr><td>'' + CONVERT(nvarchar(max), @@SERVERNAME) + N''</td>''
    + N''<td>'' + CONVERT(nvarchar(max), si.DatabaseName) + N''</td>''
    + N''<td>'' + CONVERT(nvarchar(max), si.VLFCount) + N''</td>''
    + N''<td>'' + CONVERT(nvarchar(max), si.MinVLFSizeMB) + N''</td>''
    + N''<td>'' + CONVERT(nvarchar(max), si.AverageVLFSizeMB) + N''</td>''
    + N''<td>'' + CONVERT(nvarchar(max), si.MaxVLFSizeMB) + N''</td></tr>''
FROM #SummaryInfo si
WHERE si.VLFCount > 2000
ORDER BY si.DatabaseName;

SET @msg_body = N''<html>
<head>
    <title>Databases with high VLF count</title>
    <style>
        body { font-family: Segoe UI,Verdana,Tahoma; font-size: 10pt; }
        thead tr td { background-color: #948d8d; padding: 2px; border: solid; border-color: black; border-width: 2px; margin: 0px; text-align:center; }
        tbody tr td { background-color: #eeeeee; padding-left: 5px; padding-right: 5px; padding-bottom: 2px; border-width: 0px; margin: 0px; }
        tbody tr { padding-bottom: 0px; padding-top: 0px; }
    </style>
</head>
<body>
    The following databases have more than 2,000 VLFs (Virtual Log Files).<br /><br />
    Consider shrinking and resizing the LOG file per the details shown at 
    <a href="https://www.sqlserverscience.com/recovery/fix-high-vlf-count" target="_blank" rel="noopener noreferrer">
    https://www.sqlserverscience.com/recovery/fix-high-vlf-count</a>
    <table>
        <thead>
            <tr><td>Server Name</td><td>Database Name</td><td>VLF Count</td><td>Min VLF Size (MB)</td><td>Average VLF Size</td><td>Max VLF Size (MB)</td></tr>
        </thead>
        <tbody>
            '' + @msg_body + N''
        </tbody>
    </table>
    The above notification was automatically sent by a SQL Server Agent Job on '' + @@SERVERNAME + N''.
</body>
</html>'';

PRINT @msg_body;

DECLARE @subject nvarchar(1000);
SET @subject = ''High VLF Count Notification from SQL Server: '' + @@SERVERNAME;

IF EXISTS (
    SELECT 1
    FROM #SummaryInfo si
    WHERE si.VLFCount >= 2000
    )
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
          @profile_name = ''DBA''
        , @recipients = ''<your_email_address_goes_here>''
        , @subject = @subject
        , @body = @msg_body
        , @body_format = ''HTML'';
END'
    , @database_name = N'master'
    , @flags = 0;

IF (@@ERROR <> 0 OR @rc <> 0) GOTO rollback_script;

EXEC @rc = msdb.dbo.sp_update_job @job_name = N'High VLF Count Alert'
    , @enabled = 1
    , @start_step_id = 1
    , @notify_level_eventlog = 0
    , @notify_level_email = 2
    , @notify_level_page = 2
    , @delete_level = 0
    , @description = N''
    , @category_name = N'[Uncategorized (Local)]'
    , @owner_login_name = N'sa'
    , @notify_email_operator_name = N'DBA'
    , @notify_page_operator_name = N'';

IF (@@ERROR <> 0 OR @rc <> 0) GOTO rollback_script;

EXEC @rc = msdb.dbo.sp_add_jobschedule @job_name = N'High VLF Count Alert'
    , @name = N'High VLF Count Alert Schedule'
    , @enabled = 1
    , @freq_type = 4
    , @freq_interval = 1
    , @freq_subday_type = 1
    , @freq_subday_interval = 0
    , @freq_relative_interval = 0
    , @freq_recurrence_factor = 1
    , @active_start_date = 20190403
    , @active_end_date = 99991231
    , @active_start_time = 90000
    , @active_end_time = 235959;

IF (@@ERROR <> 0 OR @rc <> 0) GOTO rollback_script;

COMMIT TRANSACTION;

GOTO end_of_script;

rollback_script:
ROLLBACK TRANSACTION;

end_of_script:

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

BEGIN TRANSACTION

DECLARE @jobId binary(16);

DECLARE @rc int;

IF EXISTS (

SELECT 1

FROM msdb.dbo.sysjobs sj

WHERE sj.name = 'High VLF Count Alert'

)

BEGIN

EXEC @rc = msdb.dbo.sp_delete_job @job_name = N'High VLF Count Alert';

END

IF (@@ERROR <> 0 OR @rc <> 0) GOTO rollback_script;

EXEC @rc = msdb.dbo.sp_add_job @job_name = N'High VLF Count Alert'

, @enabled = 1

, @notify_level_eventlog = 0

, @notify_level_email = 2

, @notify_level_page = 2

, @delete_level = 0

, @category_name = N'[Uncategorized (Local)]'

, @owner_login_name = N'sa'

, @notify_email_operator_name = N'DBA'

, @job_id = @jobId OUTPUT;

IF (@@ERROR <> 0 OR @rc <> 0) GOTO rollback_script;

EXEC @rc = msdb.dbo.sp_add_jobserver @job_name = N'High VLF Count Alert', @server_name = N'(local)';

EXEC @rc = msdb.dbo.sp_add_jobstep @job_name = N'High VLF Count Alert', @step_name = N'High VLF Count Alert Step 1'

, @step_id = 1

, @cmdexec_success_code = 0

, @on_success_action = 1

, @on_fail_action = 2

, @retry_attempts = 0

, @retry_interval = 0

, @os_run_priority = 0, @subsystem = N'TSQL'

, @command = N'/*

Summary: Detects databases with VLF count greater than 2,000

By: Hannah Vernon

Source: https://www.sqlserverscience.com/tools/detect-databases-high-vlf-count

Compatibility: SQL Server 2008+

SET NOCOUNT ON;

DECLARE @VersionMajor int;

DECLARE @VersionMinor int;

SET @VersionMajor = CONVERT(int, SERVERPROPERTY(''ProductMajorVersion''));

SET @VersionMinor = CONVERT(int, SERVERPROPERTY(''ProductMinorVersion''));

DECLARE @cmd nvarchar(max);

DECLARE @dbID int;

SET @cmd = '''';

IF OBJECT_ID(N''tempdb..#SummaryInfo'', N''U'') IS NOT NULL

BEGIN

DROP TABLE #SummaryInfo;

END

CREATE TABLE #SummaryInfo

(

DatabaseName sysname NOT NULL

PRIMARY KEY CLUSTERED

, VLFCount int NOT NULL

, AverageVLFSizeMB decimal(10,2) NOT NULL

, MinVLFSizeMB decimal(10,2) NOT NULL

, MaxVLFSizeMB decimal(10,2) NOT NULL

);

IF @VersionMajor >= 13 AND @VersionMinor >= 5026

BEGIN

IF EXISTS (SELECT 1 FROM sys.system_objects o WHERE o.name = ''dm_db_log_space_usage'')

BEGIN

--sys.dm_db_log_info in SQL Server 2016 SP2+

IF OBJECT_ID(N''tempdb..#dm_db_log_info'', N''U'') IS NOT NULL

BEGIN

DROP TABLE #dm_db_log_info;

END

CREATE TABLE #dm_db_log_info

(

database_id int NOT NULL

, file_id smallint NOT NULL

, vlf_begin_offset bigint NOT NULL

, vlf_size_mb float NOT NULL

, vlf_sequence_number bigint NOT NULL

, vlf_active bit NOT NULL

, vlf_status int NOT NULL

, vlf_parity tinyint NOT NULL

, vlf_first_lsn nvarchar(24) NOT NULL

, vlf_create_lsn nvarchar(24) NOT NULL

, CONSTRAINT dm_db_log_info_pk

PRIMARY KEY CLUSTERED

(database_id, file_id, vlf_sequence_number)

);

DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY

FOR

SELECT d.database_id

FROM sys.databases d

WHERE d.database_id >= 4

AND d.state_desc = N''ONLINE''

AND d.user_access_desc = N''MULTI_USER'';

OPEN cur;

FETCH NEXT FROM cur INTO @dbID;

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO #dm_db_log_info WITH (TABLOCKX)

SELECT *

FROM sys.dm_db_log_info(@dbID) ddli;

FETCH NEXT FROM cur INTO @dbID;

END

CLOSE cur;

DEALLOCATE cur;

INSERT INTO #SummaryInfo WITH (TABLOCKX)

SELECT DatabaseName = d.name

, VLFCount = COUNT(1)

, AverageVLFSize = AVG(ddli.vlf_size_mb)

, MinVLFSize = MIN(ddli.vlf_size_mb)

, MaxVLFSize = MAX(ddli.vlf_size_mb)

FROM #dm_db_log_info ddli

INNER JOIN sys.databases d ON ddli.database_id = d.database_id

GROUP BY d.name;

END

ELSE

BEGIN --use DBCC LOGINFO instead

IF OBJECT_ID(N''tempdb..##LogInfo'', N''U'') IS NOT NULL

BEGIN

DROP TABLE ##LogInfo;

END

CREATE TABLE ##LogInfo

(

DatabaseId int NULL

);

IF @VersionMajor >= 11

BEGIN

ALTER TABLE ##LogInfo

ADD RecoveryUnitId int NOT NULL --available in SQL Server 2012+;

END

ALTER TABLE ##LogInfo

ADD

FileId smallint NOT NULL

, FileSize float NOT NULL

, StartOffset bigint NOT NULL

, FSeqNo bigint NOT NULL

, Status int NOT NULL

, Parity tinyint NOT NULL

, CreateLSN nvarchar(24) NOT NULL;

CREATE CLUSTERED INDEX LogInfo_pk

ON ##LogInfo (FileId, FSeqNo);

DECLARE @cmdi nvarchar(max);

DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY

FOR

SELECT d.database_id

FROM sys.databases d

WHERE d.database_id >= 4

AND d.state_desc = N''ONLINE''

AND d.user_access_desc = N''MULTI_USER'';

OPEN cur;

FETCH NEXT FROM cur INTO @dbID;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = N''DBCC LOGINFO('' + CONVERT(nvarchar(11), @dbID) + N'') WITH NO_INFOMSGS;''

IF @VersionMajor >= 11

BEGIN

SET @cmdi = N''INSERT INTO ##LogInfo WITH (TABLOCKX) (RecoveryUnitId, FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)

EXEC ('''''' + @cmd + N'''''');''

EXEC sys.sp_executesql @cmdi;

END

ELSE

BEGIN

SET @cmdi = N''INSERT INTO ##LogInfo WITH (TABLOCKX) (FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)

EXEC ('''''' + @cmd + N'''''');''

EXEC sys.sp_executesql @cmdi;

END

UPDATE ##LogInfo SET DatabaseId = @dbID WHERE DatabaseId IS NULL;

FETCH NEXT FROM cur INTO @dbID;

END

CLOSE cur;

DEALLOCATE cur;

INSERT INTO #SummaryInfo WITH (TABLOCKX)

SELECT DatabaseName = d.name

, VLFCount = COUNT(1)

, AverageVLFSize = AVG(li.FileSize / 1048576.0)

, MinVLFSize = MIN(li.FileSize / 1048576.0)

, MaxVLFSize = MAX(li.FileSize / 1048576.0)

FROM ##LogInfo li

INNER JOIN sys.databases d ON li.DatabaseId = d.database_id

GROUP BY d.name;

END

DECLARE @msg_body nvarchar(max);

SET @msg_body = N'''';

SELECT @msg_body = @msg_body + CASE WHEN @msg_body = N'''' THEN N'''' ELSE N''

'' END + N''<tr><td>'' + CONVERT(nvarchar(max), @@SERVERNAME) + N''</td>''

+ N''<td>'' + CONVERT(nvarchar(max), si.DatabaseName) + N''</td>''

+ N''<td>'' + CONVERT(nvarchar(max), si.VLFCount) + N''</td>''

+ N''<td>'' + CONVERT(nvarchar(max), si.MinVLFSizeMB) + N''</td>''

+ N''<td>'' + CONVERT(nvarchar(max), si.AverageVLFSizeMB) + N''</td>''

+ N''<td>'' + CONVERT(nvarchar(max), si.MaxVLFSizeMB) + N''</td></tr>''

FROM #SummaryInfo si

WHERE si.VLFCount > 2000

ORDER BY si.DatabaseName;

SET @msg_body = N''<html>

<head>

<title>Databases with high VLF count</title>

<style>

body { font-family: Segoe UI,Verdana,Tahoma; font-size: 10pt; }

thead tr td { background-color: #948d8d; padding: 2px; border: solid; border-color: black; border-width: 2px; margin: 0px; text-align:center; }

tbody tr td { background-color: #eeeeee; padding-left: 5px; padding-right: 5px; padding-bottom: 2px; border-width: 0px; margin: 0px; }

tbody tr { padding-bottom: 0px; padding-top: 0px; }

</style>

</head>

<body>

The following databases have more than 2,000 VLFs (Virtual Log Files).<br /><br />

Consider shrinking and resizing the LOG file per the details shown at

https://www.sqlserverscience.com/recovery/fix-high-vlf-count</a>

<table>

<thead>

<tr><td>Server Name</td><td>Database Name</td><td>VLF Count</td><td>Min VLF Size (MB)</td><td>Average VLF Size</td><td>Max VLF Size (MB)</td></tr>

</thead>

<tbody>

'' + @msg_body + N''

</tbody>

</table>

The above notification was automatically sent by a SQL Server Agent Job on '' + @@SERVERNAME + N''.

</body>

</html>'';

PRINT @msg_body;

DECLARE @subject nvarchar(1000);

SET @subject = ''High VLF Count Notification from SQL Server: '' + @@SERVERNAME;

IF EXISTS (

SELECT 1

FROM #SummaryInfo si

WHERE si.VLFCount >= 2000

)

BEGIN

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ''DBA''

, @recipients = ''<your_email_address_goes_here>''

, @subject = @subject

, @body = @msg_body

, @body_format = ''HTML'';

END'

, @database_name = N'master'

, @flags = 0;

IF (@@ERROR <> 0 OR @rc <> 0) GOTO rollback_script;

EXEC @rc = msdb.dbo.sp_update_job @job_name = N'High VLF Count Alert'

, @enabled = 1

, @start_step_id = 1

, @notify_level_eventlog = 0

, @notify_level_email = 2

, @notify_level_page = 2

, @delete_level = 0

, @description = N''

, @category_name = N'[Uncategorized (Local)]'

, @owner_login_name = N'sa'

, @notify_email_operator_name = N'DBA'

, @notify_page_operator_name = N'';

IF (@@ERROR <> 0 OR @rc <> 0) GOTO rollback_script;

EXEC @rc = msdb.dbo.sp_add_jobschedule @job_name = N'High VLF Count Alert'

, @name = N'High VLF Count Alert Schedule'

, @enabled = 1

, @freq_type = 4

, @freq_interval = 1

, @freq_subday_type = 1

, @freq_subday_interval = 0

, @freq_relative_interval = 0

, @freq_recurrence_factor = 1

, @active_start_date = 20190403

, @active_end_date = 99991231

, @active_start_time = 90000

, @active_end_time = 235959;

IF (@@ERROR <> 0 OR @rc <> 0) GOTO rollback_script;

COMMIT TRANSACTION;

GOTO end_of_script;

rollback_script:

ROLLBACK TRANSACTION;

end_of_script:

This post is part of our series on recovery and SQL Server Tools.

6 Responses

Jiri

2019-04-10 at 9:18 AM · Reply →

Hi Hannah,

In order to make your script SQL Server 2012 (and SQL Server 20008 R2) compatible,
I replaced the following 2 lines (line #10 & #11)

SET @VersionMajor = CONVERT(int, SERVERPROPERTY('ProductMajorVersion'));
SET @VersionMinor = CONVERT(int, SERVERPROPERTY('ProductMinorVersion'));

(both of which return NULL on older versions for SQL Server)
with the following four lines

DECLARE @sqlVers numeric(4,2)
SELECT @sqlVers = left(cast(serverproperty('productversion') as varchar), 4)
SET @VersionMajor = FLOOR(@sqlVers)
SET @VersionMinor = (@sqlVers - @VersionMajor) * 100

DECLARE @sqlVers numeric(4,2)

SELECT @sqlVers = left(cast(serverproperty('productversion') as varchar), 4)

SET @VersionMajor = FLOOR(@sqlVers)

SET @VersionMinor = (@sqlVers - @VersionMajor) * 100

Hannah Vernon

2019-04-10 at 9:20 AM · Reply →

Cheers, Jiri! That should help quite a few people!

Loading...

Neeraj Kumar

2020-06-05 at 3:28 PM · Reply →

Getting Errror:

Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.

Loading...
1. Hannah Vernon
  
  2020-06-05 at 3:22 PM · Reply →
  
  What version of SQL Server are you using?
  
  Loading...
saradhi

2021-07-26 at 12:40 PM · Reply →

Hi , Thanks for the Article.

Is there a way to Schedule a SQL Agent Job to Shrink the Log file based on the high number of VLFs. and based on the recovery model

Loading...
1. Hannah Vernon
  
  2021-07-26 at 12:48 PM · Reply →
  
  You could certainly do that. Take a look at this post where I discuss a way to automate shrinking the log file to reduce the number of VLFs.
  
  Loading...

Detect Databases with High VLF Count

Introduction

One-off Script

SQL Server Agent Job containing the Above Script

Related

6 Responses

Leave a ReplyCancel reply

Search

Categories

Pages

Meta