Transaction Space Utilization Info

 

use DB_Name

select db_name(database_id) 'db_name', total_log_size_in_bytes/(1024*1024) 'total_log_size_in_mb',used_log_space_in_bytes/(1024*1024) 'used_log_space_in_mb',used_log_space_in_percent,log_space_in_bytes_since_last_backup/(1024*1024) 'log_space_in_mb_since_last_backup' from sys.dm_db_log_space_usage

DBCC SQLPERF(LOGSPACE);

--VLF Count info..

SELECT [name], COUNT(l.database_id) AS 'vlf_count' FROM sys.databases AS s CROSS APPLY sys.dm_db_log_info(s.database_id) AS l GROUP BY  name

 

--VLF Status Info

use DB_Name

;WITH cte_vlf AS (

SELECT ROW_NUMBER() OVER(ORDER BY vlf_begin_offset) AS vlfid, DB_NAME(database_id) AS [Database Name], vlf_sequence_number, vlf_active, vlf_begin_offset, vlf_size_mb

    FROM sys.dm_db_log_info(DEFAULT)),

cte_vlf_cnt AS (SELECT [Database Name], COUNT(vlf_sequence_number) AS vlf_count,

    (SELECT COUNT(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 0) AS vlf_count_inactive,

    (SELECT COUNT(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS vlf_count_active,

    (SELECT MIN(vlfid) FROM cte_vlf WHERE vlf_active = 1) AS ordinal_min_vlf_active,

    (SELECT MIN(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS min_vlf_active,

    (SELECT MAX(vlfid) FROM cte_vlf WHERE vlf_active = 1) AS ordinal_max_vlf_active,

    (SELECT MAX(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS max_vlf_active

    FROM cte_vlf

    GROUP BY [Database Name])

SELECT [Database Name], vlf_count, min_vlf_active, ordinal_min_vlf_active, max_vlf_active, ordinal_max_vlf_active,

((ordinal_min_vlf_active-1)*100.00/vlf_count) AS free_log_pct_before_active_log,

((ordinal_max_vlf_active-(ordinal_min_vlf_active-1))*100.00/vlf_count) AS active_log_pct,

((vlf_count-ordinal_max_vlf_active)*100.00/vlf_count) AS free_log_pct_after_active_log

FROM cte_vlf_cnt;

GO

 

dbcc loginfo

--VLF Info of T-Log

declare @database_id int

set @database_id = DB_ID();

select * from sys.dm_db_log_info ( @database_id ) 

 

DBCC OPENTRAN;

SELECT name, log_reuse_wait, log_reuse_wait_desc, is_cdc_enabled FROM sys.databases WHERE name = 'DB_Name';

--To find Big Tables List in SQL Server

WITH TableSizes AS (SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8.0 / 1024 / 1024 AS TotalSpaceGB,  SUM(a.used_pages) * 8.0 / 1024 / 1024 AS UsedSpaceGB,  (SUM(a.total_pages) - SUM(a.used_pages)) * 8.0 / 1024 / 1024 AS UnusedSpaceGB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

   INNER JOIN  sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

   INNER JOIN  sys.allocation_units a ON p.partition_id = a.container_id

   INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

   WHERE  t.is_ms_shipped = 0 GROUP BY  t.Name, s.Name, p.Rows ) SELECT SchemaName, 

   TableName, RowCounts, TotalSpaceGB, UsedSpaceGB,  UnusedSpaceGB FROM 

   TableSizes ORDER BY  TotalSpaceGB DESC;