SQL Server Shrink File Fails

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)

From serverfault Dustin Laine

• You probably have a valid reason for not moving the table to a new file-group.

  Ensure that all ancillary processes (SQL Agent, Log-Shipping, Replication, DB-Mirroring, third-party applications, etc.) have also been disabled.

  Dustin Laine : Is there a way to determine what could be still be accessing it? Would a move be the more appropriate action to take? Will the move need an extra 1.5TB? The reason for archiving is the lack of disk space to continue current growth.

  Robert Miller : As always, this is prefaced with 'It Depends'. Moving is preferred. If you are completely out of space, then the SkrinkFile or more hard-drive space. The problem with your ShrinkFile option is it attempts to release by file extents; in the reverse order they were added. If the last extent is not empty, then the Shrink will fail. Probably need to rebuild tables and it is a wonderful time to move the image table to a new File-Group, if you have enough drive space. Suggest rebuilding all tables -- Ouch -- followed by the DBCC ShrinkFile. Watch the T-Log file. Best done in Single-User mode.

  Robert Miller : Speaking from experience (I inherited a database storing images) it is best to move the images out of the database by storing them as disk-files. For a variety of reasons, I do not like the File-Stream option. This means you are left with architecture changes in your application. If you put together a good architecture, the change is worth it.

  Robert Miller : As far as ancillary services, you can stop the SQL Server Agent. If Replication or DB-Mirroring are set up, you should know. The same with Log-Shipping. Third-Party applications? Again, I would figure you know which ones have been installed on the box. Also check for file access from remote systems (Windows Server 2008 "Share and Storage Management" in Administrative tools). All of this presumes you have control of the database server and the system it is running on.

  mrdenny : None of the services listed need to be disabled to shrink a data file.
  From Robert Miller

• What output do you get when you run the DBCC SHRINKFILE command manually?

  Dustin Laine : When running `DBCC SHRINKFILE ([LOGICALNAMEOFDATAFILE], TRUNCATEONLY)`, I get `A severe error occurred on the current command. The results, if any, should be discarded.`

  mrdenny : Please run DBCC CHECKDB on the database in question, and if there are any errors fix them as the output instructs. Also let me know what comes up.
  From mrdenny