Shrinking and consolidating to only one log file

Question

I've restored a database from production to QA, on SQL Server 2012. One of the databases has two log files:

• logfile.ldf - 6 GB
• logfile2.ldf - 200 GB

Due to space issues and data not being truncated through the years, there is data dating back from 10 years ago. However, I would like to only retain data from 2015 onwards.

I did the following:

1. Set Recovery Mode to Simple;

2. Issued DBCC SHRINKFILE multiple times in this order:

   200 GB > 100 GB > 50 GB > 25 GB > 12 GB

   DBCC SHRINKFILE(EMS_1_4_3_log, 12288); -- unit is set in MBs
   

3. Used a table for min and max dates and the dates remain the same.

Questions

1. Although the shrink was successful, I believe I can shrink it more but I'll stop here. How would I know that it shrunk efficiently?

   I didn't use TRUNCATEONLY, should I have used this?

   TRUNCATEONLY

   Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. target_size is ignored if specified with TRUNCATEONLY. The TRUNCATEONLY option does not move information in the log, but does remove inactive VLFs from the end of the log file. This option is not supported for FILESTREAM filegroup containers.

   What does it mean that "it does not move information in the log"?

2. To get the data dates back to retention period, do I truncate data in the tables themselves? What's a better way of doing this? I would lose the LSN, which we don’t care right now, but I still would like to recover from the retention dates.

3. How do I consolidate multiple logfiles?

Answer 1

I'd consolidate the log files first, before worrying about the right size to set the log.

To do that, you would need to use the EMPTYFILE clause in a DBCC SHRINKFILE operation, so that the second file can be emptied out and then deleted.

Once you are back to a single log file, you can then shrink right back down to zero (to remove any fragmentation), and then resize it appropriately, to ensure good distribution of Virtual Log Files (VLFs) inside the transaction log file.

There's no way to know what the "right" size is for your log file until you run a normal workload against the database, and see how big it gets. Once you know that, you shrink it down again, and then resize back to that value.

Make sure you set a fixed growth setting appropriate for the performance of your underlying I/O subsystem, and not a percentage file growth. You might want to set it to 100 MB, or 1 GB. It's entirely up to you.

Regarding TRUNCATEONLY, all it does is free any inactive portion, at the end of the transaction log file, back to the operating system. No data inside the log is moved. If there's no inactive portion of the log at the end of the file, no space will be released.

The Simple Recovery Model does not allow point-in-time recovery, but I think you know that. If you want to do point in time recovery, you'll need the Full Recovery Model, and perform regular transaction log backups.