Problem with delete, lock and transaction log

we have problem with SQL Server 2000 SP3 / Windows 2003 EE
we have 10 huge tables, I have a procedure which deletes data from
these tables each day.
1. The deletes are very slow, it takes 20 min to delete 400,000
records. It does use index when it deletes, i have seen the plan
2. i am running the database in simple mode but still the transaction
log grows bigger and bigger during the delete process. I have used
checkpoint after every table delete but it didnt help. is there a way
we can avoid writing to transaction log so that i can solve this
problem and improve the delete performance too
3. If i stop the job then everything gets rolled back that is been done
by procedure. does it not commit table by table within the procedure?
is it waiting for the procedure to finish before it commits?
Can any one please help me ASAP
Thanks
RaghuAnswer for you questions:-
1. Disable the foreign key if you can do the house keeping after shutdown
the application [ See Alter table command]
2. No you can not avoid to log the transaction. The easy way is shrink the
transaction log fter delete [See DBCC SHRINKFILE]
3. That depends up the way you handle the transaction.. If its a single
delete statement inside the procedure and if you kill the job;
then automatically every thing pertaning to that delete will be rolled back
Thanks
Hari
SQL Server MVP
"raghu" <raghu.burju@.gmail.com> wrote in message
news:1154641875.483366.120510@.75g2000cwc.googlegroups.com...
> we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> we have 10 huge tables, I have a procedure which deletes data from
> these tables each day.
> 1. The deletes are very slow, it takes 20 min to delete 400,000
> records. It does use index when it deletes, i have seen the plan
> 2. i am running the database in simple mode but still the transaction
> log grows bigger and bigger during the delete process. I have used
> checkpoint after every table delete but it didnt help. is there a way
> we can avoid writing to transaction log so that i can solve this
> problem and improve the delete performance too
> 3. If i stop the job then everything gets rolled back that is been done
> by procedure. does it not commit table by table within the procedure?
> is it waiting for the procedure to finish before it commits?
> Can any one please help me ASAP
> Thanks
> Raghu
>|||Thanks hari for the quick response.
I have no foreign keys on these tables. All i have is indexes to of
them one is for delete based on timestamp and other is for select which
includes 3 more columns.
DBCC SHRINKFILE is only after i do the delete, even before the delete
is finished my 100 GB transaction log is getting filled and stopping
all other application to either insert or delete data.
I have 10 delete statements so this mean that everything that it has
tried till that time is rolledback. I can do some kind of commit which
will clear the log as well as prevent the rollback if any problem
occurs. The begin and end transaction locks the table and wont allow to
make selects till it is commited, i want where selects can work when
delete is going on
Raghu
Hari Prasad wrote:
> Answer for you questions:-
> 1. Disable the foreign key if you can do the house keeping after shutdown
> the application [ See Alter table command]
> 2. No you can not avoid to log the transaction. The easy way is shrink the
> transaction log fter delete [See DBCC SHRINKFILE]
> 3. That depends up the way you handle the transaction.. If its a single
> delete statement inside the procedure and if you kill the job;
> then automatically every thing pertaning to that delete will be rolled back
> Thanks
> Hari
> SQL Server MVP
>
>
> "raghu" <raghu.burju@.gmail.com> wrote in message
> news:1154641875.483366.120510@.75g2000cwc.googlegroups.com...
> > we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> > we have 10 huge tables, I have a procedure which deletes data from
> > these tables each day.
> >
> > 1. The deletes are very slow, it takes 20 min to delete 400,000
> > records. It does use index when it deletes, i have seen the plan
> >
> > 2. i am running the database in simple mode but still the transaction
> > log grows bigger and bigger during the delete process. I have used
> > checkpoint after every table delete but it didnt help. is there a way
> > we can avoid writing to transaction log so that i can solve this
> > problem and improve the delete performance too
> >
> > 3. If i stop the job then everything gets rolled back that is been done
> > by procedure. does it not commit table by table within the procedure?
> > is it waiting for the procedure to finish before it commits?
> >
> > Can any one please help me ASAP
> >
> > Thanks
> > Raghu
> >|||raghu wrote:
> we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> we have 10 huge tables, I have a procedure which deletes data from
> these tables each day.
> 1. The deletes are very slow, it takes 20 min to delete 400,000
> records. It does use index when it deletes, i have seen the plan
> 2. i am running the database in simple mode but still the transaction
> log grows bigger and bigger during the delete process. I have used
> checkpoint after every table delete but it didnt help. is there a way
> we can avoid writing to transaction log so that i can solve this
> problem and improve the delete performance too
> 3. If i stop the job then everything gets rolled back that is been done
> by procedure. does it not commit table by table within the procedure?
> is it waiting for the procedure to finish before it commits?
> Can any one please help me ASAP
> Thanks
> Raghu
>
You could use TRUNCATE TABLE, which is non-logged. Be sure to read the
Books Online entry first, there are some caveats to using this vs. DELETE.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy
Truncate table would clean the entire table. I am just trying to clean
old data that is 15 days old and this is a job which does this each day
Raghu
Tracy McKibben wrote:
> raghu wrote:
> > we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> > we have 10 huge tables, I have a procedure which deletes data from
> > these tables each day.
> >
> > 1. The deletes are very slow, it takes 20 min to delete 400,000
> > records. It does use index when it deletes, i have seen the plan
> >
> > 2. i am running the database in simple mode but still the transaction
> > log grows bigger and bigger during the delete process. I have used
> > checkpoint after every table delete but it didnt help. is there a way
> > we can avoid writing to transaction log so that i can solve this
> > problem and improve the delete performance too
> >
> > 3. If i stop the job then everything gets rolled back that is been done
> > by procedure. does it not commit table by table within the procedure?
> > is it waiting for the procedure to finish before it commits?
> >
> > Can any one please help me ASAP
> >
> > Thanks
> > Raghu
> >
> You could use TRUNCATE TABLE, which is non-logged. Be sure to read the
> Books Online entry first, there are some caveats to using this vs. DELETE.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||raghu wrote:
> Tracy
> Truncate table would clean the entire table. I am just trying to clean
> old data that is 15 days old and this is a job which does this each day
>
I guess I missed that in your original post...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I have resolved one problem by splitting the job of delete into 2
different procedures where in the transaction log doesn't grow big now.
The performance of delete is still not improved it does take same
amount of time as mentioned. can anyone have any idea FYI
The deletes are too slow. it takes 20 min to delete 400000 records.
Raghu
raghu wrote:
> Thanks hari for the quick response.
> I have no foreign keys on these tables. All i have is indexes to of
> them one is for delete based on timestamp and other is for select which
> includes 3 more columns.
> DBCC SHRINKFILE is only after i do the delete, even before the delete
> is finished my 100 GB transaction log is getting filled and stopping
> all other application to either insert or delete data.
> I have 10 delete statements so this mean that everything that it has
> tried till that time is rolledback. I can do some kind of commit which
> will clear the log as well as prevent the rollback if any problem
> occurs. The begin and end transaction locks the table and wont allow to
> make selects till it is commited, i want where selects can work when
> delete is going on
> Raghu
> Hari Prasad wrote:
> > Answer for you questions:-
> >
> > 1. Disable the foreign key if you can do the house keeping after shutdown
> > the application [ See Alter table command]
> >
> > 2. No you can not avoid to log the transaction. The easy way is shrink the
> > transaction log fter delete [See DBCC SHRINKFILE]
> >
> > 3. That depends up the way you handle the transaction.. If its a single
> > delete statement inside the procedure and if you kill the job;
> > then automatically every thing pertaning to that delete will be rolled back
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> >
> >
> >
> > "raghu" <raghu.burju@.gmail.com> wrote in message
> > news:1154641875.483366.120510@.75g2000cwc.googlegroups.com...
> > > we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> > > we have 10 huge tables, I have a procedure which deletes data from
> > > these tables each day.
> > >
> > > 1. The deletes are very slow, it takes 20 min to delete 400,000
> > > records. It does use index when it deletes, i have seen the plan
> > >
> > > 2. i am running the database in simple mode but still the transaction
> > > log grows bigger and bigger during the delete process. I have used
> > > checkpoint after every table delete but it didnt help. is there a way
> > > we can avoid writing to transaction log so that i can solve this
> > > problem and improve the delete performance too
> > >
> > > 3. If i stop the job then everything gets rolled back that is been done
> > > by procedure. does it not commit table by table within the procedure?
> > > is it waiting for the procedure to finish before it commits?
> > >
> > > Can any one please help me ASAP
> > >
> > > Thanks
> > > Raghu
> > >