Geek Answers Handbook

Huge transaction on Sql server, are there any problems?

I have a program that performs many mass operations in a SQL Server 2005 or 2008 database (collapses and creates indexes, creates columns, full table updates, etc.), all in one transaction.

Are any problems expected?

  • I know that the transaction log expands even in simple recovery mode.
  • This program does not run during normal operation of the system, so locking and concurrency are not a problem.

Are there other reasons for splitting a transaction into smaller steps?

+4
source share
4 answers

Shortly speaking,

  • Using smaller transactions provides more reliable disaster recovery.
  • Long transactions can also unnecessarily hold object locks for extended periods of time, so other processes may require access to the lock.

Keep in mind that if at any point in time between the start and end of the transaction your server failed, in order to bring the database online, SQL Server had to complete the crash recovery process, which would include the rollback of all pending transactions from the log.

Suppose you have developed a data processing solution that is smart enough to pick up from where it left off. Using one transaction, this will not be available to you because you will need to start the process again with begging.

+2
source

This is not a problem until the disk space runs out, but you will find that the rollback will take a long time. I am not going to plan failure, of course.

However, consider the process, not the transaction log itself. I would think of a split:

  • DDL in a single transaction
  • Transaction Bulk Tables
  • Clear data from stage to final table in another transaction

If something goes wrong, I hope you have rollback scripts and / or backup.

Do I need to do everything atomically?

+1
source

If a transaction causes too many database log entries (updates), the log may fall into the so-called "high water mark". This is the point at which the log reaches (about) half its absolute maximum size, when it should then start rolling back all updates (which will consume about the same amount of disk as would be required for the update).

Not rolling back at this point would mean the risk of ultimately reaching the maximum size of the log and still not complete the transaction or hit the rollback command, after which the database will be screwed up because there is not enough space for the log to roll back.

0
source

Depending on the complexity of your update statements, I would recommend doing this only on small tables, say, a few 100 rows. Especially if you have only a small amount of main memory. Otherwise, for example, updates on large tables can be very time consuming and even seem to freeze. Then it’s hard to understand what the process is doing (spid) and how long it may take.

I am not sure that the "Drop index" is a transaction with a transaction anyway. See this question here at stackoverflow.com.

0
source

More articles:


All Articles