SQL Server Shrink File Fails

sql-server-2008

I have a large database 1.5 TB, which contains image data. There is an archiving process that has been implemented which has removed a large portion of the data. I want to shrink the size of the data file.

I am using the Shrink File dialog from the SSMS 2008 user interface. I am selecting to Release unused space and the operation fails after about 30 seconds. I have ensured there are no active connections to the DB. The error, while uninformative will be below.

==================================

Shrink failed for DataFile
'DBNAME'.
(Microsoft.SqlServer.Smo)

—————————— For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+DataFile&LinkId=20476

—————————— Program Location:

at
Microsoft.SqlServer.Management.Smo.DatabaseFile.Shrink(Int32
newSizeInMB, ShrinkMethod shrinkType)
at
Microsoft.SqlServer.Management.SqlManagerUI.ShrinkDatabaseFiles.OnRunNow(Object
sender)

===================================

An exception occurred while executing
a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

—————————— Program Location:

at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
sqlCommand, ExecutionTypes
executionType) at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection
sqlCommands, ExecutionTypes
executionType) at
Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection
queries) at
Microsoft.SqlServer.Management.Smo.DatabaseFile.Shrink(Int32
newSizeInMB, ShrinkMethod shrinkType)

===================================

A severe error occurred on the current
command. The results, if any, should
be discarded. (.Net SqlClient Data
Provider)

—————————— For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

—————————— Server Name: SERVERNAME Error Number: 0
Severity: 11 State: 0

—————————— Program Location:

at
System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj) at
System.Data.SqlClient.TdsParser.Run(RunBehavior
runBehavior, SqlCommand cmdHandler,
SqlDataReader dataStream,
BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject
stateObj) at
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String
methodName, Boolean async) at
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean
sendToPipe) at
System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
sqlCommand, ExecutionTypes
executionType)

Best Answer

I had given up in shrink and was starting to investigate my options on moving to new file. That question was answered with a suggestion that solved my original issue here.

Using the Reorganize pages before releasing unused space in small chunks did the trick. Once I got it down a bit, I was able to use large 100GB+ chunks.