CODEWITHSUNDEEP
sql-servertransactionsbulk
Stefan Steinegger
Stefan Steinegger

Reputation: 64648

Huge transaction in Sql Server, are there any problems?

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?

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

Upvotes: 9

Views: 8754

Answers (4)

knb
knb

Reputation: 9313

Depending on the complexity of your update statements, I'd recommend to do this only on small tables of, say, a few 100 rows. Especially if you have only a small amount of main memory available. Otherwise, for instance, updates on big tables can take a very long time and even appear to hang. Then it's difficult to figure out what the process (spid) is doing and how long it might take.

I'm not sure whether "Drop index" is transaction-logged operation anyway. See this question here on stackoverflow.com.

Upvotes: 0

gbn
gbn

Reputation: 432561

It isn't really a problem until you run out of disk space, but you'll find that rollback will take a long time. I'm not saying to plan for failure of course.

However, consider the process not the transaction log as such. I'd consider separating:

  • DDL into a separate transaction
  • Bulk load staging tables with a transaction
  • Flush data from staging to final table in another transaction

If something goes wrong I'd hope that you have rollback scripts and/or a backup.

Is there really a need to do everything atomically?

Upvotes: 1

John Sansom
John Sansom

Reputation: 41889

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.

Upvotes: 7

Bohemian
Bohemian

Reputation: 425288

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.

Upvotes: 1

Related Questions