Thread: Transaction Log Full
01-13-06, 18:45 #1Registered User
- Join Date
- Apr 2005
Unanswered: Transaction Log Full
I have a table with over 120 million records. In order to keep the number of records down to a minimum, I have to find a way to export summary data and then delete records on a monthly basis from this table. Supporting tables don't have any where near as many records and I only have to delete from this one table. Currently using circular logging.
Records do not get edited.... simply post a record, pull summary data, export summary data and delete records when old.
My Log File Size is set to 50000 and my number of primary logs was set to 25... I just upped it to 50 today. Secondary Log files is set to 0.
My delete query isn't pretty, but it's simple -
Where Month(Timekey_dttm) = 11 and Day(Timekey_dttm) < 5;
My script repeats this and increments the Day(Timekey_dttm) < 10... 15.. etc., until I've covered all days in the month.
Once I'm able to successfully delete records in this manner, I'll clean up the query/script to address year changes, etc.
Can someone explain why I'm still getting Transaction Log File Full msgs? I'm now allocating 10 gig to my Transaction Logs. My backups aren't that big.
Should I look at an alternative delete solution?
Thanks in advance.
01-15-06, 04:29 #2Registered User
- Join Date
- Nov 2004
you could : alter table aCTIVATE NOT LOGGED INITIALLY and delete records in same unit of work and commit...Best Regards, Guy Przytula
DB2 DBA & Advanced DBA Certified
DB2 Dprop Certified
01-16-06, 11:22 #3Registered User
- Join Date
- Mar 2004
It seems you do a periodic(monthly?) purge of records from this table.
Doing it using deletes will certainly log the activity(deletes).
One suggestion for both performance and to reduce logging activity is to export the (required data out) and reload it into the table using db2 load.
db2 "export to <file-name> of del select * from <table> where <search-condition-for-data-to-preserve>"
db2 "drop table <table>"
db2 "create table <table>...."
db2 "load from <file-name of del ......(non-recoverable, etc.)"
Another option is to create another (new) table, load that new table with data to be retained. Then drop the current table and rename the new table to the new table.