DMV for Log Statistics in SQL Server

SELECT name as 'Database Name',  <values from below> as '<alias for view column>'
FROM sys.databases AS s
CROSS APPLY sys.dm_db_log_stats(s.database_id)
where name='<Database Name>';

Important View Column Names

What LSNs are in my Transaction Logs?

SELECT name as 'Database Name', log_min_lsn as 'Minimum LSN', log_end_lsn as 'Maximum LSN'
FROM sys.databases AS s
CROSS APPLY sys.dm_db_log_stats(s.database_id)
where name='BaseballData';

Last Backup of the Transaction Log?

SELECT name AS 'Database Name', log_backup_time AS 'last log backup time' FROM sys.databases AS s CROSS APPLY sys.dm_db_log_stats(s.database_id);
SELECT s.name as 'Database Name', log_since_last_log_backup_mb as 'Log Size Since Last Backup'
FROM sys.databases AS s
CROSS APPLY sys.dm_db_log_stats(s.database_id)
where name='BaseballData';
SELECT t.name as 'Database Name', s.recovery_model as 'Backup Model', s.log_backup_time AS 'last log backup time'
FROM sys.dm_db_log_stats(5) AS S,sys.databases AS t
where t.database_id=s.database_id;

Trouble Avoidance

SELECT t.name as 'Database Name', s.total_vlf_count as 'Total VLFs', s.active_vlf_count as 'Active VLFs', s.log_truncation_holdup_reason as 'Reason for Log Holdup'
FROM sys.dm_db_log_stats(5) AS S,sys.databases AS t
where t.database_id=s.database_id;

--

--

--

Data Goth Girl, abnormally denormalized, combat boot wearing techie-blogs at DBAKevlar, Oak Table Member, TSP at Microsoft in Power BI and AI

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

[LeetCode]#1684. Count the Number of Consistent Strings

Deploy Cloud Management Gateway (CMG) on MECM 2002 — Single Wildcard Cert Method

Wordpress-MySQL Automation with NAT Gateway

The Ultimate Guide to What is an Algorithm and How to Write Easily? | Cybjurnal

Creating an App without creating the App: Google Firebase

Legacy Systems Modernization for Travel Enterprises

Lehman’s laws of software evolution: Increasing Complexity

Master Projects The Zen Way

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Kellyn PotVin-Gorman

Kellyn PotVin-Gorman

Data Goth Girl, abnormally denormalized, combat boot wearing techie-blogs at DBAKevlar, Oak Table Member, TSP at Microsoft in Power BI and AI

More from Medium

How to use a Google Sheet template to easily track and manage your expenses!

How to use a google sheet template to easily track and manage your expenses!

A360 RPA Challenge — Employee Data Migration-Human Resource

How Data as a Service Adoption is Helping Companies Win Markets

Adoption of Data as a Service is helping companies win markets

InfoWorks ICM GeoPlan Tips