Check Log Health

--Log File is a very important property of MSSQL SERVER
--This scripts checks log health very dynamically
--Using Log_Reuse_Wait & DBCC SQLPERF

--Author : Mustak Ahammad Prince
--Email: mustak.prince@gmail.com

IF OBJECT_ID('tempdb.dbo.#TmpLOGSPACE', 'U') IS NOT NULL
  DROP TABLE #TmpLOGSPACE;
IF OBJECT_ID('tempdb.dbo.#TmpLOG', 'U') IS NOT NULL
  DROP TABLE #TmpLOG;
create table #TmpLOGSPACE(
  DatabaseName varchar(100),
  LOGSIZE_MB decimal(18, 9),
  LOGSPACE_USED decimal(18, 9),
  LOGSTATUS decimal(18, 9))

create table #TmpLOG(
  DatabaseName varchar(100),
  LOG_REUSE varchar(30),
  LOGSIZE_MB decimal(18, 9),
  LOGSPACE_USED decimal(18, 9),
  LOGSTATUS decimal(18, 9))


insert #TmpLOGSPACE(DatabaseName, LOGSIZE_MB, LOGSPACE_USED, LOGSTATUS)
EXEC('DBCC SQLPERF(LOGSPACE);')

insert #TmpLOG(DatabaseName, LOG_REUSE, LOGSIZE_MB, LOGSPACE_USED)
(select a.name, a.log_reuse_wait_desc, b.LOGSIZE_MB, b.LOGSPACE_USED
from sys.databases a
inner join #TmpLOGSPACE b
on a.name = b.DatabaseName
where a.name not in ('master','msdb','model','tempdb'))

DROP TABLE #TmpLOGSPACE;
SELECT * FROM #TmpLOG
ORDER BY 2, 4 DESC;
DROP TABLE #TmpLOG