Hi there

I have a DTS package
which delete records from tables (around half a million records totally). Data is fetched from different sql server using datapump to local temp tables. Now the temp records that are imported from different SQL Server are copied to production tables using "Insert into" statements with different criteria. The problem is the transaction log is getting filled at the first part, deleting records itself. I have listed the restrictions at the server.

Restrictions
------------
Cant go for BCP as there is no file write permission.
There is no permission for truncate command.
Cant make transaction log autogrow.
The max size is set to 40 MB.
Log backed up every 5 mins

I think that batch execution is a way out. But i have not used it and not sure.

I would be very grateful for any help on this.

Thanks
Vinod

When you say temp do you mean working tables?
Can you move the logs to a different drive with enough space?
Set the recovery model to SIMPLE

If as you say you do not have the option to TRUNCATE and DELETE is your only
option then are you rebuilding the whole table?
If you are then you may just be better off dropping the table and creating
it again?
If you cannot do that then use batches.

execute in a loop
delete the top 5000 rows from table
CHECKPOINT
loop until rowcount = 0




--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Vinod" <mirk_mee_jim (AT) yahoo (DOT) com> wrote

tables. Now the temp records that are imported from different SQL Server are
copied to production tables using "Insert into" statements with different
criteria. The problem is the transaction log is getting filled at the first
part, deleting records itself. I have listed the restrictions at the server.

Temp means working tables used to store temporary records before transferring them to the production tables.
I cant move log to differnt drive or even truncate, I have low level rights. Neither i can drop or create tables dynamicallly.

Yes im now trying to delete the records in batches. Moving further I have the same problem when i import data that has to be filled in the tables in which the records been deleted

The Process is like this..

1. Data is imported from Server1 to Server
2. DTS Package runs from Server2 and has low level rights (Insert, Delete, Update, SPs
3. There are temporary tables (actual tables) meant to store the data from Server1. There are more identical tables meant for production to which data from temporary tables shall be converted and transferred
4. In this process as we have restricted rights, we are first deleteing the records from the Server2 (Temp tables) and then importing data from Server1 (using Transform Data Task) and then the data from temp tables are converted and transferred to production
5. We had problem in deleting all the records in each table. As we run single "delete from <tablename>" statement for each table. Then we are now adopting batch method for deleting. But for the rest of the data we are clueless because the data is transferred from Server1 to Server2(temp tables) using "Transformation Task" where againt he tr logs are getting filled up :

Regard
Vino