Is all transactions in one cursor treated as one transaction?

  • Siqing LU

    SSChasing Mays

    Points: 649

    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?

  • Site Owners

    SSC Guru

    Points: 80375

    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: 1648

    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

    SSChasing Mays

    Points: 649

    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: 1648

    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: 996832

    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...

    1. Is this a "one-off" effort or something that's going to occur on a regular basis?
    2. 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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996832

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

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

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