Skip to main content
Database Administrators

Return to Question

Tweeted twitter.com/StackDBAs/status/1086005398644117504
added 42 characters in body
Source Link
LowlyDBA - John M
LowlyDBA - John M
  • 11k
  • 11
  • 45
  • 62

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:

DBCC SHRINKFILE (N'myfile' , EMPTYFILE)'

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?

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:

DBCC SHRINKFILE (N'myfile' , EMPTYFILE)'

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.

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?

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:

DBCC SHRINKFILE (N'myfile' , EMPTYFILE)'

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?

Source Link
LowlyDBA - John M
LowlyDBA - John M
  • 11k
  • 11
  • 45
  • 62

SHRINKFILE Failure - Why does increasing file size resolve it?

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:

DBCC SHRINKFILE (N'myfile' , EMPTYFILE)'

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.

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?