--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
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