压缩数据库日志文件(存储过程)

USE master
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--建立人：    高升
--建立日期：2007/05/18
--修改日期：2007/06/02
--功能目的：收缩数据库的日志文件
--参数：    要执行收缩的数据库名称，如果参数为''，则收缩所有的非系统数据库，这个参数是为了应付临时--情况的，根据特殊情况临时针对某个数据库执行可以写上这个数据库的名称
--注意：    修改了恢复模式，注意备份    
--流程说明：1.转到需要收缩的数据库
--            2.更改数据库恢复模式为简单
--            3.指定收缩大小
--            4.更改数据库恢复模式为完整
CREATE PROCEDURE [dbo].[DB_Shrink_Log]
  @dbName varchar(50)                --要执行收缩的数据库名称
AS
DECLARE @exec_shrink varchar(500)    --存储所有的执行语句
DECLARE @sno int                    --执行的数据库临时编号
DECLARE @old_size decimal(18,2)        --收缩前ldf文件大小
DECLARE @new_size decimal(18,2)        --收缩后ldf文件大小
DECLARE @mdf_size decimal(18,2)        --数据库mdf文件的大小
DECLARE @shrink_DB table(            --存储待执行的数据库名称
            sno int identity(1,1),name sysname)

BEGIN try

if (@DBName = '')    --记录需要收缩的数据库名称
  INSERT @shrink_DB SELECT name FROM sys.databases WHERE database_id > 4
else
  INSERT @shrink_DB SELECT name FROM sys.databases WHERE name = @DBName

--开始循环收缩数据库Log文件
SET @sno = 1
while(@sno <= (SELECT COUNT(sno) FROM @shrink_DB))
BEGIN 
  SELECT @dbName = name FROM @shrink_DB WHERE sno = @sno

  --取出数据库mdf和ldf文件的大小
  SELECT @mdf_size = size * 8 / 1024 FROM sys.master_files WHERE database_id = DB_ID(@DBName) and file_id = 1
  SELECT @old_size = size * 8 / 1024 FROM sys.master_files WHERE database_id = DB_ID(@DBName) and file_id = 2

--如果数据库的ldg文件大于mdf/10文件则收缩，否则不需要收缩
if (@old_size > @mdf_size/10) 
BEGIN
  --1.将数据库上下文更改为需要收缩的数据库
  SET @exec_shrink = 'USE ' + @dbName + ';' + char(13)

  --2.将恢复模式改为'简单'
  set @exec_shrink = @exec_shrink +
    'ALTER DATABASE ' + @dbName +
    ' SET RECOVERY SIMPLE;' + char(13)

  --3.取当前数据库所有的ldf文件，文件将压缩到数据库mdf文件大小的1/10
  DECLARE @shrink varchar(200)
  SET @shrink = ''
  SELECT @shrink = @shrink + 'DBCC SHRINKFILE('+rtrim(file_id)+','+rtrim(Ceiling(@mdf_size/10))+');' +char(13) FROM sys.database_files WHERE type = 1
  set @exec_shrink = @exec_shrink + @shrink

  --如果数据库只有一个ldf文件则可以写简单点,ldf文件的file_id通常为2
  --SET @exec_shrink = @exec_shrink + 'DBCC SHRINKFILE(2,' + rtrim(Ceiling(@mdf_size/10)) + ');'+ char(13)

  --4.将恢复模式改为'完整'
  set @exec_shrink = @exec_shrink +
    'ALTER DATABASE ' + @dbName +
    ' SET RECOVERY FULL;'

  exec (@exec_shrink)
  --print @exec_shrink

  SELECT @new_size = size * 8 / 1024 FROM sys.master_files WHERE database_id = DB_ID(@DBName) and file_id = 2
  print (@dbName +'收缩完成,压缩前 ' +rtrim(@old_size) + ' MB,收缩后 '+ rtrim(@new_size) + ' MB')
END
else
BEGIN
  print(@dbName + '暂时不需要收缩。')
END

SET @sno = @sno + 1
END    --while
END try
BEGIN catch
  print ERROR_MESSAGE()
END catch
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

 

转载与CSDN，地址：http://blog.csdn.net/hb_gx/article/details/1616550