How to delete large data?

redraiment · #1 (**permalink**) 05-10-11, 06:15

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!

mike_bike_kite · #2 (**permalink**) 05-10-11, 09:25

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

redraiment · #3 (**permalink**) 05-10-11, 09:39

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

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

mike_bike_kite · #4 (**permalink**) 05-10-11, 12:19

Quote:

Originally Posted by redraiment

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.

redraiment · #5 (**permalink**) 05-10-11, 21:28

Quote:

Originally Posted by mike_bike_kite

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.

hegde66 · #6 (**permalink**) 05-13-11, 03:41

Why dont you user "truncate table table_A". This command will not write longs, but deletes all the records from the table.

redraiment · #7 (**permalink**) 05-13-11, 04:38

Quote:

Originally Posted by hegde66

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.

mike_bike_kite · #8 (**permalink**) 05-13-11, 06:27

I still can't see why my previous answer doesn't fix your problem.

redraiment · #9 (**permalink**) 05-13-11, 22:38

Quote:

Originally Posted by mike_bike_kite

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech