Maximum transaction size?

Question

Maximum transaction size?

Megistal

SSCrazy Eights

Points: 8787
More actions
January 18, 2012 at 6:50 am

#263040

Anyone knows the maximum size a transaction can be?
In other words how much data can be scope inside a single transaction?
My first thought was: as long as there place inside the Log which would be around 2 terabytes but I'm wrong.
I've also check on the DBEngine maximum and nothing is listed for this.
And I know there's a limit, we've reached it and I believe it would be around 2 or 4 gigs of data but I'm not sure of the actual limit or how I can forecast (compute) that limit for the huge amount of data we need to commit at once.
Ty

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There's no hard limit on the amount of data that a single transaction can process (other than the one imposed by the amount of available log space)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Megistal SSCrazy Eights Points: 8787 More actions · Answer 2

Thks

I should have copy the error but SQL gave back something like "Exceeded transaction size" which make me wonder what limit I've reached for a transaction.

I had enough room for the transaction log to grow (and no limit was applied on the log size). I'm still wondering why.

I'll let you know the exact error message once I get it again.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I had a quick look through the SQL error messages table, there's no error message that resembles that. There's messages about aborting the transaction because the log is full, the version store is full, but I couldn't see one that's general. Sure that was a SQL error message?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Megistal SSCrazy Eights Points: 8787 More actions · Answer 4

Thank you for your time. I'll call it off for now, unable to reproduce it (and unable to have the exact same scenario)

At that time too many things were different, x86 setup instead of x64 (not the same machine anymore and not the same SQL instance), MSDTC was coordinating the whole transaction, read_committed_snapshot isolation was on (shouldn't be an issue), and of course (and what I suspect), a manipulation error I've made or overlook.

Great idea to dig inside the sys.messages.