To run this script automatically, create a SQL JOB and place this script in the STEP. That's all.
See Also: SHRINK DATABASE, Check Job Status, Grant Access to SQL Agent, View Job history.
|
USE master;
GO
DECLARE @LOG_SIZE_LIMIT int = 10240 -- 10GB
declare
DBName cursor for
select a.DATABASE_NAME,mf. name AS LogicalFileName ,LOG_SIZE_MB
from(
SELECT DATABASE_NAME = DB_NAME(s.database_id)
, LOG_SIZE_MB = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(10,2))
, ROW_SIZE_MB = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(10,2))
, TOTAL_SIZE_MB = CAST(SUM(size) * 8. / 1024 AS DECIMAL(10,2))
FROM sys.databases s
JOIN sys.master_files m
ON s.database_id = m.database_id
where s.database_id > 4 and s.state_desc= 'ONLINE' and s.recovery_model_desc = 'SIMPLE'
GROUP BY s.database_id,s.name
) a
inner join sys.master_files AS mf on DB_NAME(mf.database_id) = a.DATABASE_NAME and mf.type_desc = 'LOG'
where LOG_SIZE_MB > @LOG_SIZE_LIMIT
ORDER BY LOG_SIZE_MB desc
declare @DBNAme varchar(1000), @LogicalFileName varchar(1000), @LOG_SIZE_MB int,
@SQLExec varchar(4000)
open DBName
fetch next from DBName
into @DBNAme,@LogicalFileName,@LOG_SIZE_MB
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQLExec= 'use '+@DBNAme+'; DBCC shrinkfile ('+@LogicalFileName+')'
print @SQLExec
execute (@SQLExec)
fetch next from DBName
into @DBNAme,@LogicalFileName,@LOG_SIZE_MB
END
close DBName
deallocate DBName
|
Thanks.
