Hi there,

Thanks to forum for everything.
I have a problem here for ur valuable response,


I have a table with almost one million rows, the table has 21 child tables. I need to delete two lac rows from the master table. Is there any solution, so that I dont face any transaction log full error???

thank you.

Write a stored procedure with a cursor on the main parent table, and then commit after each logical unit of work (which includes deleting all the child tables for a parent). You will need to define the cursor WITH HOLD so it will not close when you do a commit.

thank you, but can u plz elaborate it?

Actaully writing such a stored procedure is a fee-based activity IMO. If you or your company are interested, then send me a private message.

Try using the search button. This question has been asked and answered dozens of times. If you search hard enough you will find an example.

If you have difficulties when creating stored procedure, come back to the forum.

Or, of course you have the option to PM Marcus.

that is really a good idea ..
...is a fee-based activity IMO. If you or your company are interested, then send me a private message..
I will use it a lot in the future.. in this forum anyhow..

..... or Guy

I will also provide forum searches for people (on a fee basis) for those who don't have time to do it themselves.

If it is a one time job .. then this could be a idea. Select rows in chunks
and delete them again and again.

delete form (select * from table name fetch first 15000 rows only).

you can right a small shell script also.

Thanks
Naval K

The reason why I recommended a stored procedure is that the OP said there was one parent table with 21 child tables, and I assume that the logical UOW integrety needs to be maintained. A properly written SP will also have the following additional advantages:

• not filling up the transaction log since there would be intermediate commits at the logical UOW level
• doing intermediate commits reduces lock contention during the deletes, since only those rows deleted since the last commit point will be locked

This is second best option to SP. I had used it for all one time jobs. Some daily jobs run via SP.

Make sure the chunk is small enough to let the database keep going easy.

One of my prune job using shell script took 11 hours on a very busy gaming database.

A template SP can be provided if needed !!


Regards

DBFinder