Shrinking log files on development

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?

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.

StackExchange Database Administrators Q#119184, answer score: 3