Hi,

i understand all your replies and reasons not to use this script on a regular base.

But if you know what you're doing in a case of an "incident" then this script is really helpful...

Just to perform a shrink on all logfiles of all databases in an instance to "clean up" disk just for one time... (instead of building a new script for each database or using the gui...)

I've found something strange in the script it can't handle database names which contains - (minus)

Therefor i've added some ' to the @Filenname

'DBCC SHRINKFILE('''+@FileName+''',1)'

instead of

'DBCC SHRINKFILE('+@FileName+',1)'

so whole script must be...

DECLARE @tbl TABLE

(

ID INT IDENTITY(1,1),

DBNAME NVARCHAR(1000),

[FileName] NVARCHAR(1000)

)

INSERT INTO @tbl

SELECT DB_NAME(DbID),st.NAme from sys.sysaltfiles ST INNER JOIN Sys.databases SB on ST.dbid = sb.database_id where Size > 100000 AND FileName LIKE '%ldf' and sb.state = 0

DECLARE @MinID INT , @MaxID INT,@DBName NVARCHAR(1000),@FileName NVARCHAR(1000),@RecoveryModel NVARCHAR(1000),@SQL NVARCHAR(MAX)

SELECT @MinID = MIN(ID),@MaxID = MAX(ID) FROM @tbl

WHILE(@MinID <=@MaxID)

BEGIN

SELECT @DBName = DBNAME,@FileName=[FileName] FROM @tbl where ID = @MinID

SELECT@RecoveryModel = recovery_model_desc FROM sys.databases where name = @DBName

SELECT@SQL= N'USE ['+ @DBName+']'+CHAR(10)+CHAR(10)

+CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT'ELSE N''END+CHAR(10)+

+N'DBCC SHRINKFILE('''+@FileName+''',1)'+CHAR(10)

+CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY '+@RecoveryModel+N' WITH NO_WAIT'+CHAR(10) ELSE N'' END

+CHAR(10)

PRINT @SQL

EXEC SP_EXECUTESQL @SQL

SELECT @MinID = @MinID +1

END