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. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/4\/47\/10500878-1.jpg\/v4-460px-10500878-1.jpg","bigUrl":"\/images\/thumb\/4\/47\/10500878-1.jpg\/aid10500878-v4-728px-10500878-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>Image by: Uploader<br>\nLicense: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by\/3.0\/\">Creative Commons<\/a>\n<\/p><\/div>"}
    You can check the transaction log usage locally on the server or when connected remotely.
  2. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/0\/03\/10500878-2.jpg\/v4-460px-10500878-2.jpg","bigUrl":"\/images\/thumb\/0\/03\/10500878-2.jpg\/aid10500878-v4-728px-10500878-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>Image by: Uploader<br>\nLicense: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by\/3.0\/\">Creative Commons<\/a>\n<\/p><\/div>"}
    It’s in the left panel.
    Advertisement
  3. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/3\/33\/10500878-3.jpg\/v4-460px-10500878-3.jpg","bigUrl":"\/images\/thumb\/3\/33\/10500878-3.jpg\/aid10500878-v4-728px-10500878-3.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>Image by: Uploader<br>\nLicense: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by\/3.0\/\">Creative Commons<\/a>\n<\/p><\/div>"}
    It’s in the toolbar at the top of the window.
  4. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/e\/e3\/10500878-4.jpg\/v4-460px-10500878-4.jpg","bigUrl":"\/images\/thumb\/e\/e3\/10500878-4.jpg\/aid10500878-v4-728px-10500878-4.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>Image by: Uploader<br>\nLicense: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by\/3.0\/\">Creative Commons<\/a>\n<\/p><\/div>"}
    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. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/8\/82\/10500878-5.jpg\/v4-460px-10500878-5.jpg","bigUrl":"\/images\/thumb\/8\/82\/10500878-5.jpg\/aid10500878-v4-728px-10500878-5.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>Image by: Uploader<br>\nLicense: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by\/3.0\/\">Creative Commons<\/a>\n<\/p><\/div>"}
    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
      Submit
      Thanks for submitting a tip for review!

      You Might Also Like

      5 Easy Ways to Get Around ChatGPT Security Filters
      Character AI: What It Is, Fixing Filters & Repeats, & More
      How to Download and Install Cinema HD on Fire Stick
      How to Screen Share Crunchyroll on Discord with Friends
      Everything You Need to Know About Soap2Day: Safety & More
      18 Free, Educational & School-Approved Gaming Websites
      The Best Tinder Bios & Tips for Guys
      6 Ways to Undo Accidental Typing and Deleting in Notes
      How to Access Blocked Sites Without a VPN: School, Work, and Home
      Tinder Likes: How Many You Get + More
      3 Simple Ways to Share Your Venmo Link
      Simple Steps to Solve "Spotify Can't Play This Right Now"
      Funny, Flirty, & Creative Bumble Bios for Girls
      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 97,500 times.

      Is this article up to date?

      Advertisement