sql-server – 截断200GB表但未释放磁盘空间

发布时间：2020-12-12 06:21:34 所属栏目：MsSql教程来源：网络整理

导读：我还剩2GB,所以我需要删除这个历史表.此表现在为空,但未释放数据库磁盘空间.数据库文件是320GB. 解决方法如果要引用卷上的实际数据库文件使用量,则SQL Server不会自动处理该数据库文件.仅仅因为您从数据库中删除数据并不意味着数据库文件将缩小以仅适合现有

我还剩2GB,所以我需要删除这个历史表.此表现在为空,但未释放数据库磁盘空间.数据库文件是320GB.

解决方法

如果要引用卷上的实际数据库文件使用量,则SQL Server不会自动处理该数据库文件.仅仅因为您从数据库中删除数据并不意味着数据库文件将缩小以仅适合现有数据.

您要寻找的内容,如果您需要回收卷上的空间,将使用DBCC SHRINKFILE缩小特定文件.根据该文档,值得注意一些最佳实践：

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.

还要注意：

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

这样做肯定有一些事情需要考虑,我建议你看看Paul Randal’s blog post,当你做这个操作时会发生什么.

第一步肯定是验证您实际可以替换多少空间和可用空间,以及文件中使用的空间：

use AdventureWorks2012;
go

;with db_file_cte as
(
    select
        name,type_desc,physical_name,size_mb = 
            convert(decimal(11,2),size * 8.0 / 1024),space_used_mb = 
            convert(decimal(11,fileproperty(name,'spaceused') * 8.0 / 1024)
    from sys.database_files
)
select
    name,size_mb,space_used_mb,space_used_percent = 
        case size_mb
            when 0 then 0
            else convert(decimal(5,space_used_mb / size_mb * 100)
        end
from db_file_cte;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!