分享一个收缩数据库日志文件的存储过程

实际工作中，经常数据库日志文件非常大，但是实际使用的容量又非常小，具体可以通过下面的sql看到日志文件大小，和文件使用率：

SELECT name, data_space_id 文件组id, size/128 [文件大小(兆)],

    FILEPROPERTY(name, 'SpaceUsed')/128 [已用空间(兆)],

    size/128 - FILEPROPERTY(name, 'SpaceUsed')/128 [未用空间(兆)],

    FILEPROPERTY(name, 'SpaceUsed')*100.0/size [使用率(%)],

    max_size/128 [最大值(兆)],

    case is_percent_growth when 0 then cast(growth/128 as nvarchar) + '兆' else cast(growth as nvarchar) + '%' end 增长值,

    physical_name 物理路径

FROM sys.database_files a ORDER BY a.[name]

于是写了一个存储过程，可以快速回收日志文件占用的磁盘空间，具体调用就是Exec UP_ShinkLog '数据库名'：

CREATE PROCEDURE [UP_ShinkLog]

    @dbName VARCHAR(100)

AS

BEGIN

SET NOCOUNT ON;

 

    DECLARE @sql NVARCHAR(200);

    DECLARE @logname NVARCHAR(200);

    DECLARE @dbRecovery TINYINT;

 

    -- 获取日志文件名

    SET @sql = N'USE [' + @dbName + N'];' + Char(13) + Char(10) +

    --'GO' + Char(13) + Char(10) +

    'select top 1 @logname=name from sys.database_files where type=1';

    --PRINT @sql

    EXECUTE sp_executesql @sql, N'@logname NVARCHAR(200) output', @logname output;

    IF @logname IS NULL

    BEGIN

        PRINT '未找到日志文件：' + @sql;

        RETURN;

    END 

    

    -- 获取现有的恢复模式 1是FULL 3是SIMPLE

    SET @sql = N'USE [' + @dbName + N'];' + Char(13) + Char(10) +

    --'GO' + Char(13) + Char(10) +

    'select top 1 @dbRecovery=recovery_model FROM sys.databases WHERE name = ''' + @dbName + '''';

    --PRINT @sql

    EXECUTE sp_executesql @sql, N'@dbRecovery TINYINT output', @dbRecovery output;

   

    -- 开始收缩日志

    -- 1、设置模式为简单模式

    SET @sql = N'ALTER DATABASE [' + @dbName + N'] SET RECOVERY SIMPLE WITH NO_WAIT';

    EXECUTE sp_executesql @sql;

    SET @sql = N'ALTER DATABASE [' + @dbName + N'] SET RECOVERY SIMPLE';

    EXECUTE sp_executesql @sql;

 

    -- 2、收缩文件

    SET @sql = N'USE [' + @dbName + N'];' + Char(13) + Char(10) +

        --'GO' + Char(13) + Char(10) +

        'DBCC SHRINKFILE (N''' + @logname + ''', 1, TRUNCATEONLY)';

    EXECUTE sp_executesql @sql;

 

    -- 3、恢复原来的模式

    IF @dbRecovery = 1

    BEGIN

        SET @sql = N'ALTER DATABASE [' + @dbName + N'] SET RECOVERY FULL WITH NO_WAIT';

        EXECUTE sp_executesql @sql;

        SET @sql = N'ALTER DATABASE [' + @dbName + N'] SET RECOVERY FULL';

        EXECUTE sp_executesql @sql;

    END  

 

    PRINT '数据库：' + @dbName + ' 日志文件：' + @logname + ' 收缩完成';

END

 

---

首发：http://beinet.cn