When you create a database in SQL Server using the default options, one data file and one log file are created. The data file stores the data and database objects (such as tables, views, stored procedures, etc). The log file stores the information that is required to recover all transactions in the database. If you have a growing database, you may find yourself in the situation where you need to add a new log file (and/or data file).

Just as you can add more data files to an existing database in SQL Server, you can also add more log files. However, the syntax is slightly different depending on whether you’re creating a data file or a log file. Adding a data file requires ADD FILE while adding a log file requires ADD LOG FILE.

This article demonstrates how to use T-SQL to add a log file to an existing database in SQL Server.

Example

To add a log file to an existing database, use the ALTER DATABASE statement with the ADD LOG FILE argument. Supply the details of the new log file and run the statement.

Here’s an example:

USE master;  
GO

ALTER DATABASE Solutions   
ADD LOG FILE 
(  
    NAME = Solutions_log_2,  
    FILENAME = '/var/opt/mssql/data/Solutions_log2.ldf',  
    SIZE = 10MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5%  
);
GO

This adds a log file with a logical name of Solutions_log_2 to the Solutions database. We specify that the physical path is /var/opt/mssql/data/Solutions_log2.ldf. Note that this path uses Linux/Mac syntax. If you use Windows you’ll need to use the applicable syntax (backslashes instead of the forward slashes).

Check the Result

You can check the result by running the following query:

USE Solutions;  
GO

SELECT name, physical_name  
FROM sys.database_files;  
GO

Result:

name             physical_name                         
---------------  --------------------------------------
Solutions        /var/opt/mssql/data/Solutions.mdf     
Solutions_log    /var/opt/mssql/data/Solutions_log.ldf 
Solutions_dat_2  /var/opt/mssql/data/Solutions_dat2.ndf
Solutions_dat_3  /var/opt/mssql/data/Solutions_dat3.ndf
Solutions_log_2  /var/opt/mssql/data/Solutions_log2.ldf

We can see the new log file listed in addition to the original log file (as well as various data files).

In this example I only select two columns of the sys.database_files view to display. You can also use the asterisk (*) to return all columns if you prefer.