Retrieving messages printed during sp_executesql

Question

I am running a command through sp_executesql in a SQL Server Agent. How do I capture messages from this command so I can log them in a table?

In particular I am attempting to truncate the database log and would like to be able to see if this succeeded. I am adding the code snippet in the agent SP:

IF (@truncate = 1)
BEGIN
  SET @msg = 'Truncating log ''' + @description + ''' for the ''' + @db + ''' database'
  EXEC _LogInsert 'Information', '_Loop', 'Status', @msg

  --PRINT 'Attempting to truncate the log file'
  SET @sql = N'USE [' + @db + ']' +

'DECLARE @LogFile varchar(128)' +

'SELECT TOP 1 @LogFile = [Name] ' +
'FROM sys.database_files ' +
'WHERE ([Type_Desc] = ''LOG'')' +

'DBCC SHRINKFILE(@LogFile, EMPTYFILE)' +
'DBCC SHRINKFILE(@LogFile, 10) WITH NO_INFOMSGS'
  --PRINT 'truncation attempt finished'
EXEC sp_executesql @sql

  -- Capture messages from SP
  -- SET @msg = 'Truncating log msg ''' + @resmsg + ''' for the ''' + @db + ''' database'
  -- EXEC _LogInsert 'Information', '_Loop', 'Status', @msg


END

Answer 1

If you run this as a job you can take data you need (at least DBCC output, or PRINT) from dbo.sysjobhistory:

SELECT  jh.[run_date],
        jh.[step_name],
        jh.[message]
FROM msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j
    ON j.[name] = 'job_name' and j.job_id = jh.job_id

Also, pay attention to WITH NO_INFOMSGS- this suppresses all informational messages.