Is DBCC SHRINKFILE (filename, EMPTYFILE) fully logged?

Update: Paul Randal told me I had made a mistake in my post. The correction is below.

---

On Wednesday last week, Kenneth Fisher (b | t) asked: “Is DBCC SHRINKFILE (filename, EMPTYFILE) fully logged?”

Is DBCC SHRINKFILE (filename, EMPTYFILE) fully logged? #sqlhelp

— Kenneth Fisqher (the q is silent) (@sqlstudent144) April 6, 2016

Let’s buckle down and see for ourselves, shall we?

Firstly, what does DBCC SHRINKFILE do?

As we know, SQL Server allows us to reclaim space in data and log files by issuing a DBCC SHRINKFILE command. Books Online goes into some detail about each of the parameters we can use but does not seem to answer Kenneth’s question.

What does the EMPTYFILE parameter do, anyway?

Per Books Online:

EMPTYFILE

Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.

Let’s say we want to move data from one file in a filegroup to an empty file. We might do this if we want to free up a significant amount of space after a major data cleanup, but the default SHRINKFILE options would take a long time.

Fair enough, but Kenneth wants to know if it is fully logged.

How do we test this?

Let’s create a database called ShrinkFileTest, and then add a new filegroup called FGData, which will in turn contain a single data file called FGFile1.

[All of the following code was executed on a SQL Server 2014 instance.]

CREATE DATABASE [ShrinkFileTest] ON
PRIMARY (NAME = N'ShrinkFileTest',
FILENAME = N'D:\SQL2014\DI\ShrinkFileTest.mdf',
SIZE = 5120 KB, FILEGROWTH = 1024 KB),
FILEGROUP [FGData] (NAME = N'FGFile1',
FILENAME = N'D:\SQL2014\DI\FGFile1.ndf',
SIZE = 2048 MB, FILEGROWTH = 1024 MB)
LOG ON (NAME = N'ShrinkFileTest_log',
FILENAME = N'D:\SQL2014\DI\ShrinkFileTest_log.ldf',
SIZE = 1024 MB, FILEGROWTH = 1024 MB);
GO

ALTER DATABASE [ShrinkFileTest]
SET RECOVERY FULL;
GO

Later, we will add a new file to FGData and use the shrink command to move the data to the new file.

So now let’s populate the FGData filegroup with some test data. Create a table called [dbo].[Test] on the filegroup, and insert ten million rows.

use [ShrinkFileTest];
GO
CREATE TABLE [dbo].[Test] (
[Col1] INT IDENTITY(1,1) PRIMARY KEY,
[Col2] NCHAR(5) DEFAULT N'aaaaa'
) ON [FGData];
GO

-- Load in ten million rows
INSERT INTO [dbo].[Test]
SELECT TOP (10000000) 'aaaaa'
FROM sys.all_objects AS o1
CROSS JOIN sys.all_objects AS o2
CROSS JOIN sys.all_objects AS o3;
GO

We will now create a scenario where most of the rows are deleted but the data file contains a lot of free space.

DELETE FROM [dbo].[Test] WHERE [Col1] % 1000 <> 0;

We should be left with ten thousand rows in the table, at 1,000 intervals (this scenario is designed to create massive free space gaps in the data and ensure each row is on its own data page).

We want to move all the existing data into an empty file, so we can delete the file FGFile1.

Add the second file to the FGData filegroup:

USE [master]
GO
ALTER DATABASE [ShrinkFileTest] ADD FILE (
NAME = N'FGFile2',
FILENAME = N'D:\SQL2014\DI\FGFile2.ndf',
SIZE = 512 MB, FILEGROWTH = 1024 MB
) TO FILEGROUP [FGData];
GO

Before we run the DBCC SHRINKFILE command though, we should flush the transaction log of the tens of thousands of DELETEs (which are fully logged), so that it’s easier to read:

use [ShrinkFileTest];
GO
CHECKPOINT;
GO

Run the shrink command with the EMPTYFILE parameter:

DBCC SHRINKFILE (FGFile1, EMPTYFILE);

Here’s our output from the shrink:

Let’s see what is in the log:

SELECT [Current LSN], [Operation], [Context], [Page ID], [Slot ID]
FROM fn_dblog (NULL, NULL);
GO

Below the CHECKPOINT output, you will see two rows in the log file, both of which are modifying the Page Free Space (PFS) page.

Summary

Kenneth, it is fully logged. In Paul’s words:

Shrink is always fully logged – it’s impossible not to log physical changes to data files.

I hope this satisfies your curiosity.