Today’s script will execute DBCC LOGINFO for all user databases. This is a script I use to monitor the number of Virtual Log Files (VLF’s). Kimberly Tripp has a great article on VLF’s here.
/*
-----------------------------------------------------------------
User Database VLF Count
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.com
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
-- Set database context
USE master;
GO
-- Declare variables
DECLARE @DatabaseName VARCHAR(100)
DECLARE @MinDatabaseID INT
DECLARE @MaxDatabaseID INT
DECLARE @SQL VARCHAR(200)
-- Check for temporary table and drop it if it exists
IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL
DROP TABLE [#Database] ;
-- Create temporary table
CREATE TABLE #Database
(
ID INT IDENTITY(1, 1),
DatabaseName VARCHAR(100)
)
-- Check for existing user databases
IF EXISTS ( SELECT name
FROM sys.databases
WHERE database_id > 4
AND name NOT IN ( 'ReportServer', 'ReportServerTempDB',
'distribution' ) )
BEGIN
-- Insert all database names into a temporary table
INSERT INTO #Database ( DatabaseName )
SELECT name
FROM sys.databases
WHERE database_id > 4
AND name NOT IN ( 'ReportServer', 'ReportServerTempDB',
'distribution' )
-- Set variables for the loop
SELECT @MinDatabaseID = MIN(ID),
@MaxDatabaseID = MAX(ID)
FROM #Database
-- Begin loop
WHILE @MinDatabaseID <= @MaxDatabaseID
BEGIN
-- Get databaseName
SELECT @DatabaseName = DatabaseName
FROM #Database
WHERE ID = @MinDatabaseID
-- Build command
SET @SQL = 'DBCC LOGINFO(' + '''' + @DatabaseName + ''''
+ ');'
-- Try Catch block to execute SQL and handle errors
BEGIN TRY
-- Execute SQL
EXEC ( @SQL
)
PRINT 'DBCC LOGINFO RUN FOR ' + @DatabaseName
END TRY
BEGIN CATCH
SELECT @DatabaseName,
message_id,
severity,
[text],
@SQL
FROM sys.messages
WHERE message_id = @@ERROR
AND language_id = 1033 -- British English
END CATCH
--Get the next databasename ID
SET @MinDatabaseID = @MinDatabaseID + 1
-- End loop
END
END
GO
Enjoy!
Chris
