Is all transactions in one cursor treated as one transaction?

Question

Post reply

Is all transactions in one cursor treated as one transaction?

Siqing LU

Say Hey Kid

Points: 665
More actions
December 11, 2019 at 10:29 pm

#3707185

The client need to update a big table. the UPDATE will be done in the application and the application is using cursor to update the table. I am worrying about it will fill up the transaction log. will the backup log help?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

SQL_Hacker SSCommitted Points: 1678 More actions · Answer 2

Siqing LU wrote:

The client need to update a big table. the UPDATE will be done in the application and the application is using cursor to update the table. I am worrying about it will fill up the transaction log. will the backup log help?

When you say the application is using a cursor, is that happening outside of the database? Or is the cursor in a stored procedure the application calls within the database?

Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Siqing LU Say Hey Kid Points: 665 More actions · Answer 3

it is not stored procedure. the application send the SQL statement to SQL, which use cursor to update the database.

Thanks

SQL_Hacker SSCommitted Points: 1678 More actions · Answer 4

Hmmm. Okay, well your question was really about the transaction log, so here's how you can make sure the log file doesn't grow "too much". Please keep in mind that the transaction log is supposed to grow to accommodate the transactions flowing through the database. If it is growing bigger than you want it to, increase the frequency of the transaction log backups. For example, if you're currently backing up the log every hour, consider changing it to every 30 minutes or even every 15 minutes. This will keep the size from getting out of control on most systems. If you have VERY heavy transaction throughput, then you may have to make a decision about how often to backup the log file versus allowing the log file to grow. If you start to see performance problems related to backing up the log too often, I would suggest allowing the log file to grow. If disk space is an issue, grow the disk to be able to handle the size of the log.

These suggestions assume that you have control over the frequency of log backups and the disk size on your server.

Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

Siqing LU wrote:

The client need to update a big table. the UPDATE will be done in the application and the application is using cursor to update the table. I am worrying about it will fill up the transaction log. will the backup log help?

I agree with SQL_Hacker and to emphasize a bit...

It doesn't matter if it's done in bulk or by row using a cursor nor where the cursor is instigated from, the transaction log is going to record every update for every updated attribute of every updated row, period. Of course, you knew that but I had to say it out loud.

You say the "client need to update a big table" and that they're going to use an application to do so. You've not specifically stated it but you made it sound like they going to do an update to very large number of rows in the big table.

While I think that's going to totally suck for performance if they do it using RBAR (Row By Agonizing Row), it will also be the easiest on the log file. In fact, doing smaller groups of updates (say, 1000 rows at a time) is one of the methods folks recommend to do monster sized updates of large tables just so the log table doesn't suffer massive unwanted growth. In such situations, increasing the frequency of transaction log file backups will definitely help keep the size of the log file down.

Just to ask the questions...

Is this a "one-off" effort or something that's going to occur on a regular basis?
Regardless of which is true above, what will a typical total row count be?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

p.s. To answer the question posed in the title of this post, no... not unless you tell it to with an explicit transaction.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)