April 22, 2014 at 3:29 pm
Comments posted to this topic are about the item vlfs_Count_for_Each_Database
May 6, 2014 at 4:39 am
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. 🙂
May 6, 2014 at 8:33 am
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 toN'USE [?]
LogInfo(?)change to LogInfo([?])
May 6, 2014 at 9:10 am
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 🙂
May 6, 2014 at 9:54 am
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
May 6, 2014 at 11:23 pm
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
May 29, 2014 at 10:52 am
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
May 29, 2014 at 11:24 am
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
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
June 2, 2014 at 11:45 am
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
June 2, 2014 at 11:50 am
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
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply