BI SQL # 196 : SQL Server DBA Scripts : Script to VLF count for all databases
Hi Folks,
In this article we are going to cover How to Script to VLF count for all Databases.
In this post we are going to discuss following points:
- Problem Statement of SQL Script:
- Description of SQL Script:
- SQL Script Output Column
- SQL Script Code
- SQL Script Output Screenshot
- User Level to execute
Problem Statement of SQL Script:
Script to VLF count for all databases.
Description of SQL Script:
This Script shows list of VLF(virtual Log Files) counts for all databases in Current system.
SQL Script Output Column
SQL Script Code
DECLARE @query VARCHAR(100) DECLARE @dbname SYSNAME DECLARE @vlfs INT --table variable used to 'loop' over databases DECLARE @databases TABLE (dbname SYSNAME) INSERT INTO @databases --only choose online databases SELECT NAME FROM sys.databases WHERE STATE = 0 --table variable to hold results DECLARE @vlfcounts TABLE ( dbname SYSNAME ,vlfcount INT ) --table varioable to capture DBCC loginfo output DECLARE @dbccloginfo TABLE ( fileid TINYINT ,file_size BIGINT ,start_offset BIGINT ,fseqno INT ,[status] TINYINT ,parity TINYINT ,create_lsn NUMERIC(25, 0) ) WHILE EXISTS ( SELECT TOP 1 dbname FROM @databases ) BEGIN SET @dbname = ( SELECT TOP 1 dbname FROM @databases ) SET @query = 'dbcc loginfo (' + '''' + @dbname + ''') ' INSERT INTO @dbccloginfo EXEC (@query) SET @vlfs = @@rowcount INSERT @vlfcounts VALUES ( @dbname ,@vlfs ) DELETE FROM @databases WHERE dbname = @dbname END --output the full list SELECT dbname ,vlfcount FROM @vlfcounts ORDER BY dbname
SQL Script Output Screenshot
User Level to execute
- 200
Hope you will like to How to Script to VLF count for all Databases.
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :
For More information related to BI World visit my Mentalist Blog
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Infographic Mentalist >> Image worth explaining thousand Words
Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ
DBA Mentalist >>Advance SQL Server Blog
Microsoft BI Mentalist >> MS BI Development Update
Connect With me on
SQL Server Mentalist 