Mass Delete - Log Entries Kills Available Log Space

Mass Delete - Log Entries Kills Available Log Space

Post by Gary Glickma » Sat, 20 Sep 1997 04:00:00



This may be a newbie type question, but...

I have a VB program that does mass loads to certain SQL databases.  The
programs are set to empty the tables first, then do massive loads.

However, I have run into an issue that when the program attempts to delete
the rows first (about 250,000), about 1/3 of the way through, I get a log
file full error.  I then have to Truncate the log (with the No_Log option)
and restart the program.  It takes two attempts before the program finally
finishes the delete.  The delete is a simple mass delete like this:

DELETE FROM MYTABLE

Is there any way to issue this SQL statement without the resulting deletes
being logged.  I am using VBSQL to hit the SQL server from VB.  I do not do
a Begin/End Transaction.

Any help would be appreciagted.


--
Regards,
Gary Glickman
http://www.interwavesoft.com

****InterWave - "Your Total Solution Provider"****

 
 
 

Mass Delete - Log Entries Kills Available Log Space

Post by Bob Pfeif » Sat, 20 Sep 1997 04:00:00


Use truncate table, but remember it can't be rolled back.
--
Bob Pfeiff, MCSD
Spectrum Technology Group, Inc.

<<remove "nospam" to reply via e-mail>>

 
 
 

Mass Delete - Log Entries Kills Available Log Space

Post by JRSte » Mon, 22 Sep 1997 04:00:00




Quote:>DELETE FROM MYTABLE

>Is there any way to issue this SQL statement without the resulting deletes
>being logged.  I am using VBSQL to hit the SQL server from VB.  I do not do
>a Begin/End Transaction.

>Any help would be appreciagted.

Probably the simplest thing is to (a) enlarge your log, and/or (b)
break the statement into several smaller deletes.

Josh

 
 
 

Mass Delete - Log Entries Kills Available Log Space

Post by steve robinso » Thu, 25 Sep 1997 04:00:00


One way to do this would be to set the truncate log on checkpoint option on
(found under the edit button of the database when you right click) .  You
could the break down your delete into chunks of 10000 eg

set rowcount 10000
  DELETE FROM MYTABLE
go
  DELETE FROM MYTABLE
go
  DELETE FROM MYTABLE
go

This will keep deleting from the table until nothing is left and the log
should truncate itself about once a minute

OR

set rowcount 10000
while exists (select * from Mytable)
begin
    DELETE FROM MYTABLE
    dump transaction <dbname> with no_log
end

This will keep deleting from the table until nothing is left and truncating
the logas it goes along
I hope this is of use to you

--

 
 
 

Mass Delete - Log Entries Kills Available Log Space

Post by Hatheway, Darwin L » Fri, 26 Sep 1997 04:00:00


Use the "truncate table" command.  It's very fast and logs almost nothing
at all.

 
 
 

1. Log Size 0, Log Space Available 2,100 MB

The 1 gig log on an 8 gig database was expanded to 2.1 gigs.  Now the
GUI shows a log size of zero with 2.1 mb available.  DBCC
CheckTable(Syslogs) shows the correct numbers.  I've tried
recalculating.  Any ideas on how to correct the GUI would be appreciated
(the customer can't grasp the fact that the GUI is wrong).

-Don Moro
Radiant Systems

2. Number Alignment

3. Recapturing disk space from the transaction log after truncating the log space

4. Using Visual C++ to run sample dblib programs

5. log buffer space vs. redo log space requests

6. (Fwd) Looking for Database program to create Database Applicat

7. Mass delete and transaction log

8. Auto Incrementing "TEXT" fields

9. Trx log backup - how suppress event log entries?

10. Entries in DB2DIAG.log and NT-Event-Log when connecting

11. Transaction Log Space Available

12. Log Space Available

13. SP that Returns Available Log Space?