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
-
Log into the SQL Server Management Studio. You can check the transaction log usage locally on the server or when connected remotely.
-
Select the database in the Object Explorer. It’s in the left panel.Advertisement
-
Click New Query . It’s in the toolbar at the top of the window.
-
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] X Research source
< USE nameofdatabase ; GO SELECT file_id , name , type_desc , physical_name , size , max_size FROM sys . database_files ; GO >
-
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] X Research source
< 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
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
Thanks for submitting a tip for review!
References
- ↑ 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
- ↑ 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
Thanks to all authors for creating a page that has been read 99,435 times.
Advertisement