Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2011
    Posts
    5

    Question Unanswered: trans log fills out when deleting large data

    Hi all,

    Our DB is ASE 15.5. As far as I know, every modification (insert, delete or update) to a database will be logged. Hence, the transaction log continues to grow until it is truncated (either by a dump transaction command or automatically if the trunc log on chkpt option is turned on). However, this option would turn off on production environment, because it does not save the information contained in the log. In addition, to execute "dump transaction" must either be the database owner or process sa_role or oper_role (obviously, we does not have permissions).

    Therefore, in theory, the transcation log in production environment would never be truncated automatically. So that, there were not enough log space for our application to delete large data. Increase the log sapce may be able to temporarily solve the issue. However, we cannot sure that this issue will not occur any more.

    Do you have any idea? Please kindly give me some suggestions, Thanks!
    Last edited by redraiment; 05-10-11 at 22:26.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I would just delete say 10k records (adjust figure to suit your needs) at a time.
    Wait a suitable period for the trans log to get dumped again.
    Repeat until all deletions done.

    ie something a bit like this:
    Code:
    declare @num_del = 1
    set rowcount 10000
    
    while @num_del > 0
    begin
          delete YourTable where your_condition
          select @num_del = @@rowcount
    
          select count(*) "Num remaining" from YourTable where your_condition
    
          waitfor delay '01:00:00'
    end
    
    set rowcount 0

  3. #3
    Join Date
    Apr 2011
    Posts
    5
    The point in my problem is we didn't know how to dump the trans log with lower permission. Because we cannot trun on "trunc log on chkpt" option and also have not permission to execute "dump tran" statement. Do you have any idea about it?

    Anyway, thanks for your reply!

    Quote Originally Posted by mike_bike_kite View Post
    I would just delete say 10k records (adjust figure to suit your needs) at a time.
    Wait a suitable period for the trans log to get dumped again.
    Repeat until all deletions done.

    ie something a bit like this:
    Code:
    declare @num_del = 1
    set rowcount 10000
    
    while @num_del > 0
    begin
          delete YourTable where your_condition
          select @num_del = @@rowcount
    
          select count(*) "Num remaining" from YourTable where your_condition
    
          waitfor delay '01:00:00'
    end
    
    set rowcount 0

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by redraiment View Post
    The point in my problem is we didn't know how to dump the trans log with lower permission.
    Your question was titled "How to delete large data" and your post rambled on about transaction log issues. It seemed fair to assume that your problem was how to delete large amounts of data and not fill the transaction log - my answer related to this.

  5. #5
    Join Date
    Apr 2011
    Posts
    5
    Quote Originally Posted by mike_bike_kite View Post
    Your question was titled "How to delete large data" and your post rambled on about transaction log issues. It seemed fair to assume that your problem was how to delete large amounts of data and not fill the transaction log - my answer related to this.
    Thanks for your reminder ! I have changed the title.

  6. #6
    Join Date
    May 2011
    Posts
    4
    Why dont you user "truncate table table_A". This command will not write longs, but deletes all the records from the table.

  7. #7
    Join Date
    Apr 2011
    Posts
    5
    Quote Originally Posted by hegde66 View Post
    Why dont you user "truncate table table_A". This command will not write longs, but deletes all the records from the table.
    Because we just want delete a part of data. However, truncate table would removes all rows from a table.

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I still can't see why my previous answer doesn't fix your problem.

  9. #9
    Join Date
    Apr 2011
    Posts
    5
    Quote Originally Posted by mike_bike_kite View Post
    I still can't see why my previous answer doesn't fix your problem.
    Hi Mike,

    Yes, your answer is very helpful!

    As I mentained above, the "trunc log on chkpt" option was turned off on our producation environment. Hence, I thought the log would not clean up regularly.

    However, I have confirmed it with our DBA yesterday. They told us the trans log would backup automatically every one hour. Therefore, we can check log space usage by "sp_spaceused syslogs" behind every delete statement, then wait for one hour as your recommended if the free space less than 10 MB.

    I think this post can be closed. Thanks for your help!

Tags for this Thread

Posting Permissions

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