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:[vbcol=seagreen]
> 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 ba
ck
> Thanks
> Hari
> SQL Server MVP
>
>
> "raghu" <raghu.burju@.gmail.com> wrote in message
> news:1154641875.483366.120510@.75g2000cwc.googlegroups.com...|||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:
> 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:[vbcol=seagreen]
> 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:

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
> > >

Problem with delete trigger

I am trying to write a delete trigger that fires when the user deletes the only record for a given entity in the table. In this situation I have an entity PMA which can have one or many uses. I want the trigger to fire if the user tries to delete the last use record for that PMA_NUM from the PMA_USE table.

Here is my code:

if (SELECT Count(pma_use.pma_num) FROM dbo.PMA_USE
inner join dbo.deleted as D1 on dbo.PMA_USE.PMA_NUM= d1.PMA_NUM) = 0
BEGIN
RAISERROR ('Cannot delete only Use for PMA!', 16, 1)
ROLLBACK TRANSACTION
END

I seem to be getting inconsistent results when I create the trigger and when I try to test it re the deleted table

Sometimes when I run the trigger script in Management Studio Express, it has trouble with my using dbo.deleted and sometimes it doesn't

When I try to test the trigger by using MSE to view the table and delete the last use record for a PMA, I get an invalid object cannot find 'dbo.deleted' or cannot find 'deleted'

What am I doing wrong?

Roberta

1 - Use just [deleted]

2 - you do not need to count in order to prove existence. Use EXISTS operator.

if exists(select * from dbo.PMA_USE as a innner join deleted as b on a.PMA_NUM = b.PMA_NUM)

BEGIN
RAISERROR ('Cannot delete only Use for PMA!', 16, 1)
ROLLBACK TRANSACTION
END

AMB

problem with delete statement

Hi guys,

i need some help on this issue, i have a table with a nvarchar type column with primary key constraint. I have made a procedure which deletes record from this table using the primary key. My primary key is TxnID, and when i ran the following statement:

DELETE from mytable where TxnID = '119DA-117440520'

it deleted the record with the id '119DA-1174405208'

I dont want this to happen, i only want to delete records with the exact IDs that i provide. Can any one tell me how i should do that?

Thanks in advance.

sorry! i managed to fix it...actually the width was not defined properly which was causing problem

sorry again