File cannot be shrunk as it is either being shrunk by another process or is empty

On Monday on one of the Production Instances we had a High Severity Incident. The problem was that a database which never exceeded 40 GB in size had started growing at a rate of ~10 GB per hour! This database was sort of an audit database which logged events from the application. It was logging events abnormally eating up all space on the database. The database ran out of space and the DBA team added one more data file on another drive to make some room for the database.

After sometime it turned out that the Application Team had changed the logging level in the Application which increased the inserts in the only table in that database. They decided to change the logging to the original setting. This stopped the database from growing abnormally. The table size was only 60 MB now but the database had grown to 300 GB+ in size. At the end of the incident it was decided to reclaim the unused space in the database and the DBA was asked to Shrink the data files.

Shrink me if you can

Since the Incident was resolved, it was a relatively easy task for the DBA to shrink the data files. We started by shrining the data file which was added recently. Since there is hardly any data in the database, the second data file which was ~200GB in size got shrunk in no time. But while trying to shrink the first data file, SQL Server reported the following error message.

File ID 1 of database ID 7 cannot be shrunk as it is either being shrunk by another process or is empty.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Going by the Error Message details the DBA checked if someone else was shrinking th data file. It was not the case. As per this KB article, if one tries to shrink the database when the database backup is underway, it would throw the same error message. But no database backups were running at that time. Moreover the issue addressed by this hotfix was applicable to SQL Server 2005 SP2. The instance in question was running SQL Server 2005 Sp3+. Even the truncateonly parameter in the SHRINKFILE command resulted in the same error.

DBCC SHRINKFILE ('logicalname' , 0, TRUNCATEONLY)
GO

At this time I had two options in mind.

  1. To create a table in the database and insert sample data into it. Then try shrinking.
  2. Increase the file size of the data file in question by 1 MB and try shrinking.

Since the database was *owned* by the Application Team and it was one of the most critical systems for the customer, I did not want to go by the first option. The second option seemed to be more feasible. Hence I increased the data file size by 1 MB.

ALTER DATABASE [dbname] MODIFY FILE ( NAME = N'datafile', SIZE = 141313MB )
GO

After modifying the data file size, I issued the query to shrink the data file to 15 GB. Surprise! Within no time, the data file got shrunk to 15 GB!

PS: Data file should never be shrunk unless it is a one time activity to reclaim space as in my case. Shrinking data file causes indexes to be fragmented and will undo all the good work done by Rebuilding the Indexes. This article by Paul Randal (b | t) explains it with an example.

19 thoughts on “File cannot be shrunk as it is either being shrunk by another process or is empty

    1. PradeepAdiga Post author

      Thanks Santhu! The DBA did not capture much information before shrinking since he way in a hurry to fix this issue. The DBA was me 😉

      Reply
  1. RV

    Hi Pradeep,

    As you have mentioned in the article “The table size was only 60 MB now but the database had grown to 300 GB+ in size.” I was wondering at first place whether the database was 60MB or table inside the database was 60MB and again you said that database is 300 GB+ I am assuming that whether the table size increased from 60 mb to 300 MG. if I was going by your statement table size was only 60 MB, like you mentioned earlier the database started growing 10 GB /hour if this was the case, was there any data being pushed into the database from the application?

    Can you please let me know what exactly happened. Everything looks fine pradeep with this article but from my side I’m bit unclear.

    Thanks,

    RaviVarma.

    Reply
    1. PradeepAdiga Post author

      Hi Ravi,
      Thanks for the comment. May be the words that I had chosen are little ambiuous. Let me clarify it. The database had only 1 table in it. The application started inserting data in huge volume because of change in “logging level”. The database grew to 300+ GB. During this incident, Application team changed the setting and the table/database came down to 60 MB. Since the database had grown earlier there was a lot of free space left in the database and while shrinking the database we encountered this issue.

      Since the database had only 1 table (with no indexes), the table and database size are one and the same.

      Hope I have made it clear this time 😉

      Reply
  2. Pingback: Strange Error when shrinking db file - The Multifunctioning DBA

  3. Eric

    I wanted to let you know you helped me out. We were attempting to move 60GB of data from one file to another while the transaction log backup job was disabled. Something else modified enough data that the log file grew to 250GB+ overwhelming the disk. The shrink exited harshly and we found ourselves in your situation(error stating file currently being shrunk but no process identified as doing such). Your solution of growing by +1MB seemed to reset some internal logic and off we go. Thanks!

    Reply
  4. Pingback: Sql Server: File cannot be shrunk as it is either being shrunk by another process or is empty | Playing with database servers...

    1. Hemant

      Hi Pradeep,
      I have done the above commands for making free space on disk.
      after adding 1 mb free space on database file I can done only notruncateonly command
      but after executing truncateonly it does free make free space on disk.

      Reply
  5. Rasmussen

    Eu estava com o mesmo problema, reiniciei os serviços do “SQL Server 2012 R2” e então foi possível executar a redução do arquivo.

    Reply

Leave a Reply