近期，因磁盘空间问题，当执行常规性维护如碎片整理等后会报磁盘空间不足警告，因条件又无法及时添加磁盘空间，这时候不得不对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.