SQL SERVER – Fix – Error – Agent XPs component is turned off

When you restore a transaction log using the NORECOVERY option, SQL Server does not need to analyze the transaction log and roll back any uncommitted transactions at the end of the restore process.

When you restore a transaction log using the STANDBY option, SQL Server has to undo any uncommitted transactions so that the data files contain only committed transactions. While it does this, it records whatever details necessary to be able to redo the uncommitted transactions. From what I can tell, beyond a certain limit, it simply records the entire contents of the pages that it had to undo to the undo file. Thus, if your transaction log contained uncommitted transactions that affected rows contained in 1000 different pages, the entire contents of those 1000 pages will be stored in the undo file, even though not all the rows in those pages may be affected by the uncommitted transactions.

When you then restore the next transaction log, SQL Server has to first apply the contents of the undo file to the data files i.e. the 1000 pages above have to be first written to the data files. Only after that can SQL Server restore the transaction log, and if you are again using the STANDBY option, SQL Server has to again undo the changes made by any uncommitted transactions, and record those operations to the undo file. Note that the uncommitted transactions does not just come from the latest transaction log. It includes uncommitted transactions in all previous transaction logs that have been restored.
Say that in your conversion process, you made changes to 1000 rows in a table, and the transaction is uncommitted. In your production database, if you viewed the contents of the table in read committed isolation level from a different connection, you will not see the changes to those rows. Actually, you'll be blocked from viewing the contents of the table. However, if you changed the isolation level to read uncommitted, you will see the changes. This is because the data files contain only the new values. If the transaction was now rolled back, SQL Server would record the rollback in the transaction log, essentially an update of the uncommitted 'new' values with the 'old' values (obtained from the transaction log since it is still an active transaction).
Now in your standby database, if you viewed the contents of the table, you will not see the changes, regardless of which transaction isolation level you used. This is because the data pages contain only the old values. The new uncommitted values are stored in the undo file. So basically, there is more work to be performed when using the STANDBY option as compared to the NORECOVERY option.
You mentioned that the undo file grew to 3 GB in some cases. So on the next transaction log restore, your SQL Server instance would need to redo 3 GB worth of modifications/page writes, restore the transaction log (which was probably very fast), then scan through the entire transaction log (the .ldf file), undo all uncommitted transactions again, and generate the undo file containing details of those transactions.
You can log additional details re the backup/restore process to your SQL Server log using the following trace flags
DBCC TRACEON (3004, 3605, -1)
but do so at your own risk as I do not know all the side-effects of turning those options on.

I believe that only affects the number of records stored in the msdb..sysjobhistory table, and would have little or no impact on your restore throughput.