Huge transaction in Sql Server, are there any problems?

Question

I have a program which does many bulk operations on an SQL Server 2005 or 2008 database (drops and creates indexes, creates columns, full table updates etc), all in one transaction.

Are there any problems to be expected?

• I know that the transaction log expands even in Simple recovery mode.
• This program is not executed during normal operation of the system, so locking and concurrency is not an issue.

Are there other reasons to split the transaction into smaller steps?

Answer 1

In short,

• Using smaller transactions provides more robust recovery from failure.
• Long transactions may also unnecessarily hold locks on objects for extended periods of time that other processes may require access to i.e. blocking.

Consider that if at any point between the time the transaction started and finished, your server experienced a failure, in order to be bring the database online SQL Server would have to perform the crash recovery process which would involve rolling back all uncommitted transactions from the log.

Supposing you developed a data processing solution that is intelligent enough to pick up from where it left off. By using a single transaction this would not be an option available to you because you would need to start the process from the begging once again.

Answer 2

If the transaction causes too many database log entries (updates) the log can hit what is known as the "high water mark". It's the point at which the log reaches (about) half of its absolute maximum size, when it must then commence rolling back all updates (which will consume about the same amount of disk as it took to do the updates.

Not rolling back at this point would mean risking eventually reaching the maximum log size and still not finishing the transaction or hitting a rollback command, at which point the database is screwed because there's not enough log space to rollback.