Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Given the following snippet:

-- error checking omitted for brevity
begin tran

exec database1..my_stored_procedure
exec database2..my_other_stored_procedure

if (@@error <> 0)
  rollback

commit

Into which database's transaction log(s) will the transactional information be inserted?

I would expect both logs would get all the data, since it wouldn't make sense if you attempted to replay database1's transaction log and it only affected that DB. I'd also expect that you would be unable to replay database1's transaction log on a server where database2 wasn't present, and vice versa.

.. but I'm open to corrections!

share|improve this question
add comment

1 Answer

The transaction log isn't recording the SQL statements being executed, as you might be expecting. Instead, it's recording the changes to the raw data in each database, independently.

It's possible for a stored proc from one database to be working entirely in the transaction log of another database.

... database1..my_stored_procedure AS 
BEGIN
INSERT INTO database2..table1 (col1) values (1);
  ^^ changes written to database2's tlog
INSERT INTO database2..table2 (col1) values (2);
  ^^ changes written to database2's tlog
END
^^ when this transaction is committed, COMMIT is recorded in database2's tlog

Or for it to make changes to both.

... database2..my_other_stored_procedure AS 
BEGIN
INSERT INTO database1..table1 (col1) values (1);
  ^^ changes written to database1's tlog
INSERT INTO database2..table1 (col1) values (2);
  ^^ changes written to database2's tlog
END
^^ when this transaction is committed, COMMIT is recorded in BOTH database1's and database2's tlog

What's recorded in the transaction log are the actual data changes, not the SQL statements which caused them to happen. The entries to each transaction log file are entirely independent, except insofar as the COMMIT is written to the two log files at the same time, once the transaction is committed.

The same logic applies if you have a larger transaction running a number of stored procedure across multiple databases. Once you COMMIT your transaction, the COMMIT will be recorded in the log of every database that participated in the transaction.

It's perfectly possible to restore a backup of database2, and replay its transaction logs on a server that doesn't have database1.

This behaviour allows some flexibility in how procedures and views are laid out in SQL Server. Many database administrators keep their stored procedures - especially maintenance ones - in a database (e.g. Admin) that's completely separate to application/user databases, and write the results of the maintenance operation to that database. Thankfully, it's possible to restore one of the user databases to a development server without needing to copy across Admin as well.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.