Monthly Archives: August 2019

Count Databases Virtual Logfiles

High VLF counts can affect write performance
and they can make full database restores and crash recovery take much longer
Try to keep your VLF counts under 200 in most cases (depending on log file size)

Important change to VLF creation algorithm in SQL Server 2014
http://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/

drop table if exists #VLFInfo 
create table #VLFInfo 
(
	 RecoveryUnitId int
	,FileId int
	,FileSize bigint
	,StartOffset bigint
	,FSeqNo bigint
	,VlStatus bigint
	,Parity bigint
	,CreateLSN numeric(38)
)
DROP TABLE IF EXISTS #VLFCountResults
CREATE TABLE #VLFCountResults
(
	DatabaseName sysname
	, VLFCount int
);	 
EXEC sp_MSforeachdb N'Use [?]; 
INSERT INTO #VLFInfo 
EXEC sp_executesql N''DBCC LOGINFO([?])''; 

INSERT INTO #VLFCountResults 
SELECT DB_NAME(), COUNT(*) 
FROM #VLFInfo; 

TRUNCATE TABLE #VLFInfo;'
	 
SELECT DatabaseName, VLFCount  
FROM #VLFCountResults
ORDER BY VLFCount DESC;
	 
DROP TABLE #VLFInfo;
DROP TABLE #VLFCountResults;

Collect table space from all databases tables

exec sp_msforeachdb '
use [?];
if db_id() in (select database_id from sys.databases where state = 0 and database_id > 4)
INSERT INTO <yourtable>
(
 [DTS_DATE_INSERTED]
,[DTS_DB_NAME]
,[DTS_INSTANCE]
,[DTS_TABLE_NAME]
,[DTS_ROW_COUNT]
,[DTS_TABLE_SPACE_GB]
)
SELECT 
 [DTS_DATE_INSERTED] = getdate(),
 [DTS_DB_NAME] = db_name(db_id()),
 [DTS_INSTANCE] = @@SERVERNAME,
 [DTS_TABLE_NAME] = QUOTENAME(s.name) + ''.'' + QUOTENAME(t.Name),
 [DTS_ROW_COUNT] = part.rows,
 [DTS_TABLE_SPACE_GB] = CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 10)) 
 FROM SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
 INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id
 INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id 
                    AND idx.Index_id = part.Index_id
 INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id
 INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id
 INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id
WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255 
GROUP BY t.Name, s.name, part.rows
ORDER BY [DTS_TABLE_SPACE_GB] DESC'