sql server表空间如何回收

本文提供了一套详细的SQL Server数据库优化流程,包括清空及截断事务日志、手动及自动收缩数据库文件的方法,以及如何限制日志文件的增长等实用技巧。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

/*--特别注意 
 
请按步骤进行,未进行前面的步骤,请不要做后面的步骤 
否则可能损坏你的数据库. 
 
 
一般不建议做第4,6两步 
第4步不安全,有可能损坏数据库或丢失数据 
第6步如果日志达到上限,则以后的数据库处理会失败,在清理日志后才能恢复. 
--*/ 
 
--下面的所有库名都指你要处理的数据库的库名 
 
1.清空日志 
DUMP  TRANSACTION  Mydpc  WITH  NO_LOG    
 
2.截断事务日志: 
BACKUP LOG Mydpc WITH NO_LOG 
 
3.收缩数据库文件(如果不压缩,数据库的文件不会减小 
企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件 
--选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了 
--选择数据文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了 
 
也可以用SQL语句来完成 
--收缩数据库 
DBCC SHRINKDATABASE(库名) 
 
--收缩指定数据文件,1是文件号,可以通过这个语句查询到:select * from sysfiles 
DBCC SHRINKFILE(1) 
 
4.为了最大化的缩小日志文件(如果是sql 7.0,这步只能在查询分析器中进行) 
a.分离数据库: 
企业管理器--服务器--数据库--右键--分离数据库 
 
b.在我的电脑中删除LOG文件 
 
c.附加数据库: 
企业管理器--服务器--数据库--右键--附加数据库 
 
此法将生成新的LOG,大小只有500多K 
 
或用代码: 
下面的示例分离 pubs,然后将 pubs 中的一个文件附加到当前服务器。 
 
a.分离 
EXEC sp_detach_db @dbname = '库名' 
 
b.删除日志文件 
 
c.再附加 
EXEC sp_attach_single_file_db @dbname = '库名'
  @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\库名.mdf' 
 
5.为了以后能自动收缩,做如下设置: 
企业管理器--服务器--右键数据库--属性--选项--选择"自动收缩" 
 
--SQL语句设置方式: 
EXEC sp_dboption '库名''autoshrink''TRUE' 
 
6.如果想以后不让它日志增长得太大 
企业管理器--服务器--右键数据库--属性--事务日志 
--将文件增长限制为xM(x是你允许的最大数据文件大小) 
 
--SQL语句的设置方式: 
alter database 库名 modify file(name=逻辑文件名,maxsize=20)
### 如何在 SQL Server 中清理和释放表空间 #### 查找并分析大对象存储情况 为了有效管理SQL Server中的表空间,首先要了解哪些对象占用了大量空间。可以通过以下查询来获取数据库中各个表及其索引的空间使用详情: ```sql USE YourDatabaseName; GO SELECT t.name AS TableName, s.Name AS SchemaName, p.rows as RowCounts, SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB, SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB, (SUM(a.total_pages) - sum(a.used_pages)) * 8/1024 as UnusedSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id GROUP BY t.Name, s.Name, p.Rows ORDER BY TotalSpaceMB DESC, t.Name; ``` 此脚本能够帮助识别那些占据过多资源的大表格[^1]。 #### 清理不再需要的数据记录 当定位到具体的目标表之后,可以根据实际需求删除不需要的历史数据或冗余记录。例如,假设有一个名为`LargeTable`的表,并且决定移除其中早于某个日期之前的所有行,则可采用如下命令: ```sql DELETE FROM LargeTable WHERE EntryDate < '2023-01-01'; ``` 需要注意的是,在执行大规模删除操作前应当充分评估其影响范围以及做好相应的备份工作。 #### 收缩文件以回收磁盘空间 完成上述两步后,即使物理上的分配单元已经被标记为空闲状态,这些改变并不会立即反映在外存设备上。此时就需要通过收缩文件的方式让操作系统层面也能感知到可用性的增加。针对特定编号为4的数据文件为例: ```sql DBCC SHRINKFILE(4); ``` 该指令将会尝试压缩指定ID对应的文件至最小尺寸,从而腾出更多自由区域供其他用途调配。 另外一种方法是利用 `SHRINKDATABASE` 命令一次性处理整个库内的所有文件,不过这种方式通常不推荐频繁使用因为它可能导致性能下降和其他潜在风险[^3]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值