A few days ago, on one of the production database servers we ran into space issue. One of the databases, which is usually at 300 GB size with increase on 1 0r 2 GB per day increased to more than 500 GB in a couple of days. This is one of the critical servers that houses data received from devices located all over the globe. So we had to quickly do something before the database comes to a halt.

Side Note: When database grows and takes up all the space available on the disk, it goes into read only mode. I am not sure if it goes into READONLY mode, but it responds only to SELECT queries, but not UPDATE/INSERT statements that add data to tables in the database.

So, we immediately added a couple of data files on a different drive to let the database grow and give us some time to implement a complete solution. In the last few weeks, the amount of data received has increased significantly and the purge job that was supposed to keep it relatively small has not been running as expected. It was running successfully so there were no alerts raised. But it was not DELETing the data as expected.

We refactored the purge job and let it run for a few hours. After a couple of days of this, we were able to purge more than a billion records and clear up more than 200 GB free space.

Side Note: This database should’ve been designed with table partitions to allow for simple maintenance, but we did not. May be in near future we’ll look into it.

Now we need to free up some of this space and release it back to the Operating System. When I ran SHRINKFILE we ran into this error:

[sourcecode language=”sql”]

DBCC SHRINKFILE (DB_Data, 307200, NOTRUNCATE) — to 300 GB
GO

ERROR:

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

— DBCC SHRINKFILE (DB_Data, 307200, TRUNCATEONLY) — resize to 300 GB

— GO

[/sourcecode]

No ones else is running SHRINKFILE at the same time and I know the file is not empty. Also, the backup operation is not running (sometimes backup operation causes this error)

According to this MSDN article we did “You may continue to receive this error message until you restart the SQL Server service. Or, you may continue to receive this error message until you take the database offline and then put the database online again.” But that did not solve the problem. Then on further efforts, we ran into this article on DBA Diaries.

We tried option 2 and it worked. Increase the data file by a few MB and reattempt SHRINKFILE operation.

[sourcecode language=”sql”]
ALTER DATABASE [DB]
MODIFY FILE
(
NAME = N’DB_Dat’
, SIZE = 307210 MB
)
GO

[/sourcecode]

After this, I ran the SHRINKFILE again and was able to reclaim free space on the drive.

Side Note: SHRINKFILE is not a common operation. Very rarely does any one run it on production machines and is usually a tool for a unique situation or as a last resort. Also, it creates a lot of fragmentation. Please read this MSDN article thoroughly before attempting it.

Hope this helps,
_Sqltimes