Please read the article I posted as well as the blog post that Sean referenced.

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

The transaction has been initiated but no log records reflecting that...

Off the top of my head, no. Don't think I ever got around to writing one. Might have been part of Paul Randal's myth a day series.

Sean Lange (7/18/2013)

---

You can begin as many as you like but after the first all it does is increment @@TRANCOUNT. Then when there is a commit or rollback they are...

Err... there's no such thing as a 'logical' or 'physical' transaction. There are just transactions.

sys.dm_tran_active_transactions reports at an instance level, so if you're trying it ID transactions within a single...

If DBCC OPENTRAN is showing a session, then that session has an open transaction. If it's AWAITING COMMAND, that just means that the transaction has been started and not committed.

The...

Well index rebuilds are unlikely to make noticable improvements in performance, so maybe the question should rather be 'what can be done'

Tune the query to use indexes, modify/add indexes to...

Too small to bother with.

The reason you're getting odd results is that BEGIN TRANSACTION alone is not enough to actually start a transaction. The transaction will start when the next command is run.

So

BEGIN TRANSACTION

SELECT...

Ignore the non-leaf levels unless they're absolutely huge.

Let me guess, those nonclustered indexes are tiny, just a couple of pages?

Other...

Don't bother trying. The highest level is the root, which is only a...

Welsh Corgi (7/18/2013)

---

As far as the option to backup the tail of the transaction log, do you use that for all transaction log backups or for the last transaction log...

Benchmark the servers, see what they currently use, plan future growth, that gives you somewhere...