Using multiple data files is a common best practice for a number of reasons, but multiple log files? Typically a database has only one log file, and in fact I know of only one good reason to add an additional one. Every now and again though someone makes a mistake or you get a legacy system and Wamo! You have a database with multiple log files.

So what’s wrong with having multiple log files? Honestly it doesn’t hurt anything. Log files are used sequentially so only one file will be used at any given point in time. Once SQL reaches the end of a log file it moves on to the next. So if having multiple files doesn’t cause any harm what benefit could it have?

You can create a second (or more) log file in order to extend the log onto a new disk. For example the log file for a database is on the P drive. You notice that the log file is now up to 58GB but the P drive is only 60GB. Over time log files grow just like data files so you need to be prepared for the next growth of that file (manual or automatic). This is of course assuming that you didn’t discover the problem because a batch process crashed with an out of space error on the log file. The quick solution? Add a second (or more) file onto another drive that has some extra space. Generally this will be a temporary solution. A more permanent solution is to add additional space to the P drive or move the log to another larger drive, but that can take some time.

So for whatever reason you have decided to remove that additional log file. The question now is how?

-- Set up a test database
CREATE DATABASE [LogTests]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'LogTests', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CS\MSSQL\DATA\LogTests.mdf' , 
SIZE = 202752KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'LogTests_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CS\MSSQL\DATA\LogTests_log.ldf' , 
SIZE = 51200KB , MAXSIZE = 61440KB , FILEGROWTH = 1024KB ), 
( NAME = N'LogTests_log2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CS\MSSQL\DATA\LogTests_log2.ldf' , 
SIZE = 20480KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

First thing to do is truncate your log (take a log backup assuming FULL recovery model, nothing if SIMPLE recovery). Since this is a brand new database I don’t really have to do anything here. Next do a special shrink that removes all of the data from one of the log files and puts in the other(s).

GUI

Code

USE [LogTests]
GO
DBCC SHRINKFILE (N'LogTests_log2' , EMPTYFILE)
GO

Now you can remove the file.

GUI

Code

USE [LogTests]
GO
ALTER DATABASE [LogTests]  REMOVE FILE [LogTests_log2]
GO

We are now good to go right? Nope, and I tell you this from recent experience, we forgot something important. Can you guess what?

We got rid of one of the log files. (Well DUH!)

Ahh but we got rid of 20mb of log space of the 70mb total we had. So next time the system needs to go over the 50mb remaining the file is going to grow. No big deal right? Nope, not until it hits the 60mb limit of the first file. Then you are going to get an unexpected out of space error.

So here is a checklist to follow when you remove a log file. (And again I say this from recent personal experience)

• Total space of the log file: Before _______ After _______
• Total possible space (growth limits): Before _______ After _______
• Autogrowth: Before _______ After _______

Honestly this is probably a good checklist to go through any time you remove any database file, data or log. It’s always nice to think of these things ahead of time rather than having a client who is already prone to panic attacks all of a sudden see a “Log file is out of space” error Monday morning. (They actually handled it pretty well, for them.)

Filed under: Log File, Microsoft SQL Server, SQLServerPedia Syndication Tagged: Log File, microsoft sql server