SQL Server – Switching to Simple Recovery and Shrinking Transaction Logs

recovery-modelshrinksql serversql server 2014transaction-log

Standard disclaimer: I am an "involuntary DBA" (a nice phrase I saw in this article) and have done lots and lots and lots and lots and lots of reading on this subject, but am still confused/concerned…

I need to change the recovery model of several SQL Server 2014 production databases from Full to Simple.

The databases are currently set to Full Recovery and were being backed-up using SqlBackupAndFTP, which would do a daily full backup, with 3 differential backups and 20 transaction backups each day. This was working fine until we had huge problems with the company providing FTP-based storage.

About 3 months ago I switched to using Attix5 which is working well – however, I've now discovered that due to the way the software works the transaction logs are as big as the data files (in one case over 14Gb against a 13Gb data file).

I have been recommended to switch the recovery model to Simple, so that the transaction log doesn't grow.

I know that having Simple Recovery will not provide point-in-time restoration – that is absolutely fine, as full backups are taking place hourly and we are "happy to lose" anything that might have happened in the previous 1-59 minutes.

My understanding is that I should set the recovery model to Simple and let the backup take place, which will set a checkpoint in the transaction log allowing for the space to be re-used.

However, I really need to shrink the transaction log to a sensible size (so that it's not taking up so much of the backup space), but I keep reading that shrinking is a bad idea. Or am I getting the wrong end of the stick?

If I shrink the transaction log down to around 25Mb (which should easily be big enough for a single hourly set of transaction) using the following command, is that enough?

DBCC SHRINKFILE('log file name',25)

(I'm really sorry to be asking something that appears to have been asked a zillion times before, but this is not something I can get wrong.)

Best Answer

First of all I'd like to again point out that, in most cases, SIMPLE recovery is not recommended for a production database. However, if that is something you wish to change the best way I've found to shrink the LOG file, after you put the database into SIMPLE recovery model, is as follows. This will not only accomplish releasing free space from the log, but also shrinking the file down to its smallest possible size.

As you stated, shrinking also is not normally a good practice, but there are times when it is warranted. There are many articles pro/con about it, and I agree with those that will tell you to never use DBCC SHRINKDATABASE; always use DBCC SHRINKFILE if you absolutely have to do a shrink.

However, the log file does indeed have to have a reasonable size to it, even in the SIMPLE recovery model, as that is where information is kept during transactions and long running SPIDs need more of a log file. Among many reasons, one of those is for roll-back in the event of a problem. As I stated above, these scripts will reduce your log file to its absolute smallest size possible, but because you need at least some log space you should follow up by regrowing it manually and also make sure your "auto grow" is set to a reasonable number; arguably, a percentage is to always be avoided. Shrinking the log to its smallest size and the regrowing it manually accomplishes a very important process - that of keeping your VLF fragmentation/counts down. That's another topic I wont go into here, and many sites including Brent Ozar can go into the actual statistics and testing showing why, but always grow your log file by 8 GB per growth if it needs to be that large or larger. You mentioned above 25 MB and I can't imagine that being large enough. I personally manage over a hundred databases at the moment, and less than a dozen are set to 64 MB - All others are much larger ranging up to 64 GB. Your actual SQL activity and queries will determine what your optimal size should be. At minimum I'd go 512 MB to 2 GB and set your growth to the same until you know what your log file should sit at.

I find running each line separately works out the best, ensuring success before going to the next step. The final line of course is what regrows your log to the size you set. I have it at 8 GB, but you can change that to fit your needs. If you do not at this time want to fully shrink and then regrow remove the last line and change the "1" in the line above to your size choice.

One last thing before you go out and shrink your log file.

IMPORTANT: Any time a log file grows, manually or automatically by SQL, essentially your entire database is locked until the grow is done. Pick a good time when doing that won't impact active transactions - if you have "after hours" or maintenance windows. Such a small size of 1 GB or less should be fairly fast and invisible to the end user, but that will truly be determined by your infrastructure.

USE [DatabaseName] 

DBCC SHRINKFILE (N'LogicalName', 0, TRUNCATEONLY)

DBCC SHRINKFILE (N'LogicalName', 1)

ALTER DATABASE [DatabaseName] MODIFY FILE ( NAME = N'LogicalName', SIZE = 8192 MB )
Related Question