Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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)

share|improve this question
1  
Just curious: Why would you want to store images in a database? I can see you crashing pretty much any backup program you try since your going to have a terabyte backup. –  TheLQ Oct 24 '10 at 0:34
    
Although not best practice, it does performs really well. The backups were a challenge, but work fine. For what its worth, it was not my decision, and this is the first step into switching to FileStream datatype. –  Dustin Laine Oct 24 '10 at 16:03
add comment

3 Answers

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

share|improve this answer
    
When running DBCC SHRINKFILE ([LOGICALNAMEOFDATAFILE], TRUNCATEONLY), I get A severe error occurred on the current command. The results, if any, should be discarded. –  Dustin Laine Oct 25 '10 at 6:15
    
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. –  mrdenny Oct 25 '10 at 15:46
add comment

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.

share|improve this answer
    
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. –  Dustin Laine Oct 24 '10 at 16:01
    
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 Oct 24 '10 at 17:12
    
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 Oct 24 '10 at 17:17
    
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. –  Robert Miller Oct 24 '10 at 17:29
    
None of the services listed need to be disabled to shrink a data file. –  mrdenny Oct 25 '10 at 15:46
add comment
up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.