近期，因磁盘空间问题，当执行常规性维护如碎片整理等后会报磁盘空间不足警告，因条件又无法及时添加磁盘空间，这时候不得不对LOG文件做收缩，如果遇到库比较多的实例上，一个一个来却是比较麻烦，可通过如下SQL来处理：
---定义变量
DECLARE @DBName AS NVARCHAR(100)
DECLARE @LogFileName AS NVARCHAR(100)
DECLARE @exec_stmt nvarchar(625)
--创建临时表来保存日志文件名
CREATE TABLE #logfiles
(
dbname NVARCHAR(100), [filename] NVARCHAR(100),
)
--排除系统数据库
DECLARE curDBName CURSOR FOR
SELECT [name] FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
AND state_desc = 'ONLINE'
--逐一读取文件列表名字到游标中
OPEN curDBName
FETCH NEXT FROM curDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @exec_stmt = 'INSERT INTO #logfiles
SELECT ''' + @DBName + ''' , name FROM ' + quotename(@DBName, N'[') + N'.dbo.sysfiles
WHERE groupid = 0'
EXECUTE (@exec_stmt)
FETCH NEXT FROM curDBName INTO @DBName
END
CLOSE curDBName
DEALLOCATE curDBName
--选择所有log文件名
DECLARE curLogName CURSOR FOR
SELECT dbname, [filename] FROM #logfiles
--开始shrink每一个Log文件
OPEN curLogName
FETCH NEXT FROM curLogName INTO @DBName, @LogFileName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @exec_stmt = ' USE ' + quotename(@DBName, N'[') +
N' CHECKPOINT ' +
N' BACKUP LOG ' + quotename(@DBName, N'[') + ' WITH NO_LOG ' +
N' DBCC SHRINKFILE (' + quotename(@LogFileName, N'[') + N', 0, TRUNCATEONLY)'
SELECT (@exec_stmt)
--EXECUTE (@exec_stmt)
FETCH NEXT FROM curLogName INTO @DBName, @LogFileName
END
CLOSE curLogName
DEALLOCATE curLogName
--清除临时表
DROP TABLE #logfiles
sa.