Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    3

    Question Unanswered: how to determine the log size required for an update

    Hi

    I am new to DB .
    I have an update query which should update 300,000 rows .
    The size of the table is around 140 MB.

    When i do the update , i get log suspended .

    The log size is 290 MB .

    I want to know how much log size is required in MB for a table of size 140 MB to get updated .

    It updates till 190 MB and then gives a message log suspended .

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    It might be better leaving the log alone and just split your update into multiple smaller updates ie something like :

    Code:
    set rowcount 10000
       update MyTable
       set field = "new value"
       where field != "new value"
    set rowcount 0
    
    waitfor delay "00:00:05"
    Then run this repeatidly until no more updates made ie 30 times. If no one else is using the database then you could get rid of the delay.

  3. #3
    Join Date
    Feb 2009
    Posts
    3

    log suspended error

    Quote Originally Posted by mike_bike_kite
    It might be better leaving the log alone and just split your update into multiple smaller updates ie something like :

    Code:
    set rowcount 10000
       update MyTable
       set field = "new value"
       where field != "new value"
    set rowcount 0
    
    waitfor delay "00:00:05"
    Then run this repeatidly until no more updates made ie 30 times. If no one else is using the database then you could get rid of the delay.

    Mike

    I tried what you told me to do

    create proc sp_update
    as

    begin

    set rowcount 10000
    UPDATE Student
    SET RING_NAME = w.LNAME
    FROM Student p,SCHOOL w
    where p.ID = w.SID
    set rowcount 0

    print "Update done, sleeping for 1 minute"
    waitfor delay "00:00:05"



    end

    But i get the same error : log suspended ..

    What have i done wrong : that it is filling the log ..

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    The SQL looks "curious" to me. Why is the student ID joining with the school ID field? What is a student RING_NAME? why is it being set to the schools LNAME? What are the ID's? Perhaps you can give us a few records from each table so we can check what's going on. Is the log still full from before?

    If the SQL is correct then you don't need to put it in a stored proc. You could reduce the 10000 to 1000. It also only sleeps for 5 seconds.

    It's also polite to point out at the start of the thread that this is homework.

  5. #5
    Join Date
    Feb 2009
    Posts
    3

    The datat looks like this

    Hi ,

    Pls help me with this sp :

    The data looks like this :-(table has 300 k rows)

    Student table

    ID RING_NAME
    -----------------------------------------
    20110680 NULL
    20120693 NULL
    20120699 NULL


    SCHOOL table , data looks like this (same number of rows 300k)

    SID LNAME
    ------------------------------------------
    20110680 GEORGE
    20120693 WELLS
    20120699 MILLS





    create proc sp_update
    as

    begin

    set rowcount 10000
    UPDATE Student
    SET RING_NAME = w.LNAME
    FROM Student p,SCHOOL w
    where p.ID = w.SID
    set rowcount 0

    print "Update done, sleeping for 1 minute"
    waitfor delay "00:00:05"

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    The SQL looks "curious" to me. Why is the student ID joining with the school ID field? What is a student RING_NAME? why is it being set to the schools LNAME? What are the ID's? Is the log still full from before?

    If the SQL is correct then you don't need to put it in a stored proc. You could reduce the 10000 to 1000. It also only sleeps for 5 seconds.

    It's also polite to point out at the start of the thread that this is homework.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •