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.