Shrink Data File

 

http://technet.microsoft.com/en-us/library/ms189493.aspx

Remarks

DBCC SHRINKFILE applies to the files in the current database. For more information about how to change the current database, see USE (Transact-SQL) .

DBCC SHRINKFILE operations can be stopped at any point in the process , and any completed work is retained.

When a DBCC SHRINKFILE operation fails an error is raised.

The database being shrunk does not have to be in single-user mode; other users can be working in the database when the file is shrunk. You do not have to run the instance of SQL Server in single-user mode to shrink the system databases.

How to monitor the process and percentage complete

SELECT percentage_complete,*

FROM sys.dm_exec_requests

WHERE session_id=XXX

 

Why shrink data file taking long time

http://blog.sqlauthority.com/2008/07/25/sql-server-dbcc-shrinkfile-takes-long-time-to-run/

DBCC SHRINKFILE is a single threaded operation. A single threaded operation does not take advantage of multiple CPUs and have no effect how many RAM are available. Hyperthreaded CPU even provides worst performance.

 

 

Why shrink data file causing index fragmenation

http://www.sqlskills.com/blogs/paul/post/Why-you-should-not-shrink-your-data-files.aspx

A data file shrink operation works on a single file at a time, and uses the GAM bitmaps (see Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps ) to find the highest page allocated in the file. It then moves it as far towards the front of the file as it can, and so on, and so on. In the case above, it completely reversed the order of the clustered index, taking it from perfectly defragmented to perfectly fragmented.

Shrinking a Log File

For log files, the Database Engine uses target_size to calculate the target size for the whole log; therefore, target_size is the amount of free space in the log after the shrink operation. Target size for the whole log is then translated to target size for each log file. DBCC SHRINKFILE tries to shrink each physical log file to its target size immediately. However, if part of the logical log resides in the virtual logs beyond the target size, the Database Engine frees as much space as possible, and then issues an informational message. The message describes what actions are required to move the logical log out of the virtual logs at the end of the file. After the actions are performed, DBCC SHRINKFILE can be used to free the remaining space. For more information, see Shrinking the Transaction Log .

Because a log file can only be shrunk to a virtual log file boundary, shrinking a log file to a size smaller than the size of a virtual log file might not be possible, even if it is not being used. The size of the virtual log file is chosen dynamically by the Database Engine when log files are created or extended. For more information about virtual log files, see Transaction Log Physical Architecture .

Best Practices

Consider the following information when you plan to shrink a file:

• A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.

• Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.

• A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree . This is another reason not to repeatedly shrink the database.

Troubleshooting

This section describes how to diagnose and correct issues that can occur when running the DBCC SHRINKFILE command.

The File Does Not Shrink

If the shrink operation runs without error, but the file does not appear to have changed in size, verify that the file has adequate free space to remove by performing one of the following operations:

• Run the following query.

  SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
  
  
  
  FROM sys.database_files;

• Run the DBCC SQLPERF command to return the space used in the transaction log.

If insufficient free space is available, the shrink operation cannot reduce the file size any further.

Typically it is the log file that appears not to shrink. This is usually the result of a log file that has not be truncated. You can truncate the log by setting the database recovery model to SIMPLE, or by backing up the log and then running the DBCC SHRINKFILE operation again. For more information, see Transaction Log Truncation and Shrinking the Transaction Log .

The Shrink Operation Is Blocked

It is possible for shrink operations to be blocked by a transaction that is running under a row versioning-based isolation level . For example, if a large delete operation running under a row versioning-based isolation level is in progress when a DBCC SHRINK DATABASE operation is executed, the shrink operation will wait for the delete operation to complete before shrinking the files . When this happens, DBCC SHRINKFILE and DBCC SHRINKDATABASE operations print out an informational message (5202 for SHRINKDATABASE and 5203 for SHRINKFILE) to the SQL Server error log every five minutes in the first hour and then every hour after that. For example, if the error log contains the following error message:

DBCC SHRINKFILE for file ID 1 is waiting for the snapshot 



transaction with timestamp 15 and other snapshot transactions linked to 



timestamp 15 or with timestamps older than 109 to finish.

This means that the shrink operation is blocked by snapshot transactions that have timestamps older than 109, which is the last transaction that the shrink operation completed. It also indicates that the transaction_sequence_num , or first_snapshot_sequence_num columns in the sys.dm_tran_active_snapshot_database_transactions dynamic management view contains a value of 15. If either the transaction_sequence_num , or first_snapshot_sequence_num columns in the view contains a number that is less than the last transaction completed by a shrink operation (109), the shrink operation will wait for those transactions to finish.

To resolve the problem, you can do one of the following tasks:

• Terminate the transaction that is blocking the shrink operation.

• Terminate the shrink operation. If the shrink operation is terminated, any completed work is retained.

• Do nothing and allow the shrink operation to wait until the blocking transaction completes.

For more information about the SQL Server error log, see Viewing the SQL Server Error Log .

 Permissions

Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

 Examples

A. Shrinking a data file to a specified target size

The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7  MB.

Transact-SQL

USE UserDB;



GO



DBCC SHRINKFILE (DataFile1, 7);



GO

B. Shrinking a log file to a specified target size

The following example shrinks the log file in the AdventureWorks2008R2 database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

Transact-SQL

USE AdventureWorks2008R2;



GO



-- Truncate the log by changing the database recovery model to SIMPLE.



ALTER DATABASE AdventureWorks2008R2



SET RECOVERY SIMPLE;



GO



-- Shrink the truncated log file to 1 MB.



DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);



GO



-- Reset the database recovery model.



ALTER DATABASE AdventureWorks2008R2



SET RECOVERY FULL;



GO

C. Truncating a data file

The following example truncates the primary data file in the AdventureWorks2008R2 database. The sys.database_files catalog view is queried to obtain the file_id of the data file.

Transact-SQL

USE AdventureWorks2008R2;



GO



SELECT file_id, name



FROM sys.database_files;



GO



DBCC SHRINKFILE (1, TRUNCATEONLY);

D. Emptying a file

The following example demonstrates the procedure for emptying a file so that it can be removed from the database. For the purposes of this example, a data file is first created and it is assumed that the file contains data.

Transact-SQL

USE AdventureWorks2008R2;



GO



-- Create a data file and assume it contains data.



ALTER DATABASE AdventureWorks2008R2 



ADD FILE (



    NAME = Test1data,



    FILENAME = 'C:/t1data.ndf',



    SIZE = 5MB



    );



GO



-- Empty the data file.



DBCC SHRINKFILE (Test1data, EMPTYFILE);



GO



-- Remove the data file from the database.



ALTER DATABASE AdventureWorks2008R2



REMOVE FILE Test1data;



GO