SQL Server DBCC LOGINFO

SQL Server DBCC LOGINFO – Observing Virtual Log

Hi Friends,

We can have more than one log file for a database. However, in many cases a single log file suffices. Logically, a log file is divided into a number of virtual log files. The virtual log file concept allows SQL Server to manage various operation on the log file, including checkpoints, various phases of revovery like Analysis, Undo & Redo. With the help of Virtual Log Files (VLFs), potions of the Log file is marked as Active, Recoverable, Reusable & Unused. The number of VLFs can go into hundreds (but not advisable). When a log file grows and when it is shrunk, it is always in units of VLFs. We can observe the virtual log file using SQL Server DBCC LOGINFO statement.

USE AdventureWorks2008
GO
DBCC LOGINFO

SQL Server DBCC LOGINFO

By Amit Bansal|2020-09-02T10:23:06+05:30February 3rd, 2014|SQL Server|5 Comments

About the Author: Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: https://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

5 Comments

Amit April 25, 2020 at 6:31 pm - Reply
true..
SQLMaestros Admin April 27, 2020 at 10:58 am - Reply
Thanks & fixed.
SQLMaestros Admin April 27, 2020 at 10:58 am - Reply
Thanks & Fixed
kysphaneendra May 26, 2020 at 12:43 pm - Reply
I have a doubt in the very first statement of the article. Does a database has more than one log file?
- SQLMaestros Admin May 26, 2020 at 1:09 pm - Reply
  Yes can have. Doesn’t help in performance but some DBAs add it for safety purposes. Lets say you have one log file on a LUN and it runs out of disk space, you can add another log file on a different LUN, so on.

SQL Server DBCC LOGINFO – Observing Virtual Log