Shrinking log files on development

Question

I've been looking at getting some disk space back on some of our development machines.
These are machines that are not being backed up, not being developed on directly, and not being maintained particularly well.
Any recovery is done from PRD or PRE-PRD to the DEV environment, nothing on this particular DEV environment is critical.

The recovery models on these databases has to remain on FULL because it was so ordained (despite no actual backups being taken).
As these databases in some cases have been around for quite some time without backups, I'm looking into a proper way to shrink their logs.

I've found two approaches that might serve the purpose:

BACKUP LOG [databasename] TO DISK=’NUL:’

Followed by shrinking the log.

Or:

ALTER DATABASE [databasename] SET RECOVERY SIMPLE;
ALTER DATABASE [databasename] SET RECOVERY FULL;

Followed by shrinking the log.

My actual questions:
What are the differences between these two approaches?
Is there something I'm missing / overlooking?

Answer 1

For this scenario I prefer the method of doing (I added WITH NO_COMPRESSION just in case the server default setting is to use Compression since there is little point in using extra CPU when the resulting file isn't going to take up any space anyway):

BACKUP LOG [databasename] TO DISK = 'NUL:' WITH NO_COMPRESSION;

The reason I prefer this method over switching to SIMPLE and then back to FULL is that it doesn't change the overall backup process. This way you can have more consistency between your environments. It also allows you to have a testing environment for your backup process because it isn't different than what you have in Production. And if sending the file to NUL: presents too much of a difference, then just send it to a folder where the backup file can be deleted.

Flipping to SIMPLE and then back to FULL makes it harder to test DIFFerential backups since you need to have a FULL backup first. So if you do a FULL backup once a week and DIFF the rest of the week, flipping to SIMPLE and then back to FULL would work if done just prior to the weekly FULL backup, but not at any other time during the week. On the other hand, taking the LOG backup and either directing it to NUL: or deleting the backup file can be done at any point during the week.

Also, taking the LOG backup and either directing it to NUL: or deleting the backup file allows you to maintain a pre-grown LOG / .ldf file. And at that point you can use DBCC SHRINKFILE to enforce a maximum initial-size of the empty LOG file and not get stuck with a file that grew A LOT due to reindexing or something else.

PLEASE NOTE that getting rid of your LOG backups means that you will not be able to restore to points in time between your FULL / DIFF backups. Of course, if you don't have FULL / DIFF backups in the first place, then this is a moot point.

Answer 2

when you do BACKUP LOG [databasename] TO DISK=’NUL:’, it is like taking a backup to a drive which never exists, its just like a normal log backup but it actual does not back the log and fakes in a way, by not occupying any space.

This is not all a good practice, but since you said this being DEV and not critical you may approach, only when you are in a situation where there is hardly any space on the drive and can bring the system down.

In case you are not that bad on space, you should follow the second approach of taking the log backup first, and then changing recovery models from FULL to Simple and back to FULL accordingly.

Considering , Point in recovery time is what is not a concern here you can,

ALTER DATABASE DBNAME SET RECOVERY SIMPLE;


USE DBNAME;
GO
CHECKPOINT;
GO
CHECKPOINT;
GO

DBCC SHRINKFILE(DB_log, 512);-- 512 is something you can adjust accordingly
GO

And then adjust the file setting/growth accordingly

USE [master];
GO
ALTER DATABASE DBNAME 
  MODIFY FILE
  (NAME = DB_log, SIZE = 520 MB, FILEGROWTH = 256MB);-- 512 and 256 should be adjusted as analysed from database growth events
GO

In addition to above , please read Aaron's answer Why Does the Transaction Log Keep Growing or Run Out of Space? for better understanding