『收藏』收缩日志文件的SQL脚本

* 本文由微软新闻组摘录下来的。一段非常有用的脚本。*/

前几天也碰到日志文件过大的问题,数据库实际大小为600M, 日志文件实际大小为33M,
但日志文件占用空间为2.8G!!! 试了多种方式,SHIRNK DATABASE, TRUNCATE LOG FILE, 都没办法将文件缩小。无论如何,这应该算SQL SERVER的一个BUG吧。

后来找到下面的代码,就可以将日志文件缩小到自己想要的大小了。把代码COPY到查询
分析器里,,然后修改其中的3个参数(数据库名,日志文件名,和目标日志文件的大
小),运行即可(我已经用过多次了)
-----
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT


USE Marias -- 要操作的数据库名
SELECT @LogicalFileName = 'Marias_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 100 -- 你想设定的日志文件的大小(M)

-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)


DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not
expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name =
@LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF

### 定期收缩SQL Server日志文件的方法与最佳实践 在SQL Server中,定期收缩日志文件是一个重要的任务,它能够帮助优化磁盘空间使用并减少性能问题。以下是实现这一目标的具体方法和最佳实践: #### 方法一:通过SQL Server Management Studio (SSMS) 手动操作 1. **检查数据库恢复模式** 在开始之前,确保了解当前数据库的恢复模式(简单、完整或大容量日志备份)。如果需要频繁收缩日志文件,建议将恢复模式设置为“简单”模式[^4]。 2. **执行日志备份** 如果数据库处于完整恢复模式,首先需要进行事务日志备份。这将标记部分日志文件为空闲状态,允许后续收缩操作。可以使用以下命令: ```sql BACKUP LOG [数据库名称] TO DISK = 'C:\Backup\log.bak' ``` 这一步非常重要,因为只有在备份之后,SQL Server才会截断未使用的日志空间[^3]。 3. **收缩日志文件** 使用`DBCC SHRINKFILE`命令来收缩日志文件。例如: ```sql DBCC SHRINKFILE (N'数据库_log', 10) ``` 上述命令将日志文件收缩到10MB大小。需要注意的是,这里的文件名必须与实际的日志文件逻辑名称一致[^1]。 #### 方法二:通过SQL Server Agent 创建自动化作业 为了实现定期收缩日志文件,可以配置SQL Server Agent中的作业计划。以下是具体步骤: 1. **新建作业** 在SQL Server Management Studio中,右键单击“SQL Server Agent”,选择“新建作业”。填写作业的基本信息。 2. **添加步骤** 在“步骤”选项卡中,添加一个新步骤,并输入必要的T-SQL脚本。例如: ```sql -- 切换到简单恢复模式 ALTER DATABASE [数据库名称] SET RECOVERY SIMPLE; GO -- 收缩日志文件 DBCC SHRINKFILE (N'数据库_log', 10); GO -- 恢复完整恢复模式 ALTER DATABASE [数据库名称] SET RECOVERY FULL; GO ``` 3. **设置调度计划** 在“调度”选项卡中,定义作业运行的时间间隔。例如,可以选择每天凌晨2点执行一次。 #### 最佳实践 1. **避免频繁收缩日志文件** 频繁收缩日志文件可能导致文件碎片化,从而影响性能。因此,应根据实际情况合理设置收缩频率[^1]。 2. **定期备份事务日志** 对于处于完整恢复模式的数据库,定期备份事务日志是防止日志文件无限增长的最佳方式。建议结合业务需求制定合理的备份策略[^3]。 3. **监控日志文件增长** 使用系统视图或第三方工具监控日志文件的增长情况。例如,可以通过以下查询获取日志文件的使用情况: ```sql SELECT name, size * 8 / 1024 AS SizeInMB, CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) * 8 / 1024 AS UsedSizeInMB FROM sys.master_files WHERE database_id = DB_ID('数据库名称') AND type_desc = 'LOG'; ``` 4. **调整初始大小和自动增长设置** 合理设置日志文件的初始大小和自动增长参数,以减少频繁的扩展操作。例如,将自动增长设置为固定的50MB或百分比值。 ```sql ALTER DATABASE [数据库名称] MODIFY FILE (NAME = N'数据库_log', SIZE = 100MB, MAXSIZE = UNLIMITED, FILEGROWTH = 50MB); ``` ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值