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