SQL SAFELY Shink all possible log files

--// SAFELY Shink all possible log files
--// RETURN RESULT TO TEXT. REVIEW AND EXECUTE
--// Adjust L.LogUsed AND L.LogSize as needed
SET NOCOUNT ON
If Object_Id('tempdb..#tmpLogInfo') is Not Null
Drop table #tmpLogInfo

CREATE TABLE #tmpLogInfo
(
databaseName sysname
, logSize decimal(18,5)
, logUsed decimal(18,5)
, status INT
)

INSERT INTO #tmpLogInfo
exec('dbcc sqlperf(logspace)')

SELECT
'--// ' + convert(nvarchar,L.databaseName)
+ ' LogSize: ' + convert(nvarchar,L.LogSize)
+ ' %LogUsed: ' + convert(nvarchar,L.LogUsed) + '
USE [' + convert(nvarchar,L.databaseName) + ']
' + (CASE WHEN recovery_model_desc <> 'SIMPLE' THEN '--// RECOVERY MODEL NOT SET TO SIMPLE: ' + L.databaseName + '
--// ALTER DATABASE ' + L.databaseName + ' SET RECOVERY SIMPLE

' ELSE 'DBCC SHRINKFILE (' + ms.name + ', 1)
GO' END)
FROM
#tmpLogInfo L
JOIN sys.databases db ON db.name = L.DatabaseName
JOIN master.sys.sysaltfiles ms ON ms.dbid = DB_ID(L.databaseName)
WHERE
L.databaseName not in ('master','tempdb','model','msdb')
AND ms.Status = 1048642
AND Right(ms.filename,4) = '.ldf'
AND L.LogUsed < 10

Drop table #tmpLogInfo

Comments :

0 comments to “SQL SAFELY Shink all possible log files”