Job Step Log Output file path

USE [DBMaint]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

---------------------------------------------------------------------------------------------------------------------

Create Proc [dbo].[usp_JOBSTEPLOG_CHECK] (@dbmail_profile SYSNAME = NULL, @dbmail_recipient SYSNAME = NULL)
as

/* Purpose: The Purpose of this script is to run against -
                          
1.It will first check the path of the JOBOUTPUT folder, If itÆs deleted/missing/renamed, it will create a fresh folder with name ôJOBOUTPUTö to create the output of each job to that folder.
2.Identifies Missing OUTPUT FILE and fixes all
3.IF any Job has wrong OUTPUT Path, identifies and fixes them
4.IF WRONG path is mentioned in the JOB OUTPUT, it will fix them
5.It will send an email alert if incase, it identifies any Missing OUTPUT file against any of the JOB steps.

---------------------------------------------------------------------------------------------------------------------
                 *** RUNNING Instructions:-

 exec usp_JOBSTEPLOG_CHECK
   @dbmail_profile= ' ', 
  @dbmail_recipient = ' ';
---------------------------------------------------------------------------------------------------------------------
  ... */
BEGIN 
SET nocount ON 
BEGIN try 
       
        DECLARE @output_file_name VARCHAR(max) -- Name of log
        DECLARE @STEP_NAME VARCHAR(MAX)        -- Step name
        DECLARE @path VARCHAR(max)             -- Path for working directory  
DECLARE @path1 VARCHAR(max)             -- Path for working directory  
        DECLARE @JOB_ID VARCHAR(max)           -- Job ID
        DECLARE @STEP_ID VARCHAR(MAX)          -- Step_ID
DECLARE @JOB_Name VARCHAR(max)           -- Job ID
        DECLARE @CMD VARCHAR(MAX)              -- EXECUTABLE COMMAND
DECLARE @CMD1 VARCHAR(4000)              -- EXECUTABLE COMMAND
        DECLARE @INSTANCE VARCHAR(MAX)         -- GRABS INSTANCE NAME TO DIFFERENTIATE REPORT NAMES        
DECLARE @path2 VARCHAR(max)   
        DECLARE @RC INT
DECLARE @RC1 INT
    DECLARE @NumRecords INT
DECLARE @Count INT
DECLARE @result VARCHAR(4000) 

set nocount on
declare @file_string varchar(4000)

-------------- Running Procedsure Details ---------------------------
declare @servername nvarchar(4000) = (Select @@servername);
DECLARE @ProcedureName SYSNAME; 
SET @ProcedureName = OBJECT_NAME(@@PROCID);
Print 'Running Procedure:= '  + @ProcedureName
-----------------------------------------------------------------------


DECLARE @DEFAULTS1 TABLE (VALUE VARCHAR(MAX), DATA VARCHAR(MAX))
    
INSERT INTO @DEFAULTS1
    EXEC @rc1 = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory'
 
          SELECT @PATH2 = DATA FROM @DEFAULTS1 WHERE VALUE = 'BackupDirectory'
          SELECT @PATH2 = @PATH2 + '\JOBOUTPUT\' 
   --PRINT @PATH2

SET @file_string = @PATH2

--SET @Path = (SELECT @file_string)

create table #xp_fileexist_output (
[FILE_EXISTS]intnot null,
[FILE_IS_DIRECTORY]intnot null,
[PARENT_DIRECTORY_EXISTS]intnot null)

insert into #xp_fileexist_output
exec master.dbo.xp_fileexist @file_string

