How to Check Transaction Log Size in a SQL Server

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.

Steps

  1. Image titled 10500878 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. Image titled 10500878 2
    2
    Select the database in the Object Explorer. It’s in the left panel.
    Advertisement
  3. Image titled 10500878 3
    3
    Click New Query. It’s in the toolbar at the top of the window.
  4. Image titled 10500878 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: [1] 
      <USE nameofdatabase;  
GO
SELECT file_id, name, type_desc, physical_name, size, max_size  
FROM sys.database_files ;  
GO>
  5. Image titled 10500878 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: [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

You Might Also Like

Bypass Chat Gpt Filter3 Easy Ways to Get Around ChatGPT Security Filters
Best Tinder Bios for GuysThe Best Tinder Bios & Tips for Guys
Create a ChatGPT Account
How to
Create a ChatGPT Account
Undo in Notes6 Ways to Undo Accidental Typing and Deleting in Notes
Chatgpt Character LimitDoes ChatGPT Have a Word Limit? What It Is & How to Fix It
Block Someone on Hinge Before Matching
How to
Block Someone on Hinge Before Matching
Cancel Stockx OrderHow to Cancel a StockX Purchase
Get Chatgpt to Write CodeHow to Get ChatGPT to Write Effective Code & Build Websites
Close a Microsoft AccountPermanently Delete a Microsoft Account: Xbox, Outlook & More
Use Bitly
How to
Use Bitly
Delete Walmart Account9 Steps to Permanently Delete a Walmart.com Account
Delete a Depop ListingHow to Delete a Depop Listing or Mark As Sold
Access Blocked Sites Without a Vpn
How to
Access Blocked Sites Without a Vpn
Log Out of Hotmail
How to
Log Out of Hotmail
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

Nicole Levine, MFA
Written by:
Nicole Levine, MFA
wikiHow Technology Writer
This article was co-authored by wikiHow staff writer, Nicole Levine, MFA. Nicole Levine is a Technology Writer and Editor for wikiHow. She has more than 20 years of experience creating technical documentation and leading support teams at major web hosting and software companies. Nicole also holds an MFA in Creative Writing from Portland State University and teaches composition, fiction-writing, and zine-making at various institutions. This article has been viewed 84,813 times.
How helpful is this?
Co-authors: 3
Updated: May 16, 2020
Views: 84,813
Categories: Website Application Instructions
Advertisement