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?