How to Check Database Log File Size in SQL Server Using Query?

by

In this SQL Server tutorial, you will learn how to check database log file size in SQL Server using query.

First, you will understand ‘What is a database log file in an SQL Server instance?’. Then you will understand about two view files sys.database_file and sys.master_files to retrieve the log file and its size.

Check Database Log File Size in SQL Server Using Query

In SQL Server, two files are associated with the database, the data and log file. Here you will learn how to check database log file size in SQL Server using query.

But first, let’s know about ‘What is a database log file?’, the log file associated with the database is a file that contains information about the modification on the database.

So whatever the changes made to the database, all these changes are recorded or tracked into the database log file.

  • When you perform INSERT, UPDATE, and DELETE operations on the data files, all the changes made by these operations are kept in the database log file. The log file maintains the consistency of the database by ensuring recoverability and data integrity.
  • The database log file in SQL Server is also known as transaction log. The transaction log file follows a protocol called Write-Ahead Logging short for WAL.
  • Write-ahead logging means whatever changes you want to make to the data files, first record all the changes to the log file and then apply the changes to the data files.
  • This transaction log file keeps track of both before and after values of a change. So you can undo and redo the changes while recovering the database.

Now let’s move and learn different ways to check database log file size in SQL Server using query.

Check Database Log File Size in SQL Server Using Query: sys.database_files

The sys.database_files is a view in SQL Server that returns the information related to the database. The sys.database_files contains information about the database data and log files.

The syntax is given below.

Use database_name;

SELECT name as 'log file name', 
	size * 8 / 1024 as 'size (MB)',
	type_desc 
	FROM sys.database_files
WHERE type_desc = 'LOG';

sys.database_files returns information about the currently connected database. Now run the below query in the query editor of SSMS.

Use model;

SELECT name as 'log file name', 
	size * 8 / 1024 as 'size (MB)',
	type_desc 
	FROM sys.database_files
WHERE type_desc = 'LOG';

If you are using the SQLCMD utility or command line, then execute the below query.

Use model;
SELECT name as 'log file name', 
size * 8 / 1024 as 'size (MB)',
 type_desc 
FROM sys.database_files
WHERE type_desc = 'LOG';
Check Database Log File Size in SQL Server Using Query sys.database_files View

When you execute the above query it returns the modellog file which is associated with the model database and the size of the modellog file is 8 MB. This is how to check database log file size in SQL Server.

Check Database Log File Size in SQL Server Using Query: sys.master_files

The sys.master_files is a catalog view in SQL Server that contains information related to the database, and it can return information about the size of the log file.

To check the size of all the log files on the SQL Server instance use the below query.

SELECT
    DB_NAME(database_id) AS 'database name',
    name AS 'logical file name',
    size * 8 / 1024 AS 'size in MB'
FROM sys.master_files
WHERE type_desc = 'LOG';
Check Database Log File Size in SQL Server Using Query sys.master_files

The above query returns the size of all the log files of the database on SQL Server instance such as mastlog, templog and modellog of size 1, 8, and 8 MB respectively.

This is how you can check the size of the log file in SQL Server using the sys.master_files view.

Conclusion

In this SQL Server tutorial, you learned how to check the size of a database log file in an SQL server using the two methods. In the first method you have seen how to check the size of the log file using the sys.database_files view and in the second learned about checking the size of all the log files using sys.master.files view.

You may like the following tutorials: