SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


vlfs_Count_for_Each_Database


vlfs_Count_for_Each_Database

Author
Message
akhamoshioke
akhamoshioke
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 468
Comments posted to this topic are about the item vlfs_Count_for_Each_Database
andyscott
andyscott
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 131
I needed to add an additional column to the start of the #stage table - presumably for SQL 2012 - "RecoveryUnitID INT" in order to get this to run. :-)
Kim Claybaugh
Kim Claybaugh
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 509
Nice little utility – it will be helpful

I have a few databases with – (dash) in the name and the query quit in an error – I added [ ] around the 2 database name references and everything works.


N'USE ? change to N'USE [?]

LogInfo(?) change to LogInfo([?])
SQLQuest29
SQLQuest29
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1314 Visits: 4325
akhamoshioke (4/22/2014)
Comments posted to this topic are about the item <A HREF="/scripts/transaction_log/109527/">vlfs_Count_for_Each_Database</A>


why are you using undocumented sp_msforeachdb ? There are better ways of doing it.

Aaron Bertrand has a better way of doing it (due to the limitations of the undocumented SP)

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor :-)
Lee Linares
Lee Linares
SSC-Addicted
SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)

Group: General Forum Members
Points: 485 Visits: 1462
Thanks for taking the time and effort to share this code. Very useful. I did run into one problem though. I received this error but ONLY on a single database: Msg 2812, Level 16, State 62, Line 2 Could not find stored procedure 'sp_executeSQL'.
The problem was that the one database has a case-sensitive collation. I changed 'sp_executeSQL' to 'sp_executesql' and everything was fine.
Thanks again.

Lee
Jorge Serres
Jorge Serres
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 200
Got some blood here:

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

to correct it
Add column <RecoveryUnitID INT> to TABLE #stage


CREATE TABLE #stage
(
RecoveryUnitID INT --- <<<<<<<<<<<<<<<<<<<<<
, FileID INT
, FileSize BIGINT
, StartOffset BIGINT
, FSeqNo BIGINT
, [Status] BIGINT
, Parity BIGINT
, CreateLSN NUMERIC(38)
) ;
GO
Rich Holt
Rich Holt
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 194
If you want to see more information than just the virtual log files count, you can include DBCC SQLPERF(logspace) and include the VLF counts like this. I prefer to test for the existence of temp tables at the start of execution and drop them if necessary, rather than dropping them at the end... this leaves the temp tables available for further use within the context of my current connection, should I want to refactor my query (or whatever other reason makes sense at the time).


IF OBJECT_ID('tempdb.dbo.#stage') IS NOT NULL
DROP TABLE #stage ;
IF OBJECT_ID('tempdb.dbo.#results') IS NOT NULL
DROP TABLE #results ;
IF OBJECT_ID('tempdb.dbo.#logspace') IS NOT NULL
DROP TABLE #logspace ;
GO

CREATE TABLE #stage
( RecoveryUnitId INT
, FileID INT
, FileSize BIGINT
, StartOffset BIGINT
, FSeqNo BIGINT
, [Status] INT
, Parity INT
, CreateLSN NUMERIC(38)
) ;
GO

CREATE TABLE #results
( DatabaseName SYSNAME
, VLF_count INT
) ;
GO

CREATE TABLE #logspace
( DatabaseName SYSNAME
, [LogSize(MB)] NVARCHAR(127)
, [LogSpaceUsed(%)] NVARCHAR(127)
, [Status] INT

);
GO

EXEC sp_MSforeachdb
N'USE [?];
INSERT INTO #stage
EXEC sp_executesql N''DBCC LogInfo([?])'';

INSERT INTO #results
SELECT DB_NAME(), COUNT(*)
FROM #stage;

TRUNCATE TABLE #stage;'

INSERT INTO #logspace
EXEC sp_executesql N'DBCC SQLPERF(logspace);'
GO

SELECT #logspace.*, #results.VLF_count
FROM #logspace
JOIN #results ON #logspace.DatabaseName = #results.DatabaseName
ORDER BY CAST([LogSize(MB)] AS NUMERIC(38)) DESC
GO



Rich Holt
Rich Holt
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 194
More details on the status of the databases, including database status, recovery model, log reuse status...



IF OBJECT_ID('tempdb.dbo.#stage') IS NOT NULL
DROP TABLE #stage ;
IF OBJECT_ID('tempdb.dbo.#results') IS NOT NULL
DROP TABLE #results ;
IF OBJECT_ID('tempdb.dbo.#logspace') IS NOT NULL
DROP TABLE #logspace ;
GO

