When a critical SQL Server job fails with an error indicating a missing stored procedure, it's essential to investigate:

-- Example error message you might encounter
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'usp_CriticalProcess'.

SQL Server maintains a default trace that records schema changes:

SELECT 
    te.name AS EventType,
    t.DatabaseName,
    t.ObjectName,
    t.StartTime,
    t.LoginName,
    t.ApplicationName,
    t.HostName
FROM 
    fn_trace_gettable(
        (SELECT REVERSE(SUBSTRING(REVERSE(path), 
        CHARINDEX('\', REVERSE(path)), 256)) + 'log.trc'
        FROM sys.traces WHERE is_default = 1), DEFAULT) t
JOIN 
    sys.trace_events te ON t.EventClass = te.trace_event_id
WHERE 
    t.EventClass = 47 -- Object:Deleted
    AND t.ObjectType = 8272 -- Stored Procedure
    AND t.ObjectName = 'usp_CriticalProcess'
ORDER BY 
    t.StartTime DESC;

If server audit is configured, check the audit logs:

SELECT 
    event_time,
    server_principal_name,
    object_name,
    statement
FROM 
    sys.fn_get_audit_file('C:\Audits\*.sqlaudit', DEFAULT, DEFAULT)
WHERE 
    action_id = 'DRP' -- DROP action
    AND object_name = 'usp_CriticalProcess'
ORDER BY 
    event_time DESC;

If CDC is enabled on the database:

-- Check if CDC is enabled on the database
SELECT name, is_cdc_enabled FROM sys.databases WHERE name = DB_NAME();

-- Query CDC capture instance for schema changes
SELECT 
    __$operation,
    __$update_mask,
    __$command_id,
    __$start_lsn,
    __$seqval,
    *
FROM 
    cdc.ddl_history
WHERE 
    object_name = 'usp_CriticalProcess'
ORDER BY 
    __$start_lsn DESC;

Implement these solutions to avoid similar issues:

-- 1. Create a database trigger to log schema changes
CREATE TRIGGER tr_AuditSchemaChanges
ON DATABASE
FOR DROP_PROCEDURE, ALTER_PROCEDURE
AS
BEGIN
    INSERT INTO SchemaChangeLog
    (ChangeType, ObjectName, UserName, ChangeDate)
    VALUES
    (EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'),
     EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(255)'),
     EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(255)'),
     GETDATE())
END;

Consider implementing source control for database objects to track all changes systematically.

When a scheduled job fails with errors indicating a missing stored procedure, database administrators need to quickly determine:

• Exactly when the procedure was deleted
• Which user account performed the deletion

The most reliable approaches for tracking schema changes in SQL Server:

1. Querying the Default Trace

SQL Server maintains a rolling trace that captures schema changes:

SELECT 
    te.name AS EventName,
    t.DatabaseName,
    t.StartTime,
    t.LoginName,
    t.ApplicationName,
    t.HostName,
    t.TextData
FROM sys.fn_trace_gettable(
    CONVERT(VARCHAR(150), 
        (SELECT TOP 1 f.[value] 
         FROM sys.fn_trace_getinfo(NULL) f 
         WHERE f.property = 2)), DEFAULT) t
JOIN sys.trace_events te ON t.EventClass = te.trace_event_id
WHERE te.name IN ('Object:Deleted', 'Object:Altered')
AND t.TextData LIKE '%YourProcedureName%'
ORDER BY t.StartTime DESC;

2. Using SQL Server Audit Feature

If database auditing is configured (recommended):

SELECT 
    event_time,
    server_principal_name,
    object_name,
    statement
FROM sys.fn_get_audit_file('C:\Audits\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'DR' -- Delete object event
AND object_name = 'YourProcedureName'
ORDER BY event_time DESC;

3. Checking SQL Server Logs

For instances without auditing configured:

EXEC sp_readerrorlog 0, 1, 'DROP PROCEDURE', 'YourProcedureName';

Implement these best practices to avoid future issues:

-- Create database audit specification
USE [YourDatabase];
GO
CREATE DATABASE AUDIT SPECIFICATION [SchemaChanges]
FOR SERVER AUDIT [ServerAudit]
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP);
GO

-- Enable the audit
ALTER SERVER AUDIT [ServerAudit] WITH (STATE = ON);
GO

Create a logging table and trigger:

CREATE TABLE dbo.SchemaChangeLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    EventTime DATETIME DEFAULT GETDATE(),
    LoginName NVARCHAR(128),
    HostName NVARCHAR(128),
    EventType NVARCHAR(128),
    ObjectName NVARCHAR(128),
    TSQLCommand NVARCHAR(MAX)
);

CREATE TRIGGER tr_AuditStoredProcedures
ON DATABASE
FOR DROP_PROCEDURE, ALTER_PROCEDURE
AS
BEGIN
    INSERT INTO dbo.SchemaChangeLog (
        LoginName,
        HostName,
        EventType,
        ObjectName,
        TSQLCommand
    )
    SELECT
        ORIGINAL_LOGIN(),
        HOST_NAME(),
        EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(128)'),
        EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)'),
        EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)')
END;

• Check the SQL Agent job history for the exact failure time
• Cross-reference with your source control system if the procedure exists in version history
• Review backup schedules - a database restore might have overwritten the procedure