SQL Server数据库日志文件收缩的实现示例

1. 查看日志文件大小

-- 查看所有数据库日志文件信息
SELECT 
    DB_NAME(database_id) AS [Database Name],
    name AS [Logical Name],
    type_desc AS [File Type],
    size/128.0 AS [Size in MB],
    CAST(size/128.0 AS DECIMAL(10,2)) - CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Free Space in MB],
    (CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS [Used %]
FROM sys.master_files
WHERE type = 1;  -- 1表示日志文件，0表示数据文件

2. 检查日志使用情况

-- 查看特定数据库的日志使用情况
DBCC SQLPERF(LOGSPACE);

-- 查看日志空间详细信息
DBCC LOGINFO('YourDatabaseName');

3. 简单收缩方法

-- 方法1：直接收缩日志文件
USE YourDatabaseName;
DBCC SHRINKFILE (YourLogFileName, 100);  -- 收缩到100MB

4. 完整收缩流程（推荐）

-- 步骤1：切换恢复模式（如果需要）
ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE;
-- 注意：生产环境慎用，会破坏日志链

-- 步骤2：执行检查点（强制将脏页写入磁盘）
CHECKPOINT;

-- 步骤3：备份日志（如果是FULL/BULK_LOGGED模式）
BACKUP LOG YourDatabaseName TO DISK = 'D:\Backup\LogBackup.trn';

-- 步骤4：收缩日志文件
USE YourDatabaseName;
DBCC SHRINKFILE (YourLogFileName, 100);  -- 100MB为目标大小

-- 步骤5：恢复原恢复模式（如果修改过）
ALTER DATABASE YourDatabaseName SET RECOVERY FULL;

5. 自动化脚本

-- 自动化收缩过程
DECLARE @DatabaseName NVARCHAR(128) = 'YourDatabaseName';
DECLARE @LogFileName NVARCHAR(128);
DECLARE @TargetSizeMB INT = 100;  -- 目标大小

-- 获取日志文件名
SELECT @LogFileName = name 
FROM sys.master_files 
WHERE database_id = DB_ID(@DatabaseName) AND type = 1;

-- 生成收缩命令
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = '
USE [' + @DatabaseName + '];
ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY SIMPLE;
CHECKPOINT;
DBCC SHRINKFILE (''' + @LogFileName + ''', ' + CAST(@TargetSizeMB AS NVARCHAR) + ');
ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY FULL;
';

PRINT @SQL;
-- EXEC sp_executesql @SQL;

6. 定期维护脚本（使用游标处理所有数据库）

DECLARE @DBName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);

DECLARE db_cursor CURSOR FOR
SELECT name 
FROM sys.databases 
WHERE state = 0  -- 在线数据库
    AND name NOT IN ('master', 'model', 'msdb', 'tempdb')  -- 排除系统数据库
    AND recovery_model_desc = 'SIMPLE';  -- 只处理简单恢复模式

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'USE [' + @DBName + '];
    DECLARE @LogFile NVARCHAR(128);
    SELECT @LogFile = name FROM sys.database_files WHERE type = 1;

    DBCC SHRINKFILE (@LogFile, 100);
    PRINT ''收缩完成: ' + @DBName + '''';

    EXEC sp_executesql @SQL;

    FETCH NEXT FROM db_cursor INTO @DBName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

7. 通过PowerShell实现

# PowerShell脚本收缩日志文件
Import-Module SQLPS -DisableNameChecking

$ServerInstance = "YourServer\Instance"
$DatabaseName = "YourDatabase"

$SQL = @"
USE [$DatabaseName];
DBCC SHRINKFILE (N'YourLogFileName', 100);
"@

Invoke-Sqlcmd -ServerInstance $ServerInstance -Database "master" -Query $SQL

8. 注意事项和最佳实践

重要提示：

• SIMPLE模式：日志会自动重用
• FULL模式：需要定期备份日志才能重用空间

-- 设置日志文件自动增长
ALTER DATABASE YourDatabaseName
MODIFY FILE (NAME = YourLogFileName, 
          MAXSIZE = 1024MB,  -- 最大限制
          FILEGROWTH = 64MB);  -- 每次增长大小

推荐做法：

定期维护：

-- 创建维护计划
USE msdb;
EXEC sp_add_job @job_name = 'Log Maintenance';
-- 添加备份和收缩步骤

预防为主：

• 设置合理的初始大小和增长设置
• 监控长时间运行的事务
• 定期清理历史数据

紧急情况处理：

-- 如果日志文件已满，可以尝试
USE master;
ALTER DATABASE YourDatabaseName SET EMERGENCY;
ALTER DATABASE YourDatabaseName SET SINGLE_USER;
DBCC CHECKDB (YourDatabaseName, REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE YourDatabaseName SET MULTI_USER;

常见问题解决：

-- 如果收缩无效，检查是否有活动事务
DBCC OPENTRAN('YourDatabaseName');

-- 查看当前事务
SELECT 
    session_id,
    transaction_id,
    name AS TransactionName,
    transaction_begin_time,
    transaction_type,
    transaction_state
FROM sys.dm_tran_active_transactions
WHERE database_id = DB_ID('YourDatabaseName');

选择合适的方法根据你的具体场景，生产环境建议先测试再执行。