if exists ( select * from #xp_fileexist_output where FILE_IS_DIRECTORY = 1 )
begin
print 'File is a directory = ' +quotename(@file_string)
end
else
begin
print 'File is not a directory = '+quotename(@file_string)


--checking if dir exist if not create it
SELECT @CMD1 = 'dir' + @file_string 
EXEC @result = master.dbo.xp_cmdshell @CMD1, NO_OUTPUT
IF @result  0
BEGIN
SELECT @CMD1 = 'mkdir ' + @PATH2
EXEC master.dbo.xp_cmdshell @CMD1, NO_OUTPUT

Print 'Folder is created successfully := ' +@PATH2
END
end

--select * from #xp_fileexist_output

   create table #Missing_Job_Output 
(
[JOB NAME]nvarchar(4000)null,
[output_file_name]nvarchar(4000) null,
[JOB Step Name]nvarchar(4000)null,
[DB NAME] nvarchar(4000)  null,
[last_run_outcome] nvarchar(100)  NULL,
[last_run_datetime] datetime  NULL
)

  --      SELECT
--@@servername as ServerName,
--name as JobName,
--date_modified as LastModifiedDate
--FROM
--msdb.dbo.sysjobs WITH (NOLOCK)
--WHERE
--enabled = 0



-- Count Number Of files where the OUTPUT files are MISSING...& Ignore Disabled Jobs.
SET @Count = (select COUNT(*)
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobsteps js
on j.job_id = js.job_id
WHERE js.output_file_name IS NULL and enabled = 1);

PRINT 'Number of Active & Enabled JOBS where Output files are Missing are:= ' + CAST (@Count as VARCHAR(10))
PRINT 'No DBMAIL will be Triggerred.'

IF @Count >0
BEGIN
-- Script to find the Jobs Running with NO OUTPUT File.
INSERT Into #Missing_Job_Output 
select j.name AS JOBNAME,js.output_file_name,
  js.step_name,js.database_name "Executing On which DB?",
  last_run_outcome = case when js.last_run_outcome = 0 then 'Failed'
when js.last_run_outcome = 1 then 'Succeeded'
when js.last_run_outcome = 2 then 'Retry'
when js.last_run_outcome = 3 then 'Canceled'
else 'Unknown'
   end,
  last_run_datetime = msdb.dbo.agent_datetime(
   case when js.last_run_date = 0 then NULL else js.last_run_date end,
   case when js.last_run_time = 0 then NULL else js.last_run_time end)

from msdb.dbo.sysjobs j
  inner join msdb.dbo.sysjobsteps js
   on j.job_id = js.job_id
   WHERE js.output_file_name IS NULL
  order by js.output_file_name;
        END

        -- Update All the OutPut Files with NULL Value.
--UPDATE msdb.dbo.sysjobsteps SET
  --      OUTPUT_FILE_NAME = null

        
--SELECT  @NumRecords = @@ROWCOUNT

--PRINT 'TOTAL No Of SQL Jobs In the Instance:= ' + CAST(@NumRecords as varchar(10)) +CHAR(13)
--PRINT @NumRecords

IF (SELECT Cursor_status('global', 'db_cursor')) >= -1 
                  BEGIN 
                      IF (SELECT Cursor_status('global', 'db_cursor')) > 
                         -1 
                        BEGIN 
                            CLOSE db_cursor 
                        END 

                      DEALLOCATE db_cursor 
                  END 
        
-- Doing this step because, if incase someone provides a wrong path to job or changes the job path. 
UPDATE msdb.dbo.sysjobsteps SET
        OUTPUT_FILE_NAME = null

        DECLARE db_cursor CURSOR FOR  
        SELECT STEP_NAME, JOB_ID, STEP_ID, STEP_NAME --, output_file_name
        FROM msdb.dbo.sysjobsteps
        where output_file_name is null 
        
        OPEN db_cursor   
        FETCH NEXT FROM db_cursor INTO @STEP_NAME, @JOB_ID, @STEP_ID, @STEP_NAME

          SELECT 'STEPNAME' = @STEP_NAME,
                 'JOBID' =    @JOB_ID,
 'JOBNAME' =    @JOB_NAME,
                 'STEPID' =   @STEP_ID,
                 'STEPNAME' = @STEP_NAME,
                 'PATH' =     @Path,
                 'OUTPUT' =   @OUTPUT_FILE_NAME,
                 'INSTANCE' = @INSTANCE
        
   WHILE @@FETCH_STATUS = 0   
        BEGIN   
        
       /* DECLARE @DEFAULTS TABLE (VALUE VARCHAR(MAX), DATA VARCHAR(MAX))
        
        --SELECT @PATH = @SOURCEPTH
          INSERT INTO @DEFAULTS
          EXEC @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory'
         
 -- Print the Job Stack
     --SELECT * from @DEFAULTS

 
          SELECT @PATH = DATA FROM @DEFAULTS WHERE VALUE = 'BackupDirectory'
          SELECT @PATH = @PATH + '\JOBOUTPUT\' 
*/
--SELECT @PATH2

          SET @INSTANCE = (SELECT @@Servername)
        
          SET @STEP_NAME = @STEP_NAME + '.TXT'
        
         -- SELECT @STEP_NAME AS STEPNAME  --, @JOB_ID, @STEP_ID, @STEP_NAME
        
          SET @OUTPUT_FILE_NAME = @PATH2 + @STEP_NAME
         
 -- SELECT @OUTPUT_FILE_NAME       

  /*
 SELECT 'STEPNAME' = @STEP_NAME,
                 'JOBID' =    @JOB_ID,
                 'STEPID' =   @STEP_ID,
                 'STEPNAME' = @STEP_NAME,
                 'PATH' =     @Path,
                 'OUTPUT' =   @OUTPUT_FILE_NAME,
                 'INSTANCE' = @INSTANCE
               */
          SET @CMD = 'update msdb.dbo.sysjobsteps set output_file_name = '''+@OUTPUT_FILE_NAME+ ''' where job_id = ''' + @JOB_ID+''' and step_id = ' + @STEP_ID +''       

         -- PRINT @CMD   

          EXEC(@CMD)

          SET @OUTPUT_FILE_NAME = ''
        
       FETCH NEXT FROM db_cursor INTO @STEP_NAME, @JOB_ID, @STEP_ID, @STEP_NAME  

        END   
        
        CLOSE db_cursor   
        DEALLOCATE db_cursor
--SET NOCOUNT OFF
  --select @body1


    DECLARE @body nVARCHAR(max)   
    DECLARE @subject VARCHAR(max) 
    DECLARE @profile_name VARCHAR(max) 
    DECLARE @recipients VARCHAR(max) 

-- Print @count
  IF @count > 0 
      BEGIN 
          SELECT @subject = 'CURRENTLY THERE ARE JOB(S) IN THE SERVER WHICH HAD MISSING/WRONG JOB OUTPUT FILES : ' + '  '+ Substring(@@servername, 1, 20) 

    
set @body = N'<H3> <Font Color="red"> CURRENTLY THERE ARE JOBS IN THE SERVER WHICH HAD ****  MISSING/WRONG JOB OUTPUT FILES ****  FOUND : : </font> </H3>' +  
                        N'<H4> <Font Color="Red"> Server: ' + @@servername + '</H4></font>' + ' '+ '<BR>'+

N'<table border="1">' +
N'<tr> <th>JOB NAME</th>
<th>Output File Name</th>
<th>Step Name</th>
<th>Executed On DB</th>
<th>Last Run Date & Time</th>' +
CAST ( ( SELECT td = ISNULL(CONVERT(varchar(200),[JOB NAME]), 'NULL'), '',
                td = ISNULL(CONVERT(varchar(200),[output_file_name]), 'NULL'), '',
td = ISNULL(CONVERT(varchar(200),[JOB Step Name]), 'NULL'), '',
    td = ISNULL(CONVERT(varchar(200),[DB NAME]), 'NULL'), '',
    td = ISNULL(CONVERT(nvarchar(200),[last_run_datetime],106), 'NULL'), ' '
from #Missing_Job_Output
  order by [output_file_name]
FOR XML PATH('tr'), TYPE 
) AS NVARCHAR(MAX) ) +
N'</table>' + ' ' + '<Br><H3> <Font Color="darkblue"> The Issue is fixed. Missing OutPut File Against the JOB STEP(s) Is/Are Re-Created Automatically.. ' + '</H4></font>' + ' '+ '<BR>';


select @body
 
IF (@dbmail_profile IS NOT NULL) OR (@dbmail_recipient IS NOT NULL)
         BEGIN

 -- Sending Email to Recipients. 
          EXEC msdb.dbo.Sp_send_dbmail 
            @profile_name = @dbmail_profile, 
            @recipients = @dbmail_recipient, 
            @subject = @subject, 
            @body = @body,
@body_format = 'HTML',
@importance = 'HIGH'
         END
END

     /* Test Queued Email. ... 
       
        Query 1 : SELECT [profile_id]  
           ,[name]   
           ,[description]  
           ,[last_mod_datetime]  
           ,[last_mod_user]  
           ,'EXEC msdb.dbo.sp_send_dbmail  
        @profile_name = ''' + name + ''',  
        @recipients = ''  '',   
        @subject = ''Test'',  
        @body  = ''Message'',  
          @body_format = ''HTML'';' AS TestSQL  
          FROM [msdb].[dbo].[sysmail_profile]  
       
       
         Query 2:   EXEC MSDB..sysmail_help_queue_sp @queue_type = 'Mail' ; 
       
         Query 3: SELECT * FROM msdb..sysmail_mailitems WHERE sent_date > DATEADD(DAY, -1,GETDATE())
*/
          --EXEC msdb.dbo.sysmail_add_profile_sp  
          --  @profile_name = @dbmail_profile,  
          --  @recipients = @dbmail_profile,  
          --  @body = @body,  
          --  @subject = @subject

SET NOCOUNT OFF
END try 

    BEGIN catch 
        DECLARE @ErrorNumber INT; 
        DECLARE @ErrorSeverity INT; 
        DECLARE @ErrorState INT; 
        DECLARE @ErrorLine INT; 
        DECLARE @ErrorProcedure NVARCHAR(4000); 
        DECLARE @ErrorMessage NVARCHAR(4000); 

        SELECT @ErrorNumber = Error_number(), 
               @ErrorSeverity = Error_severity(), 
               @ErrorState = Error_state(), 
               @ErrorLine = Error_line(), 
               @ErrorProcedure = Error_procedure(); 

        SELECT @ErrorMessage = 
               N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' 
               + 'Message: ' + Error_message(); 

        SELECT @ErrorMessage AS [Error_Message]; 

        SELECT @ErrorProcedure AS [Error_Procedure]; 

        PRINT 'Error ' 
              + CONVERT(VARCHAR(50), Error_number()) 
              + ', Severity ' 
              + CONVERT(VARCHAR(5), Error_severity()) 
              + ', State ' 
              + CONVERT(VARCHAR(5), Error_state()) 
              + ', Procedure ' 
              + Isnull(Error_procedure(), '-') + ', Line ' 
              + CONVERT(VARCHAR(5), Error_line()); 

        PRINT Error_message();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState) WITH SETERROR, Log, NOWAIT;
Return;
    END catch 

    SET nocount OFF 
END


GO