CREATE TABLE #stage
( RecoveryUnitId INT
, FileID INT
, FileSize BIGINT
, StartOffset BIGINT
, FSeqNo BIGINT
, [Status] INT
, Parity INT
, CreateLSN NUMERIC(38)
) ;
GO

CREATE TABLE #results
( DatabaseName SYSNAME
, VLF_count INT
, [Status] INT
) ;
GO

CREATE TABLE #logspace
( DatabaseName SYSNAME
, [LogSize(MB)] NVARCHAR(127)
, [LogSpaceUsed(%)] NVARCHAR(127)
, [Status] INT

);
GO

EXEC sp_MSforeachdb
N'USE [?];
INSERT INTO #stage
EXEC sp_executesql N''DBCC LogInfo([?])'';

INSERT INTO #results
SELECT DB_NAME(), COUNT(*), [Status]
FROM #stage
GROUP BY [Status];

TRUNCATE TABLE #stage;'

INSERT INTO #logspace
EXEC sp_executesql N'DBCC SQLPERF(logspace);'
GO

SELECT s.DatabaseName, s.[LogSize(MB)], s.[LogSpaceUsed(%)]
, CASE c.[Status] WHEN 0 THEN N'Available for overwrite/reuse' WHEN 2 THEN N' ** Active Tlog (cant be overwritten)' ELSE CAST(c.[Status] AS NVARCHAR(10)) END AS [StatusDescr]
, c.VLF_count
, d.user_access_desc
, d.state_desc
, d.recovery_model_desc
, d.log_reuse_wait_desc
FROM #logspace as [s]
JOIN #results as [c] ON s.DatabaseName = c.DatabaseName
JOIN master.sys.databases as [d] ON c.DatabaseName = d.name
ORDER BY CAST([LogSize(MB)] AS NUMERIC(38)) DESC, c.[Status] DESC

GO



akhamoshioke
akhamoshioke
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 468
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
Rich Holt
Rich Holt
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 194
SQL 2012 added a column "RecoveryUnitId" to the results of DBCC LogInfo.
Try this, tested on SQL 2005 and 2008, as well as 2012:



-- VLF = Virtual Log Fragments. Refers to segments of the Transaction Log, think "noncontiguous segments within the logical TLog file(s)"

IF OBJECT_ID('tempdb.dbo.#stage') IS NOT NULL
DROP TABLE #stage ;
IF OBJECT_ID('tempdb.dbo.#results') IS NOT NULL
DROP TABLE #results ;
IF OBJECT_ID('tempdb.dbo.#logspace') IS NOT NULL
DROP TABLE #logspace ;
GO

CREATE TABLE #stage
( RecoveryUnitId INT
, FileID INT
, FileSize BIGINT
, StartOffset BIGINT
, FSeqNo BIGINT
, [Status] INT
, Parity INT
, CreateLSN NUMERIC(38)
);
GO
-- SQL 2012 added column [RecoveryUnitId]. Drop it for older versions.
IF (SELECT CAST(REPLACE(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(2)),'.','') AS TINYINT)) < 11
ALTER TABLE #stage DROP COLUMN RecoveryUnitId;
GO

CREATE TABLE #results
( DatabaseName SYSNAME
, [VLF count] INT
, [Status] INT
) ;
GO

CREATE TABLE #logspace
( DatabaseName SYSNAME
, [LogSize(MB)] NVARCHAR(127)
, [LogSpaceUsed(%)] NVARCHAR(127)
, [Status] INT

);
GO

EXEC sp_MSforeachdb
N'USE [?];
INSERT INTO #stage
EXEC sp_executesql N''DBCC LogInfo([?])'';

INSERT INTO #results
SELECT DB_NAME(), COUNT(*), [Status]
FROM #stage
GROUP BY [Status];

TRUNCATE TABLE #stage;'

INSERT INTO #logspace
EXEC sp_executesql N'DBCC SQLPERF(logspace);'
GO

SELECT s.DatabaseName, s.[LogSize(MB)], s.[LogSpaceUsed(%)]
, CASE c.[Status] WHEN 0 THEN N'Available for overwrite/reuse' WHEN 2 THEN N' ** Active Tlog (cant be overwritten)' ELSE CAST(c.[Status] AS NVARCHAR(10)) END AS [StatusDescr]
, c.[VLF count]
, d.user_access_desc
, d.state_desc
, d.recovery_model_desc
, d.log_reuse_wait_desc
FROM #logspace as [s]
JOIN #results as [c] ON s.DatabaseName = c.DatabaseName
JOIN master.sys.databases as [d] ON c.DatabaseName = d.name
ORDER BY CAST(s.[LogSize(MB)] AS NUMERIC(38)) DESC, s.DatabaseName, c.[Status] DESC

GO


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search