Identify Used Space in Log File- Interview Question of the Week #090

Question: How to identify Used Space in Log File?

Answer: Use following command to display used log file size and total used space in the logfile.

DBCC SQLPERF('LOGSPACE')

After running above DBCC command it will give the following results.

You can see four different columns in the resultset.

The first column displays the name of the database. The second column contains the total size of the logfile in MB. The third column is actually our answer. It contains a total Log Space Used in the percentage. We can simple formula of (LogSize * Log Space Used) / 100 to find how much total space in used in MB. The fourth and final column is always zero and it has no meaning when we are running DBCC SQLPERF with parameter LOGSPACE.

If you are taking regular backup of the logfile, your file will not grow beyond certain value and it will also have free space. There should not be any need of shrinking the log file.

Here are few of the related blog posts:

• SQL Basics: Database Datafiles and Log Files
• Solution – Log File Very Large – Log Full
• Recover Lost Data Using the Transaction Log Files
• Restore or Attach Database Without Log File – SQL in Sixty Seconds #082

Reference: Pinal Dave (https://blog.sqlauthority.com)