How to Check Transaction Log Size in a SQL Server

Download Article

Explore this Article

Steps

Steps

Other Sections

Related Articles

References

Written by Nicole Levine, MFA

Last Updated: April 10, 2024

Download Article

This wikiHow teaches you how to find out the size of a database’s transaction log, as well as how much of the total log space it’s using, on a Microsoft SQL Server.

Manage and Display Log File Size in SQL

To check the transaction log size in a SQL server, log into the SQL Server Management Studio and select the database. Open a new query, enter the transaction log code, and then click Execute.

Steps

Download Article

1. 
   1
   Log into the SQL Server Management Studio. You can check the transaction log usage locally on the server or when connected remotely.
2. 
   2
   Select the database in the Object Explorer. It’s in the left panel.
   Advertisement
3. 
   3
   Click New Query. It’s in the toolbar at the top of the window.
4. 
   4
   Find the size of the transaction log. To view the actual size of the log, as well as the maximum size it can take up in the database, type this query and then click Execute in the toolbar:

   <USE nameofdatabase;  
   GO
   SELECT file_id, name, type_desc, physical_name, size, max_size  
   FROM sys.database_files ;  
   GO>
   
5. 
   5
   Find the amount of log space in use. To check how much log space is currently in use, type this query and then click Execute in the toolbar:

   <USE nameofdatabase;  
   GO
   
   SELECT (total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024 AS [free log space in MB]  
   FROM sys.dm_db_log_space_usage;>
   
Advertisement

Expert Q&A

Search

Add New Question

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Advertisement

Tips

Submit a Tip

All tip submissions are carefully reviewed before being published

Name

Please provide your name and last initial

Submit

Thanks for submitting a tip for review!