数据库维护-收缩，压缩，修复数据库

数据库维护-收缩，压缩，修复数据库，

2008-11-10 16:01

如何维护数据库 (SQL Server Management Studio)   维护数据库时将涉及对数据库进行收缩、压缩和修复操作。通过使用 SQL Server Management Studio，可以在同一对话框内执行所有这些任务。 如何收缩数据库   在对象资源管理器中，连接到 SQL Server 数据库引擎实例，然后展开该实例。 展开“数据库”，再右键单击要收缩的数据库。 指向“任务”，指向“收缩”，然后单击“数据库”。 根据需要，可以选中“在释放未使用的空间前重新组织文件”复选框。如果选中该复选框，必须为“收缩后文件中的最大可用空间”指定值。 选中该选项的作用与执行 DBCC SHRINKDATABASE 时指定 target_percent 值相同。清除该选项的作用与使用 TRUNCATEONLY 选项执行 DBCC SHRINKDATABASE 相同。TRUNCATEONLY 将文件收缩到最后分配的区。这将减小文件的大小，但不移动任何数据。默认情况下，该选项为清除状态。 输入收缩数据库后数据库文件中剩下的最大可用空间百分比。允许的值介于 0 和 99 之间。该选项仅在选中“在释放未使用的空间前重新组织文件”时可用。 单击“确定”。 在对象资源管理器中，连接到 SQL Server 数据库引擎实例，然后展开该实例。 展开“数据库”，再右键单击要收缩的数据库。 指向“任务”，指向“收缩”，然后单击“数据库”。 根据需要，可以选中“在释放未使用的空间前重新组织文件”复选框。如果选中该复选框，必须为“收缩后文件中的最大可用空间”指定值。 选中该选项的作用与执行 DBCC SHRINKDATABASE 时指定 target_percent 值相同。清除该选项的作用与使用 TRUNCATEONLY 选项执行 DBCC SHRINKDATABASE 相同。TRUNCATEONLY 将文件收缩到最后分配的区。这将减小文件的大小，但不移动任何数据。默认情况下，该选项为清除状态。 输入收缩数据库后数据库文件中剩下的最大可用空间百分比。允许的值介于 0 和 99 之间。该选项仅在选中“在释放未使用的空间前重新组织文件”时可用。 单击“确定”。 语法 DBCC SHRINKFILE (      { file_name | file_id }      { [ , EMPTYFILE ]      | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]      } ) [ WITH NO_INFOMSGS ] 参数 file_name 要收缩的文件的逻辑名称。 file_id 要收缩的文件的标识 (ID) 号。若要获得文件 ID，请使用 FILE_IDEX 系统函数，或查询当前数据库中的 sys.database_files 目录视图。 target_size 用兆字节表示的文件大小（用整数表示）。如果未指定，则 DBCC SHRINKFILE 将文件大小减少到默认文件大小。默认大小为创建文件时指定的大小。 注意： 可以使用 DBCC SHRINKFILE target_size 减小空文件的默认大小。例如，如果创建一个 5 MB 的文件，然后在文件仍然为空的时候将文件收缩为 3 MB，默认文件大小将设置为 3 MB。这只适用于永远不会包含数据的空文件。     如果指定了 target_size，则 DBCC SHRINKFILE 尝试将文件收缩到指定大小。将要释放的文件部分中的已使用页重新定位到保留的文件部分中的可用空间。例如，如果数据文件为 10 MB，则 target_size 为 8 的 DBCC SHRINKFILE 操作会将文件最后 2 MB 中所有的已使用页重新分配到文件前 8 MB 中的任何未分配页中。DBCC SHRINKFILE 不会将文件收缩到小于存储文件中的数据所需要的大小。例如，如果使用 10 MB 数据文件中的 7 MB，则带有 target_size 为 6 的 DBCC SHRINKFILE 语句只能将该文件收缩到 7 MB，而不能收缩到 6 MB。 EMPTYFILE 将指定文件中的所有数据迁移到同一文件组中的其他文件。由于数据库引擎不再允许将数据放在空文件内，因此可以使用 ALTER DATABASE 语句来删除该文件。 NOTRUNCATE 在指定或不指定 target_percent 的情况下，将已分配的页从数据文件的末尾移动到该文件前面的未分配页。文件末尾的可用空间不会返回给操作系统，文件的物理大小也不会更改。因此，指定 NOTRUNCATE 时，文件看起来未收缩。 NOTRUNCATE 只适用于数据文件。日志文件不受影响。 TRUNCATEONLY 将文件末尾的所有可用空间释放给操作系统，但不在文件内部执行任何页移动。数据文件只收缩到最后分配的区。 如果随 TRUNCATEONLY 指定了 target_size，则会忽略该参数。 TRUNCATEONLY 只适用于数据文件。 WITH NO_INFOMSGS 取消显示所有信息性消息。 结果集 下表对结果集中的列进行了说明。 列名 说明 DbId 数据库引擎试图收缩的文件的数据库标识号。 FileId 数据库引擎试图收缩的文件的文件标识号。 CurrentSize 文件当前占用的 8 KB 页数。 MinimumSize 文件最低可以占用的 8 KB 页数。这与文件的最小大小或最初创建时的大小相对应。 UsedPages 文件当前使用的 8 KB 页数。 EstimatedPages 数据库引擎估计文件能够收缩到的 8 KB 页数。 备注 DBCC SHRINKFILE 适用于当前数据库中的文件。有关如何更改当前数据库的详细信息，请参阅 USE (Transact-SQL)。 可在进程中的任一点停止 DBCC SHRINKFILE 操作，任何已完成的工作都将保留。 当 DBCC SHRINKFILE 操作失败时，将引发错误。 要收缩的数据库不必在单用户模式下；收缩文件时，其他用户也可使用该数据库。不必在单用户模式下运行 SQL Server 实例以对系统数据库进行收缩。 收缩日志文件 对于日志文件，数据库引擎使用 target_size 来计算整个日志的目标大小；因此，target_size 是收缩操作后日志中的可用空间大小。之后，整个日志的目标大小转换为每个日志文件的目标大小。DBCC SHRINKFILE 尝试立即将每个物理日志文件收缩到其目标大小。但是，如果部分逻辑日志位于超出目标大小的虚拟日志中，则数据库引擎将释放尽可能多的空间，并发出一条信息性消息。该消息说明需要执行哪些操作来将逻辑日志移出位于文件末尾的虚拟日志。执行这些操作以后，DBCC SHRINKFILE 可用于释放剩余空间。有关详细信息，请参阅收缩事务日志。 因为日志文件只能收缩到虚拟日志文件边界，所以不可能将日志文件收缩到比虚拟日志文件更小（即使现在没有使用该文件）。虚拟日志文件的大小在创建或扩展这些日志文件时由数据库引擎动态选择。有关虚拟日志文件的详细信息，请参阅事务日志物理体系结构。 最佳实践 在计划收缩文件时，请考虑以下信息： 在执行会产生许多未使用空间的操作（如截断表或删除表操作）后，执行收缩操作最有效。 大多数数据库都需要一些可用空间，以供常规日常操作使用。如果反复收缩数据库并注意到数据库大小变大，则表明收缩的空间是常规操作所必需的。在这种情况下，反复收缩数据库是一种无谓的操作。 收缩操作不会保留数据库中索引的碎片状态，通常还会在一定程度上增加碎片。这是不要反复收缩数据库的另一个原因。 故障排除 本部分介绍如何诊断和更正在运行 DBCC SHRINKFILE 命令时可能发生的问题。 文件不收缩 如果收缩操作运行时未出现错误，但文件大小看起来没有发生更改，则请执行下列操作之一以验证文件是否有足够的可用空间可供删除： 运行以下查询。 复制代码 SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files; 运行 DBCC SQLPERF 命令以返回事务日志中使用的空间。 如果可用空间不足，则收缩操作无法进一步减小文件大小。 通常，日志文件看起来不收缩。这通常是由于未截断日志文件的原因造成的。可以通过将数据库恢复模式设置为 SIMPLE 或者通过备份日志然后再次运行 DBCC SHRINKFILE 操作来截断日志。有关详细信息，请参阅事务日志截断和收缩事务日志。 收缩操作被阻塞 在基于行版本控制的隔离级别下运行的事务可能会阻塞收缩操作。例如，执行 DBCC SHRINK DATABASE 操作时，如果在基于行版本控制的隔离级别下运行的大型删除操作正在进行中，则收缩操作将等到删除操作完成才会收缩文件。出现这种情况时，DBCC SHRINKFILE 和 DBCC SHRINKDATABASE 操作会在第一个小时每五分钟将信息性消息（对于 SHRINKDATABASE 为 5202，对于 SHRINKFILE 为 5203）输出到 SQL Server 错误日志，之后每一个小时输出一次。例如，如果错误日志包含以下错误消息： 复制代码 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. 这意味着收缩操作被时间戳早于 109 的快照事务阻塞，它是收缩操作所完成的上一事务。它还说明 sys.dm_tran_active_snapshot_database_transactions 动态管理视图中的 transaction_sequence_num 或 first_snapshot_sequence_num 列包含值 15。如果该视图中的 transaction_sequence_num 或 first_snapshot_sequence_num 列包含的数字小于收缩操作完成的上一事务 (109)，则收缩操作将等待这些事务完成。 若要解决此问题，请执行下列任务之一： 终止阻塞收缩操作的事务。 终止收缩操作。如果终止收缩操作，则会保留任何已完成的工作。 不执行任何操作，并允许收缩操作等到阻塞事务完成。 有关 SQL Server 错误日志的详细信息，请参阅查看 SQL Server 错误日志。 权限 要求具有 sysadmin 固定服务器角色或 db_owner 固定数据库角色的成员身份。 示例 A. 将数据文件收缩到指定的目标大小 以下示例将 UserDB 用户数据库中名为 DataFile1 的数据文件的大小收缩到 7 MB。 复制代码 USE UserDB; GO DBCC SHRINKFILE (DataFile1, 7); GO B. 将日志文件收缩到指定的目标大小 以下示例将 AdventureWorks 数据库中的日志文件收缩到 1 MB。若要允许 DBCC SHRINKFILE 命令收缩文件，首先需要通过将数据库恢复模式设置为 SIMPLE 来截断该文件。 复制代码 USE AdventureWorks; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (AdventureWorks_Log, 1); GO -- Reset the database recovery model. ALTER DATABASE AdventureWorks SET RECOVERY FULL; GO C. 截断数据文件 以下示例将截断 AdventureWorks 数据库中的主数据文件。需要查询 sys.database_files 目录视图以获得数据文件的 file_id。 复制代码 USE AdventureWorks; GO SELECT file_id, name FROM sys.database_files; GO DBCC SHRINKFILE (1, TRUNCATEONLY); D. 清空文件 以下示例演示了清空文件以便从数据库中将其删除的步骤。针对此示例，首先创建一个数据文件，并假设该文件包含数据。 复制代码 USE AdventureWorks; GO -- Create a data file and assume it contains data. ALTER DATABASE AdventureWorks 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 AdventureWorks REMOVE FILE Test1data; GO 如何压缩数据库 在 Management Studio 中，打开对象资源管理器。 在对象资源管理器中，单击“连接”，然后选择“SQL Server Compact Edition”。 在“连接到服务器”对话框中，选择要压缩的 SQL Server Compact Edition 数据库，然后单击“连接”。 在对象资源管理器中，右键单击所添加的 SQL Server Compact Edition 数据库，然后选择“属性”。 “数据库属性”对话框将打开。选择“执行完全数据库压缩”。 在“文件选项”下，您可以选择在压缩之后替换现有的数据库，或使用新的文件名创建新的数据库。通过选择“覆盖现有数据库文件”复选框，您可以修改现有数据库的名称与位置，然后进行替换。 如何修复数据库 在 Management Studio 中，打开对象资源管理器。 在对象资源管理器中，单击“连接”，然后选择“SQL Server Compact Edition”。 在“连接到服务器”对话框中，选择要修复的 SQL Server Compact Edition 数据库，然后单击“连接”。 在对象资源管理器中，右键单击所添加的 SQL Server Compact Edition 数据库，然后选择“属性”。 “数据库属性”对话框将打开。选择“修复物理损坏的数据库”。 如果选中“恢复已损坏的行”复选框，将可以恢复已损坏的行。 在“文件选项”下，您可以选择在修复之后替换现有的数据库，或使用新的文件名创建新的数据库。通过选择“覆盖现有数据库文件”复选框，您可以修改现有数据库的名称与位置，然后进行替换。