2

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)

2
  • 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, 2010 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. Oct 24, 2010 at 16:03

4 Answers 4

2

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.

1

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

2
  • When running DBCC SHRINKFILE ([LOGICALNAMEOFDATAFILE], TRUNCATEONLY), I get A severe error occurred on the current command. The results, if any, should be discarded. Oct 25, 2010 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, 2010 at 15:46
0

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.

5
  • 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. Oct 24, 2010 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. Oct 24, 2010 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. Oct 24, 2010 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. Oct 24, 2010 at 17:29
  • None of the services listed need to be disabled to shrink a data file.
    – mrdenny
    Oct 25, 2010 at 15:46
0

For me is shrink after I rebuild the indexes of the table that have the biggest fragmentation

I know from what table I have delete most of the data, or found it from statistics...

Then open that table like this capture

Page fullness and fragmentation[Page fullness and fragmentation]

Make Rebuild it Rebuild clustered index[Rebuild clustered index]

Shrink data file : Tasks | Shrink | Files in SQL Server Management Studio with "Reorganize files before releasing unused space"

After that rebuild, the shrink works.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .