5

We have a group of fairly complex stored procedures that process a transaction queue on a round the clock schedule. These scripts use a number of print statements to document what is being done to aid in debugging.

I want to keep this information while it is running in production (as an SQL Server Agent job) to help with troubleshooting when something goes wrong. I have looked at this site which has a fairly good method of saving the print output but it also has some downsides (output is not available until the script completes, etc). It also requires SSIS or DTS which I have no experience with.

I have thought of changing all of the print statements to insert statements to a log table. The problem is that much of the work we need debug info for is under transaction control and if there is an error, all of the log information would be rolled back with the actual transaction.

Does anyone have a better solution or an idea of how to work either of the solutions I have looked at above?

Glorfindel
  • 2,205
  • 5
  • 19
  • 26
Caynadian
  • 255
  • 1
  • 4
  • 10

2 Answers2

4

One solution to the rollback problem is to log information to a table variable (which isn't affected by the transaction), then roll back, then insert into the real logging table. Quick example:

DECLARE @LogRow TABLE
(
  dt DATETIME, objectid INT, 
  errornumber INT /* other columns */
);

BEGIN TRANSACTION;

BEGIN TRY

  SELECT 1/0;

  COMMIT TRANSACTION;

END TRY
BEGIN CATCH

  INSERT @LogRow(dt, objectid, errornumber /* , ... */)
    SELECT SYSDATETIME(), @@PROCID, ERROR_NUMBER() /* , ... */;

  ROLLBACK TRANSACTION;

  INSERT dbo.RealLogTable(/*...columns...*/) 
    SELECT /*...columns...*/ FROM @LogRow;

END CATCH
Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
1

In the end, we used the SSIS package with a script that captured the print statements as illustrated in the link in my original question. The captured print output is put in to the SSIS log which can then be directed to a CSV file, XML file, database, etc. (we used a database table). We then run the SSIS package using an SQL Server Agent job (just make sure the SQL Agent user has access to the table you are outputting the logs to).

Although this was simpler due to the complexity of the SPs we are using, it was not ideal. I did try the suggestion from @AaronBertrand above and it does work to enable logging despite using transactions.

Caynadian
  • 255
  • 1
  • 4
  • 10