How to remove additional (unwanted) log files

4

November 5, 2014 by Kenneth Fisher

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
DropExtraLogFile

DropExtraLogFile2

Code

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

Now you can remove the file.

GUI
DropExtraLogFile3

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.)

4 thoughts on “How to remove additional (unwanted) log files

  1. But how to remove primary log?

    • I’m not sure what you mean? You have to have at least one log file but if you have multiple files no one of them is “primary”.

      • This comment is from Taiob Ali (@SqlWorldWide) who was having a hard time getting it posted for some reason.

        Hi Kenneth,
        Thank you for your post. I think what Anatolii is referring to is logfile with fileid=2. When you have multiple log file fileid 2 is still considered as primary log file.
        I can send you test code if you are interested.

        USE [master]
        GO
        if db_id('MultipleLogFile') is not null
        BEGIN
        ALTER DATABASE [MultipleLogFile] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
        DROP DATABASE [MultipleLogFile]
        END
        CREATE DATABASE [MultipleLogFile]
         CONTAINMENT = NONE
         ON  PRIMARY 
        ( NAME = N'MultipleLogFile', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MultipleLogFile.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
         LOG ON 
        ( NAME = N'MultipleLogFile_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MultipleLogFile_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
        GO
        USE [MultipleLogFile]
        GO
        IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') 
        ALTER DATABASE [MultipleLogFile] MODIFY FILEGROUP [PRIMARY] DEFAULT
        GO
        USE [master]
        GO
        ALTER DATABASE [MultipleLogFile] ADD LOG FILE ( NAME = N'MultipleLogFile_log2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MultipleLogFile_log2.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
        GO
        ALTER DATABASE [MultipleLogFile] ADD LOG FILE ( NAME = N'MultipleLogFile_log3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MultipleLogFile_log3.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
        GO
        --check how many files you have now
        USE [MultipleLogFile]
        GO
        select * from sysfiles
        --It will succeed
        USE [MultipleLogFile]
        GO
        DBCC SHRINKFILE (N'MultipleLogFile_log2' , EMPTYFILE)
        GO
        ALTER DATABASE [MultipleLogFile]  REMOVE FILE [MultipleLogFile_log2]
        GO
        --it will fail
        USE [MultipleLogFile]
        GO
        DBCC SHRINKFILE (N'MultipleLogFile_log' , EMPTYFILE)
        GO
        ALTER DATABASE [MultipleLogFile]  REMOVE FILE [MultipleLogFile_log]
        GO
        --It will succeed
        USE [MultipleLogFile]
        GO
        DBCC SHRINKFILE (N'MultipleLogFile_log3' , EMPTYFILE)
        GO
        ALTER DATABASE [MultipleLogFile]  REMOVE FILE [MultipleLogFile_log3]
        GO
        --check how many files you have now
        select * from sysfiles
        
        --clean up
        if db_id('MultipleLogFile') is not null
        BEGIN
        ALTER DATABASE [MultipleLogFile] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
        DROP DATABASE [MultipleLogFile]
        END

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,671 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: