DBCC SQLPERF(logspace) --Optional
GO
DECLARE @logsize AS INT = 0; -- MB
DECLARE @databasename AS VARCHAR(256);
DECLARE @filename VARCHAR(256);
DECLARE @cmd VARCHAR(MAX);
DECLARE curdb CURSOR FOR
SELECT f.name AS filename, d.name AS databasename
FROM msdb.sys.master_files f
INNER JOIN master.sys.sysdatabases d
ON d.dbid = f.database_id
WHERE type = 1 AND state = 0 AND size > @logsize AND database_id > 4
OPEN curdb
FETCH NEXT FROM curdb INTO @filename, @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @filename
SET @cmd = ('USE [' + @databasename + ']; ')
SET @cmd = @cmd + 'DBCC SHRINKFILE ([' + @filename + '], ' + CAST(@logsize AS VARCHAR) + ');';
EXEC (@cmd)
FETCH NEXT FROM curdb INTO @filename, @databasename
END
DEALLOCATE curdb
GO
DBCC SQLPERF(logspace) --Optional If you require to send out put in file
GO
No comments:
Post a Comment