Our stored procedures use transactions with commits, rollbacks in case of error, and even save points for nested transactions. Here's the question: We want to keep a log of certain activities, even if the transaction that was doing those activities ends up getting rolled back. Is there any way to do that?
Are you using TRY/CATCH? In the CATCH, INSERT the error/etc into a logging table.
Plenty of examples that show what's available: https://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx
I'm still new with this company, so I don't know exactly what data we're dealing with, or when/how it's written. I'll have to check with the senior DB developer who assigned me to look into this and get the details. But yeah, putting it in the catch will probably do what we need. Now I feel stupid for not thinking of that myself. I'll use the end of a long day as my excuse for this one.
Actually, thinking about it, there's a scenario where this might not work. What if it's something that uses save points, and we're only rolling back to the last save point? The catch portion of the try/catch would still be part of the previous transaction, so it could still be rolled back later if that larger transaction gets rolled back.
Like I said, I'll have to talk to the senior DB developer who wanted me to look into how to do this and get more details. Once I get out of the hypothetical and into the specific, I'll know if this works.
The larger transaction should have a try/catch block too. I'll need to see actual code to picture it though.
Yeah, I'll need to see the actual code, too. 
Part of the problem is that there are a lot of sprocs here that use transactions, with rollbacks if things go wrong, but nobody thought to wonder what happens if one of those sprocs calls another until a few days ago. My first task as the new guy on the team was to look into how to deal with a particular sproc that sometimes ends up getting nested in another transaction that way, and I added save points to clean up the process. We agreed that this should be standard procedure going forward, but it looks like going back and updating the 60+ old sprocs that need that change is low priority.
And then this second question came my way, about how to save log data even if the transaction rolls back. If we were just dealing with single transactions and rollbacks, then dealing with it in the catch would be fine. Nested transactions will definitely mess it up, but this may be a narrow enough scenario that once I'm shown the actual code, I'll be able to determine if this sproc is ever nested inside another transaction.
Sorry - but if you are using explicit transactions with commits and rollbacks, logging anything to a permanent table will also be rolled back regardless - even if that is in the CATCH block.
You need to setup and use a table variable to store the logging data - and then save that logging data to a permanent table once you have committed or rolled back all transactions.
Why would this be? Any logging done in the catch block should not get rolled back as long as the logging is done after a rollback in the catch block.
Because they are using nested transactions - and logging anything on the nested transaction will be rolled back. Only the logging at the outermost transaction level - when there are no more open transactions will be saved.
To insure that you get the information at each level - you need to log the information to a table variable (which doesn't get rolled back) - then in the CATCH block check for any open transactions. If there are no open transactions then you can save to the permanent table.
Implementing that logic in the CATCH block of every procedure will be required - so that if the procedure is not nested it will log appropriately, but if it is nested the caller will still have the information to be logged.
Ah, I missed the "NESTED" keyword.
Yeah, it's the nested transactions that are the problem here. Now that I've looked at the specific code, I'm realizing that these are sprocs which could be called by other sprocs, so nesting is a possibility.
I haven't worked with table variables much, so I didn't realize they don't get rolled back. That's good to know, and gives me a direction to look at for this. Thanks, jeffw8713.
The thing about table variables is that while they don't get rolled back, the scope is like that of any other scalar variable; i.e., it is limited to the batch where it is declared; they are not available within other stored procedures that may be called when the variable is in scope etc.
This article has a very nice description of the differences between table variables and temp tables.