前言

SQLserver有的时候产生大量临时数据，存放在log日志文件中，没有及时收缩，占用了大量磁盘空间，严重情况下，会导致数据库实例崩溃，此时可以尝试手动或自动收缩数据库日志文件，一种方法是使用Microsoft SQL Server Management Studio登录SQLserver服务在UI界面操作收缩，另一种方法是编写sql存储过程，只需手动执行一下SQL就能实现收缩，或者可以创建SQLserver作业的方式定时收缩

今天就用第二种方式来解决这个问题，以下方法已在SQLserver 2008及以上版本获得验证

创建存储过程SP_Shrink_LogFile

CREATE  PROC SP_Shrink_LogFile(@dbname VARCHAR(200)) 

AS 

BEGIN

DECLARE @sql VARCHAR(4000),@logfilename VARCHAR(200)

--获取日志逻辑名称

SET @logfilename =(

select b.name from sys.databases a 

inner join sys.master_files b on b.database_id=a.database_id AND b.type_desc='LOG'

WHERE a.name=@dbname) 

IF(@logfilename IS NULL)

BEGIN 

PRINT '数据库名'''+@dbname+'''不存在！'

RETURN

END 

--在SQL2008中清除日志就必须在简单模式下进行，等清除动作完毕再调回到完全模式。

SET @sql='

USE MASTER;

ALTER DATABASE '+@dbname+' SET RECOVERY SIMPLE WITH NO_WAIT;

ALTER DATABASE '+@dbname+' SET RECOVERY SIMPLE;--修改为简单模式

USE '+@dbname+';

DBCC SHRINKFILE (N'''+@logfilename+''' , 11, TRUNCATEONLY); --收缩日志

USE MASTER;

ALTER DATABASE '+@dbname+' SET RECOVERY FULL WITH NO_WAIT;

ALTER DATABASE '+@dbname+' SET RECOVERY FULL ;--还原为完全模式

'

EXEC (@sql);

END

调用方法

方式1：直接在SQLserver中手动执行

--入参出参描述：@dbname  数据库名

exec SP_Shrink_LogFile @dbname='TestDb'

方式2：创建SQLserver 作业定时执行，方法简单，此地省略