“Shrink failed for Database” when attempting to shrink a data file.

Sometimes, when you try to shrink a data file you may get the following message:

TITLE: Microsoft SQL Server Management Studio
Shrink failed for Database ‘Data base name’.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+Database&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
A severe error occurred on the current command.  The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

First, you should know that shrinking data files is not recommended and will hurt performance. Shrinking data files will cause index fragmentation. You can recreate the indexes but it will require the freed space. Shrinking will also cause fragmentation in the server’s file system which will slow it even more. In addition, since every page move is logged to the transaction log, chances are that the transaction log will claim the same space.

However, if in case you decide to shrink the database anyway (for example, after a large and permanent delete, or in case it is a test system) a common reason for this error is lack of space.
Since many times the attempt to shrink a database will come after discovering that the drive is running out of space, it would only make sense that there is not enough space for the shrinking process itself.

To verify, open the sql server error log (usually under Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG) and look for Operating system error 112(There is not enough space on the disk.).

To solve, you should clear some space to allow the process to complete. If you can not free some disk space, Try to shrink the transaction log and tempdb.

One thought on ““Shrink failed for Database” when attempting to shrink a data file.

  1. Gaurav Srivastava

    Remaining space on drive was 10.5 GB. Data file of the database did not shrink when I tried to shrink it by 5 GB. It shrank when I shrank it by 1 GB only.

Leave a Reply

Your email address will not be published. Required fields are marked *