Object reference not set to an instance of an object.

Hi Greg,

This happens behind of "scenes" with the database. I just I am feeding a table to master.

USE [msdb]
GO

/****** Object: Job [CaptureOILatency] Script Date: 8/09/2020 4:18:43 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 8/09/2020 4:18:43 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job Anonymous_name=N'CaptureOILatency',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'DOMAIN\JairoSuarez', Anonymous_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [CaptureIOLatency] Script Date: 8/09/2020 4:18:43 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep Anonymous_id=@jobId, @step_name=N'CaptureIOLatency',
@step_id=1,
@cmdexec_success_code=0,
on_success_action=1,
on_success_step_id=0,
on_fail_action=2,
on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
Anonymous_run_priority=0, @subsystem=N'TSQL',
@command=N'DECLARE @CaptureID INT;

SELECT @CaptureID = MAX(CaptureID) FROM [master].[dbo].[IOStatsLatency];

PRINT (@CaptureID);

IF @CaptureID IS NULL
BEGIN
SET @CaptureID = 1;
END
ELSE
BEGIN
SET @CaptureID = @CaptureID + 1;
END

--
INSERT INTO master.dbo.IOStatsLatency
( CaptureID,
CaptureDate,
ReadLatency,
WriteLatency,
Latency,
AvgBPerRead,
AvgBPerWrite,
AvgBPerTransfer,
Drive,
DB,
physical_name
)
SELECT Top 100 @CaptureID, getdate(),
[ReadLatency] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[Latency] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
[AvgBPerRead] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
[AvgBPerTransfer] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE
(([num_of_bytes_read] + [num_of_bytes_written]) /
([num_of_reads] + [num_of_writes])) END,
LEFT ([mf].[physical_name], 2) AS [Drive],
DB_NAME ([vfs].[database_id]) AS [DB],
[mf].[physical_name]
FROM
sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
ORDER BY [WriteLatency] ASC;',
@database_name=N'msdb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job Anonymous_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule Anonymous_id=@jobId, name=N'CaptureIOLatency_Schedule',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20200826,
@active_end_date=20200831,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'1bf17617-4608-4283-9cd4-419ed87c6094'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver Anonymous_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO