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)
FileStream
datatype.