Add an extra SQL Transaction log file – ALTER DATABASE ADD LOG

23 February,2013 by Jack Vamvas

Question: I need to create an extra sql transaction log file to a SQL Server database. What is the correct syntax?

I’ve run out of disk space on the dedicated log file drive and need to run a very large query one-off query.The storage administrators have added a temporary extra disk , for the extra sql transaction log file.  

Answer:  Use the ALTER DATABASE command to add an extra SQL transaction log file  with a destination location . The example below adds an extra log file to an existing database

USE [master]
ALTER DATABASE [database_name] ADD LOG FILE ( NAME = N'database_nameExtraLog', FILENAME = N'N:\ database_nameExtraLog.ldf' , SIZE = 20480KB , FILEGROWTH = 1024KB )


Read More

Modify sql transaction log file size - increase or decrease

SQL Server - Find Default Data location and Default Log Location

SQL Server - Preallocate SQL Transaction Logs for large queries – Initial Size

SQL Server – Monitor SQL Transaction Log AutoGrowth for performance issues

Author: Jack Vamvas (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Add an extra SQL Transaction log file – ALTER DATABASE ADD LOG | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer