最近数据库日志文件增加太快，其中一个数据库的日志文件在短短一个星期居然从600多MB增加到40多G，为了偷懒所以写了这个存储过程压缩日志文件，不然30多个数据库每天光点收缩就要10几分钟，想想还是很值得的。
数据库有个选项叫自动收缩，说的很好听，可是我们的数据库恢复模式都是完整，在完整模式下数据库的收缩并不会释放太多的空间，而且我也不知道自动收缩是什么时候收缩，baidu和google上也没人给个准确说明，所以就自己写了，这也是种学习的方法。
本存储过程只压缩日志文件，用 SQL Server Agent 执行，自动压缩所有数据库，当然为了有时候临时执行一下所以加了个参数，绝对偷懒的方法！以后也不用去担心日志文件占用太多的磁盘空间了。 其实这个存储过程就执行了几句代码，写的这么麻烦是因为让他自动执行后需要看看执行的情况，如果不打印那些信息根本用不了那么麻烦，虽然候麻烦一次却可以轻松很久，这就是本人做事的原则。
本来5月份写了一个，让它每个星期自动执行1次，结果现在几个数据库的日志文件都压缩到了不能再缩小，虽然占用的空间是减少了很多可是总感觉这样不是很好，当时写的时候很多东西都不是很了解，经过半个月的时间自己的理解又深了一步，所以重写了一个。因为日志文件如果太小在使用的时候会让它花比较多的效能去增大，而如果太大了则会浪费掉磁盘空间，那么日志文件多大比较适合呢，我个人觉得应该根据每段时间增长的数据量和硬件的配置来考虑。
这是我在网上搜索到的一部分，主要从这几个方面考虑的：
1、每段时间的业务量所产生的数据量，比如一天
2、系统对性能的要求
3、系统（客户）对数据丢失的承受能力（不能丢任何数据，可以接受一个小时...）
4、对恢复的时间要求
5、储存设备的吞吐能力
6、实际存在可以用来archive 的设备
总的来说都是需要根据各自的实际需求和具体的使用情况来决定，我现在的日志文件大小是根据数据库的mdf文件大小来决定，保留日志文件的大小为mdf文件的1/10。存储过程如下，已经测试过了，还是很好用的，建议放在master数据库下，如果以后发现问题会随时修改的。在压缩前没有截断事务日志是因为没有必要，每天备份的时候都截断过，而且我这里改变了数据库的恢复模式，也不需要截断了。
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
严重声明：由于切换了恢复模式，所以在压缩完成后如果先备份事务日志会报错，必须先完整或者差异备份一次才能备份事务日志，切记切记！！！
最后如果有人不小心看完了请给点建议，谢谢了！