How to Check Transaction Log Size in a SQL Server

PDF download Download Article
PDF download 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

PDF download Download Article
  1. You can check the transaction log usage locally on the server or when connected remotely.
  2. It’s in the left panel.
    Advertisement
  3. It’s in the toolbar at the top of the window.
  4. 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: [1] 
       < 
 USE 
 nameofdatabase 
 ; 
 GO 
 SELECT 
 file_id 
 , 
 name 
 , 
 type_desc 
 , 
 physical_name 
 , 
 size 
 , 
 max_size 
 FROM 
 sys 
 . 
 database_files 
 ; 
 GO 
 >
  5. To check how much log space is currently in use, type this query and then click Execute in the toolbar: [2] 
       < 
 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 
 ; 
 >
    6. Advertisement

Expert Q&A

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!

      You Might Also Like

      5 Easy Ways to Get Around ChatGPT Security Filters
      How to
      Avoid Being Banned on Soulseek: Tips & Fixes
      Everything You Need to Know About Soap2Day: Safety & More
      3 Simple Ways to Share Your Venmo Link
      How to
      Access Blocked Sites Without a VPN: School, Work, and Home
      Character AI: What It Is, Fixing Filters & Repeats, & More
      5 Ways to Fix Logitech G HUB When It Won’t Open
      Starting Again on Tinder
      Steam Level Guide: How to Earn XP by Crafting Badges & More
      How to
      Use AutoHotkey
      Simple Ways to Make a GIF Your Wallpaper
      How to
      Log Into Ultipro
      2 Easy Ways to Change a PSN Account Age on Desktop or Mobile
      How to
      Fix ChatGPT Login Problems & Errors: 12 Solutions
      Advertisement

      References

      1. ↑ https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-log-space-usage-transact-sql?view=sql-server-2017
      2. ↑ https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-log-space-usage-transact-sql?view=sql-server-2017

      About This Article

      In other languages
      Thanks to all authors for creating a page that has been read 103,955 times.

      Is this article up to date?

      Advertisement