收缩数据文件

在执行DBCC ShrinkFile命令，收缩数据文件的时候，SQL Server首先将文件尾部的区（extent）移动到文件的开头，文件结尾的空闲的Disk空间会被收缩，释放给操作系统，就像截断将文件的尾部一样。在收缩时，SQL Server会扫描数据文件并对正在读取的页面加锁，所以对数据库的性能会有所影响。但是这不是一个独占行为，在做shrinkfile的时候，其他用户仍然可以对数据库进行读写操作。在进程中的任意一个时间点停止dbcc shrinkfile操作，任何已经完成的工作都将保留。

DBCC SHRINKFILE命令做的收缩都是区一级的动作，它会把使用过的区前移，把没有使用的区从文件中移除。但是，它不会把一个区里面的空闲页面(empty page)移除、合并区，也不会把页面里面的空间移除、合并页面。所以，一个数据库中有很多只使用了一两个页面的区，DBCC SHRINKFILE的效果会不明显。

一，DBCC ShrinkFile Syntax

DBCC SHRINKFILE 
(
    { file_name | file_id } 
    { [ , EMPTYFILE ] 
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)
[ WITH NO_INFOMSGS ]

target_size  单位是MB

Is the size for the file in MB, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size. The default size is the size specified when the file was created.

This option is not supported for FILESTREAM filegroup containers.

If target_size is specified, DBCC SHRINKFILE tries to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, if there is a 10-MB data file, a DBCC SHRINKFILE operations with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any unallocated pages in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.

EMPTYFILE               

Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.
NOTRUNCATE

Moves allocated pages from the end of a data file to unallocated pages in the front of the file with or without specifying target_percent. The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change. Therefore, when NOTRUNCATE is specified, the file appears not to shrink.

NOTRUNCATE is applicable only to data files. The log files are not affected.

TRUNCATEONLY               

Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.

target_size is ignored if specified with TRUNCATEONLY.

The TRUNCATEONLY option does not move information in the log, but does remove inactive VLFs from the end of the log file.

1，Remarks

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

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.

2，Shrinking  file, including in data and log

如果想收缩Database的 Data file和Log file， 释放空间，那么需要做两步，先移动，后释放

Step1，将File的 End portion 中正在使用的extent向前移动，移动到File的Front portion

dbcc shrinkfile('filename',0,notruncate)

Step2，将file的End portion中未被使用的Extent释放掉，归还给OS

dbcc shrinkfile('filename',target_size_MB,truncateonly)

3，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.

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.

事务日志文件不停增长的原因有很多，比如1，Recovery Mode 不是simple，或者说不处于auto-truncate mode下，2，有一个很大的事务在运行，3，有一个运行很长时间的事务没有提交 都会导致事务日志文件不停增长，此时 dbcc shrinkflie 命令是不能把日志文件shrink的，必须针对特定的情况，采取对应的对策，一般来说，对于1，必须安排backup log的计划，使事务日志文件截断和重用 ，对于2，优化语句的业务逻辑，避免出现大的事务，对于3，优化application，及时commit 或 rollback 事务。

3， Side Effect

Shrinking a data file requires a lot of IO,CPU, and buffer pool resources, generates a lot of transaction log records, and generally results in index fragmentation. Shrinking a log file does not consume any significant resources. However ,if the log is shrunk too small, it must grow again, which require the new portion of the log to undergo zero initialization, wich stalls logging activity.

由于Data file的增长也是非常耗时的，所以，在Shrinkfile时，不要将Data file收缩得太小，要预留足够的空间，避免Data File出现增长的情况。

3.1，影响Range操作的查询性能

Shrink file 有可能产生大量的碎片，这是因为shrink file 存在Extent的移动，逻辑上连续的extent，在移动之后，可能会导致物理上不再连续，影响Range 操作的性能。

Running DBCC SHRINKFILE or DBCC SHRINKDATABASE may introduce fragmentation if an index is partly or completely moved during the shrink operation. Therefore, if a shrink operation must be performed, you should do it before fragmentation is removed.

3.2，消耗IO和CPU资源

如果SQL Server在shrink data file时不移动数据，那么shrink 就不会产生碎片，对现有数据不会有影响。唯一不影响现有物理数据的情况是在执行DBCC Shrinkfile 命令时指定TruncateOnly选项，DBCC Shrink命令只将文件末尾的剩余空间释放，归还给OS。

Shrinkfile 是个非常消耗IO资源的操作，Shrinkfile的过程需要移动大量的数据，消耗大量的IO；Shrinkfile的过程会被记录到日志，造成日志暴涨；还会消耗大量的CPU资源。

3.3，产生大量的日志，影响依赖日志的Application的性能

收缩产生的大量日志会被事务日志传送，镜像，复制等操作重复执行。

3.4，ShrinkFile 不能将Data File 收缩的太小

当插入新的数据时，如果Data File空间不够，那么SQL Server需要重新申请 Disk Space，增加 Data file Size，在此过程中，SQL Server需要对新分配的Disk Space填0初始化，除非你开启的是不用填零初始化的选项，不用填零初始化有泄露信息的风险。在全部空间初始化完成之前，新分配的Data File是不能使用的。这个填0初始化，是个非常耗费IO资源的操作，如果一次增长的空间过大，SQL Server需要很长时间对新分配的Disk Space进行初始化，这可能会导致Application timeout，使事务回滚，影响Application的性能。

二，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.

• Shrink multiple files in the same database sequentially instead of concurrently. Contention on system tables can cause delays due to blocking.

三，Troubleshooting

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

1，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.

  dbcc sqlperf('LogSpace')

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 been 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.

2，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 then the following error will occur:

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 issue, 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.

四，Empty 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.

USE AdventureWorks2012;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2012 
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 AdventureWorks2012
REMOVE FILE Test1data;
GO

 

参考文档：

DBCC SHRINKFILE (Transact-SQL)

Why you want to be restrictive with shrink of database files 

转载于:https://www.cnblogs.com/xieyulin/p/7050658.html