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.
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.
- To create a table in the database and insert sample data into it. Then try shrinking.
- 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.