SQL: SAFELY Shrink all possible log files

--NOTE: I commented out the exec, it will just print until you change it yourself....
--// SAFELY Shink all possible log files
--// 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)')

DECLARE @sqlcmd nvarChar(max)
Select @sqlcmd = REPLACE((
SELECT
'USE [' + convert(nvarchar,L.databaseName) + '];
CHECKPOINT;
' + (CASE WHEN recovery_model_desc <> 'SIMPLE' THEN '--// RECOVERY MODEL NOT SET TO SIMPLE: ' + L.databaseName + '
--// ALTER DATABASE ' + L.databaseName + ' SET RECOVERY SIMPLE
--// DBCC SHRINKFILE ([' + ms.name + '], 1);
--// ALTER DATABASE ' + L.databaseName + ' SET RECOVERY ' + recovery_model_desc + '
' ELSE 'DBCC SHRINKFILE ([' + ms.name + '], 1);
' 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 < 5
AND L.LogSize > 2
FOr XML PATH('')

),'&#x0D;',char(13))

Drop table #tmpLogInfo

Print @sqlcmd
--exec(@sqlcmd)


Comments :

0 comments to “SQL: SAFELY Shrink all possible log files”