I am running some SHRINKFILE operations to clean up a bunch of tiny, unnecessary files in a filegroup. For one of the shrinks, the command below results in an error:


File ID x of database ID x cannot be shrunk as it is either being shrunk by another process or is empty

It is not empty nor being shrunk. It is being run on a database not currently in use by anyone except myself. Auto shrink is not enabled and never was. However, there were manual shrinks performed on this database on a regular basis prior to me getting my hands on it, if that matters at all.

On SQLServerCentral, a thread from a decade ago suggests adding a few MB to the file because that "resets an internal counter or switch that tells it it's not in the middle of a shrink now."

This worked - awesome. But can anyone explain with greater detail how/why this works in regard to SQL Server internals?

  • 1
    Couldn't tell you the answer, but upvoting as that's a handy trick to know if I ever run into this situation in the future! – John Eisbrener Jan 17 '19 at 18:04
  • if you can repro maybe some flag on the file header page that gets set during a shrink? – Martin Smith Jan 18 '19 at 0:06
  • Yeah I might give that a shot on a test instance, but this was prod so definitely don't have the luxury of trying to reproduce it there. – LowlyDBA Jan 18 '19 at 18:59

I poked around in the file header page, as suggested by Martin Smith in the comments. I think this is part of the answer, but it's mostly speculation based on observing changes to the file header page flag values between performing shrinks and other operations.

First I created a database to test with, including a secondary filegroup:

CREATE DATABASE [Shrinkfile_Test]
    NAME = N'Shrinkfile_Test',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\Shrinkfile_Test.mdf',
    SIZE = 8192KB,
    FILEGROWTH = 65536KB
    NAME = N'ShrinkFile_Test_Secondary',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\ShrinkFile_Test_Secondary.ndf',
    SIZE = 1024KB,
    FILEGROWTH = 65536KB
    NAME = N'Shrinkfile_Test_log',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\Shrinkfile_Test_log.ldf',
    SIZE = 73728KB,
    FILEGROWTH = 65536KB 

USE Shrinkfile_Test;

The I looked at "page 0" for the secondary file, which is file_id 3:

DBCC PAGE (N'Shrinkfile_Test', 3, 0, 3);

There's a field called m_flagBits that has a value of 0x208.

If I empty this file:

DBCC SHRINKFILE (N'ShrinkFile_Test_Secondary' , EMPTYFILE);

That m_flagbits field stays the same (0x208). Not that interesting, but now I'm in the situation you reported: if I try to empty the file again, I get this error:

File ID 3 of database ID 19 cannot be shrunk as it is either being shrunk by another process or is empty.

I'll try growing the file (the solution that worked for you):

    NAME = ShrinkFile_Test_Secondary,
    SIZE = 1025KB

Now m_flagbits is 0x8!

At this point, emptying the file again is successful returns the value to 0x208 as you might expect.

The thing that I find interesting is that if I do this after growing the file back out (AKA flagbits value is 0x8):

USE [master]

The file is marked as is_read_only in the sys.databases table, and m_flagbits is set back to 0x208. So it appears there is some similar file-level flag set when shrinking a file and when setting it to read only.

My best guess is that this value is used along with some other (internal) flag to indicate that a file is eligible to be shrunk. Growing the file appears to un-set that flag (at least the one visible in m_flagbits).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.