Log size monitor

--SQL JOB Step. Run every 15 mins

INSERT INTO CrouseScripts.dbo.LogMonitor

SELECT DB_NAME(database_id) AS DatabaseName,

Name AS Logical_Name,

Physical_Name, (size*8)/1024 SizeMB,state_desc,GETDATE()

FROM sys.master_files

--where physical_name like '%ldf%'

--WHERE DB_NAME(database_id) = 'nSightDB'

--DB table definition

USE [CrouseScripts]

GO

/****** Object:  Table [dbo].[LogMonitor]    Script Date: 3/6/2020 9:33:28 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[LogMonitor](

       [DatabaseName] [nvarchar](128) NULL,

       [Logical_Name] [sysname] NOT NULL,

       [Physical_Name] [nvarchar](260) NOT NULL,

       [SizeMB] [int] NULL,

       [state_desc] [nvarchar](60) NULL,

       [dDateTime] [datetime] NULL

) ON [PRIMARY]

GO

Pivot to read Log size over days of week

use CrouseScripts

SELECT *

FROM (

    SELECT

        'APF_OLTP' as LogDB,DATEPART( wk, convert(nvarchar(max),[dDateTime],101)) as WeekNum,  convert(nvarchar(max),[dDateTime],101) AS [TheDate],left(datename(HOUR,[dDateTime]),3)as [Day], --left(datename(DAY,[dDateTime]),3)as [Day]

        [SizeMB] as Amount

    FROM LogMonitor

       where logical_name like '%oltp_log%' and DatabaseName not in ('master','tempdb','model','msdb')

) as s

PIVOT

(

    MAX([Amount])

       FOR [Day] IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])--,[25],[26],[27],[28],[29],[30],[31])

)AS pvt