Deleting Large Quantiy of Rows

Hi Guys,
I need a little advice. I need to delete 250000 rows on a monthly basis and
my users are using MSDE 2000 with merge replication, publisher is SQL Server
2000 Standard.
The users will never be uploading or making changes to this table. Is there
any way i can make this a quick delete as currently for each row that is in
the Article published table sql is sending down a delete statement for each
row to the subscriber. Thus taking longer than expected.
Any ideas or advice would be greatly apprecitaed.
Thanks, Tim.
From what you are saying it looks like these rows on the subscriber are read
only and the data flow is only from the publisher to the subscriber for this
table. If so remove this table from the merge publication, put it in a
transactional publication and do your deletes through a stored procedure and
replicate the execution of the stored procedure.
Hilary Cotter
Looking for a SQL Server replication book
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:%23kd7psfrFHA.3604@.tk2msftngp13.phx.gbl...
> Hi Guys,
> I need a little advice. I need to delete 250000 rows on a monthly basis
and
> my users are using MSDE 2000 with merge replication, publisher is SQL
Server
> 2000 Standard.
> The users will never be uploading or making changes to this table. Is
there
> any way i can make this a quick delete as currently for each row that is
in
> the Article published table sql is sending down a delete statement for
each
> row to the subscriber. Thus taking longer than expected.
> Any ideas or advice would be greatly apprecitaed.
> Thanks, Tim.
>
|||Hi Hilary,
Thanks for your response, this is not going to be possible as we use the
ActiveX control and the publications have been hard coded. Which i know is
not good. Is there any other way that we could perhaps specify the order
that things get run in I.E sp_AddScriptexec. I have worked a way out with
the system tables that we could remove generations. However i would need to
make sure that sp_addscriptexec was fired first in the replication process.
Thanks, Tim.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23boP2ShrFHA.1788@.tk2msftngp13.phx.gbl...
> From what you are saying it looks like these rows on the subscriber are
> read
> only and the data flow is only from the publisher to the subscriber for
> this
> table. If so remove this table from the merge publication, put it in a
> transactional publication and do your deletes through a stored procedure
> and
> replicate the execution of the stored procedure.
> --
> Hilary Cotter
> Looking for a SQL Server replication book
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
> news:%23kd7psfrFHA.3604@.tk2msftngp13.phx.gbl...
> and
> Server
> there
> in
> each
>
|||I think your best option would be to drop the subscription (possibly drop
the publication as well to drop the triggers which will slow the delete), do
the delete, and then recreate the publication and subscription and do a no
sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:eSsLlVhrFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi Hilary,
> Thanks for your response, this is not going to be possible as we use the
> ActiveX control and the publications have been hard coded. Which i know is
> not good. Is there any other way that we could perhaps specify the order
> that things get run in I.E sp_AddScriptexec. I have worked a way out with
> the system tables that we could remove generations. However i would need
to
> make sure that sp_addscriptexec was fired first in the replication
process.[vbcol=seagreen]
> Thanks, Tim.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23boP2ShrFHA.1788@.tk2msftngp13.phx.gbl...
is
>
|||Hi Hilary,
Thanks for that. What would happen to the information that was in the table
on the subscriber? Would that get deleted locally, as this information
would no longer be needed.
Again Thanks for all you help.
Tim.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23HJ2hOjrFHA.2592@.TK2MSFTNGP09.phx.gbl...
>I think your best option would be to drop the subscription (possibly drop
> the publication as well to drop the triggers which will slow the delete),
> do
> the delete, and then recreate the publication and subscription and do a no
> sync.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
> news:eSsLlVhrFHA.3720@.TK2MSFTNGP14.phx.gbl...
> to
> process.
> is
>
|||It would remain there. This is probably not what you want, so you would have
to delete on both sides.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:OBtaphsrFHA.240@.tk2msftngp13.phx.gbl...
> Hi Hilary,
> Thanks for that. What would happen to the information that was in the
table[vbcol=seagreen]
> on the subscriber? Would that get deleted locally, as this information
> would no longer be needed.
> Again Thanks for all you help.
> Tim.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23HJ2hOjrFHA.2592@.TK2MSFTNGP09.phx.gbl...
delete),[vbcol=seagreen]
no[vbcol=seagreen]
the[vbcol=seagreen]
order[vbcol=seagreen]
with[vbcol=seagreen]
need[vbcol=seagreen]
are[vbcol=seagreen]
for[vbcol=seagreen]
a[vbcol=seagreen]
SQL[vbcol=seagreen]
Is[vbcol=seagreen]
that[vbcol=seagreen]
for
>

Deleting Database users in 2005

We constantly import DB's from our different customers for research
issues to our server. In the past after the DB was restored I would
then go and delete the users from the old network to keep it clean but
now with 2005 it seems like there is no easy way of doing it in MS
management studio.
Deleting users froma DB in 2000 was very simple all I had to do was
highlight all the users in EM and then hit the delete key to delete all
the highlighted users. In 2005 I have not found a way to delete
multiple users at one shot, it seems like it requires you to delete one
user at a time. Is there any other way of doing it in MS management
studio?
Also when I restore a 2000 database from a different server on a 2005
server it automatically upgrades the DB to 2005, which is cool however
when I try to delete the orphan users that do not belong to the
original network from the restored DB on the 2005 server I get an
error "drop failed for user xxx", I then have to delete the schema for
that user before I can delete the user. In 2000, I was always able to
delete them with no issues.
I am sure many of you guys have run into this issue also and I was
wondering if there was a better way of doing it.
Any tips or knowhow in this issue will be greatly appreciated.
Thanks
The best way to drop multiple uses is with a script using the command drop
login XXX
I don't believe there is a way to do this with SSMS. I don't know of a way
of fixing the orphaned users through SSMS either.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shub" <shubtech@.gmail.com> wrote in message
news:1161102716.034164.30650@.e3g2000cwe.googlegrou ps.com...
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
> Any tips or knowhow in this issue will be greatly appreciated.
> Thanks
>
|||shub wrote:
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
Don't use the GUI to do this, write a script and save yourself some
pain...

> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
According to the Books Online entry for sp_dbcmptlevel:
"When a database is upgraded to SQL Server 2005 from any earlier
version of SQL Server, the database retains its existing compatibility
level. This applies to both system and user databases. Use
sp_dbcmptlevel to change the compatibility level of the database to 90.
To view the current compatibility level of a database, query the
compatibility_level column in the sys.databases catalog view."
Based on this, I would assume that if you restore a SQL 2000 database
backup using the RESTORE command, and not the GUI, your newly restored
database will remain in SQL 2000 compatibility mode.

> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
Nope, because I don't use the GUI. The "better way of doing it" is via
T-SQL commands and scripts.
sql

Deleting Database users in 2005

We constantly import DB's from our different customers for research
issues to our server. In the past after the DB was restored I would
then go and delete the users from the old network to keep it clean but
now with 2005 it seems like there is no easy way of doing it in MS
management studio.
Deleting users froma DB in 2000 was very simple all I had to do was
highlight all the users in EM and then hit the delete key to delete all
the highlighted users. In 2005 I have not found a way to delete
multiple users at one shot, it seems like it requires you to delete one
user at a time. Is there any other way of doing it in MS management
studio?
Also when I restore a 2000 database from a different server on a 2005
server it automatically upgrades the DB to 2005, which is cool however
when I try to delete the orphan users that do not belong to the
original network from the restored DB on the 2005 server I get an
error "drop failed for user xxx", I then have to delete the schema for
that user before I can delete the user. In 2000, I was always able to
delete them with no issues.
I am sure many of you guys have run into this issue also and I was
wondering if there was a better way of doing it.
Any tips or knowhow in this issue will be greatly appreciated.
ThanksThe best way to drop multiple uses is with a script using the command drop
login XXX
I don't believe there is a way to do this with SSMS. I don't know of a way
of fixing the orphaned users through SSMS either.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shub" <shubtech@.gmail.com> wrote in message
news:1161102716.034164.30650@.e3g2000cwe.googlegroups.com...
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
> Any tips or knowhow in this issue will be greatly appreciated.
> Thanks
>|||shub wrote:
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
Don't use the GUI to do this, write a script and save yourself some
pain...
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
According to the Books Online entry for sp_dbcmptlevel:
"When a database is upgraded to SQL Server 2005 from any earlier
version of SQL Server, the database retains its existing compatibility
level. This applies to both system and user databases. Use
sp_dbcmptlevel to change the compatibility level of the database to 90.
To view the current compatibility level of a database, query the
compatibility_level column in the sys.databases catalog view."
Based on this, I would assume that if you restore a SQL 2000 database
backup using the RESTORE command, and not the GUI, your newly restored
database will remain in SQL 2000 compatibility mode.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
Nope, because I don't use the GUI. The "better way of doing it" is via
T-SQL commands and scripts.

Deleting Database users in 2005

We constantly import DB's from our different customers for research
issues to our server. In the past after the DB was restored I would
then go and delete the users from the old network to keep it clean but
now with 2005 it seems like there is no easy way of doing it in MS
management studio.
Deleting users froma DB in 2000 was very simple all I had to do was
highlight all the users in EM and then hit the delete key to delete all
the highlighted users. In 2005 I have not found a way to delete
multiple users at one shot, it seems like it requires you to delete one
user at a time. Is there any other way of doing it in MS management
studio?
Also when I restore a 2000 database from a different server on a 2005
server it automatically upgrades the DB to 2005, which is cool however
when I try to delete the orphan users that do not belong to the
original network from the restored DB on the 2005 server I get an
error "drop failed for user xxx", I then have to delete the schema for
that user before I can delete the user. In 2000, I was always able to
delete them with no issues.
I am sure many of you guys have run into this issue also and I was
wondering if there was a better way of doing it.
Any tips or knowhow in this issue will be greatly appreciated.
ThanksThe best way to drop multiple uses is with a script using the command drop
login XXX
I don't believe there is a way to do this with SSMS. I don't know of a way
of fixing the orphaned users through SSMS either.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shub" <shubtech@.gmail.com> wrote in message
news:1161102716.034164.30650@.e3g2000cwe.googlegroups.com...
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
> Any tips or knowhow in this issue will be greatly appreciated.
> Thanks
>|||shub wrote:
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
Don't use the GUI to do this, write a script and save yourself some
pain...

> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
According to the Books Online entry for sp_dbcmptlevel:
"When a database is upgraded to SQL Server 2005 from any earlier
version of SQL Server, the database retains its existing compatibility
level. This applies to both system and user databases. Use
sp_dbcmptlevel to change the compatibility level of the database to 90.
To view the current compatibility level of a database, query the
compatibility_level column in the sys.databases catalog view."
Based on this, I would assume that if you restore a SQL 2000 database
backup using the RESTORE command, and not the GUI, your newly restored
database will remain in SQL 2000 compatibility mode.

> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
Nope, because I don't use the GUI. The "better way of doing it" is via
T-SQL commands and scripts.

Deleteing large bulks of data

Brief background:

We are using SQL Server 2000, and one of the tables stores user
sessions details (each time our users logs into our system we insert a
new record in the session table, and each time user logs out from our
system we insert another record in the same table).
SESSION_ID is the primary key and it is clustered index.
The system produces 5 million session records/day.

The problem:

Each day we transfer the session data (delta only) to other machine and
we want to delete bulk of ~5 million sessions. This should happend
without any interfering of our customers activity ( in the same time,
we should not block the table - new sessions should be created).

What is the best way to perform such task ?generally truncate table xxx ...will be MUCH faster than delete. Be
aware of some of the logging issues associated with truncate table
before you do this. Search your BOL for "truncate table".

MJKulangara
http://sqladventures.blogspot.com|||Truncating table will delete the entire table, and this is not what we
want. We are looing for a method to delete specific sessions (by
specifying the exact sessions ID's).|||rosherman@.hotmail.com (rosherman@.hotmail.com) writes:
> We are using SQL Server 2000, and one of the tables stores user
> sessions details (each time our users logs into our system we insert a
> new record in the session table, and each time user logs out from our
> system we insert another record in the same table).
> SESSION_ID is the primary key and it is clustered index.
> The system produces 5 million session records/day.
> The problem:
> Each day we transfer the session data (delta only) to other machine and
> we want to delete bulk of ~5 million sessions. This should happend
> without any interfering of our customers activity ( in the same time,
> we should not block the table - new sessions should be created).
> What is the best way to perform such task ?

If I understand this correctly, you want to delete the main bulk of the
five million rows, but keep some of them.

I would consider doing something like:

1) Rename the table.
2) Create a new table with the same schema.
3) Insert the rows you want to keep from the old table to the new table.
4) Drop the old table.

You would need to put 1) and 2) into a transaction. During this
transactions logins would be blocked, but it would be a matter of
centiseconds.

If you can find a method to define a clean cut a head, then you could
consider partitioned views. That is, you would have a set of table
that are united in a view, and a CHECK constraint defining which
intervals that go into which table. Insertions would be into the view.
You would transfer one table a time, and then truncate and finally
redefine it to fit another slot in the future.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks. We'll try this method.

Delete user

SQL 2005. and SQL Management studio I have a user 'myuser' in a SQL DB
'mydatabase' under security users. When I try to delete him, I get error
says DB principal owns schema and cant be dropped. I try to remove his role
as DBOwner and it says login name must be specified. (The window above has
his username shown but the loginname field is blank and greyed out/
disabled. I added him to the overall sql DB users / security and it allowed
me to. When I tried to map him to the 'mydatabase' it says 'myuser' already
exists. How can I get rid of this user in the 'mydatabase' DB. I have other
users in the DB that I can delete.
Thanks
bucThe key seems to be that the user owns a schema. See what schema the user ow
ns (schema folder) and
either drop the schema (if empty) or change the owner of the schema to someb
ody else.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<Buc> wrote in message news:OpjsV6ceGHA.1456@.TK2MSFTNGP04.phx.gbl...
> SQL 2005. and SQL Management studio I have a user 'myuser' in a SQL DB 'my
database' under security
> users. When I try to delete him, I get error says DB principal owns schema
and cant be dropped. I
> try to remove his role as DBOwner and it says login name must be specified
. (The window above has
> his username shown but the loginname field is blank and greyed out/ disabl
ed. I added him to the
> overall sql DB users / security and it allowed me to. When I tried to map
him to the 'mydatabase'
> it says 'myuser' already exists. How can I get rid of this user in the 'my
database' DB. I have
> other users in the DB that I can delete.
> Thanks
> buc
>|||Make sure there are no objects owned by this user. If there are any, you
should change the owner and then you should be able to delete this user from
the database.
Anith|||That worked, he had a entry under the schema folder which had zero objects.
I deleted his entry and it worked. Thanks
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:e52E89ceGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Make sure there are no objects owned by this user. If there are any, you
> should change the owner and then you should be able to delete this user
> from the database.
> --
> Anith
>

Delete statement of one table is blocking all other users

Hi,
I want to execute a delete statement for one table , which is very common
and so many tables refers to it
when the delete is hapening all the users will be blocked and the delete
never happens
Is there any way that I can delete rows without blocking other users ??
regards
Hi sarajuma !
You could use a more granualar lock like ROWLOCK specified after the
Tablename in the Delete Statement. This will produce more overhead but
could solve you problem in some ways.
DELETE FROM SomeTable WITH (ROWLOCK)
HTH, Jens Suessmeyer.
|||Jens wrote:
>Hi sarajuma !
>You could use a more granualar lock like ROWLOCK specified after the
>Tablename in the Delete Statement. This will produce more overhead but
>could solve you problem in some ways.
>DELETE FROM SomeTable WITH (ROWLOCK)
>HTH, Jens Suessmeyer.
Dear ,
thanks for your reply , but still other users are locked although I 've added
WITH (ROWLOCK)
to the delete statement .
Is there anythingelse I need to do ?
regards
|||Hi
You have to have a WHERE condition in your DELETE statement . Also you can
remove all indexes defined on the table and re-create them after deletion.
"sarajuma" <u16630@.uwe> wrote in message news:58c357579b93e@.uwe...
> Jens wrote:
>
> Dear ,
> thanks for your reply , but still other users are locked although I 've
> added
> WITH (ROWLOCK)
> to the delete statement .
> Is there anythingelse I need to do ?
> regards
|||Dear,
I already have a where clause
and I can't remove the indexes , since I want to execute this in the
program frequently
regards
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>You have to have a WHERE condition in your DELETE statement . Also you can
>remove all indexes defined on the table and re-create them after deletion.
>[quoted text clipped - 14 lines]
|||Well, so do you see that an optimizer is available to use the index
"sarajuma" <u16630@.uwe> wrote in message news:58c386580ef4a@.uwe...[vbcol=seagreen]
> Dear,
> I already have a where clause
> and I can't remove the indexes , since I want to execute this in the
> program frequently
> regards
> Uri Dimant wrote:
|||I would also try to look at the statstics of this query, how much
impact it has on the logical / physical reads, and (like another poster
stated before) if it uses the indexes to execute the query.
HTH, Jens Suessmeyer.
|||In addition to the other posts: Are you saying that you have foreign key referring to this table? If
so, did you create indexes over those foreign key columns in the other tables?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c30c2d37b52@.uwe...
> Hi,
> I want to execute a delete statement for one table , which is very common
> and so many tables refers to it
> when the delete is hapening all the users will be blocked and the delete
> never happens
>
> Is there any way that I can delete rows without blocking other users ??
> regards
|||Dear ,
yes , and so I'm in a loop , many users are locked
regards
Tibor Karaszi wrote:[vbcol=seagreen]
>In addition to the other posts: Are you saying that you have foreign key referring to this table? If
>so, did you create indexes over those foreign key columns in the other tables?
>[quoted text clipped - 6 lines]
|||So do you have indexes on your foreign key columns? This can help delete and update on the
referenced table a *lot*.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c446123256a@.uwe...[vbcol=seagreen]
> Dear ,
> yes , and so I'm in a loop , many users are locked
> regards
> Tibor Karaszi wrote:

Delete statement of one table is blocking all other users

Hi,
I want to execute a delete statement for one table , which is very common
and so many tables refers to it
when the delete is hapening all the users will be blocked and the delete
never happens
Is there any way that I can delete rows without blocking other users '?
regardsHi sarajuma !
You could use a more granualar lock like ROWLOCK specified after the
Tablename in the Delete Statement. This will produce more overhead but
could solve you problem in some ways.
DELETE FROM SomeTable WITH (ROWLOCK)
HTH, Jens Suessmeyer.|||Jens wrote:
>Hi sarajuma !
>You could use a more granualar lock like ROWLOCK specified after the
>Tablename in the Delete Statement. This will produce more overhead but
>could solve you problem in some ways.
>DELETE FROM SomeTable WITH (ROWLOCK)
>HTH, Jens Suessmeyer.
Dear ,
thanks for your reply , but still other users are locked although I 've added
WITH (ROWLOCK)
to the delete statement .
Is there anythingelse I need to do ?
regards|||Hi
You have to have a WHERE condition in your DELETE statement . Also you can
remove all indexes defined on the table and re-create them after deletion.
"sarajuma" <u16630@.uwe> wrote in message news:58c357579b93e@.uwe...
> Jens wrote:
>>Hi sarajuma !
>>You could use a more granualar lock like ROWLOCK specified after the
>>Tablename in the Delete Statement. This will produce more overhead but
>>could solve you problem in some ways.
>>DELETE FROM SomeTable WITH (ROWLOCK)
>>HTH, Jens Suessmeyer.
>
> Dear ,
> thanks for your reply , but still other users are locked although I 've
> added
> WITH (ROWLOCK)
> to the delete statement .
> Is there anythingelse I need to do ?
> regards|||Dear,
I already have a where clause
and I can't remove the indexes , since I want to execute this in the
program frequently
regards
Uri Dimant wrote:
>Hi
>You have to have a WHERE condition in your DELETE statement . Also you can
>remove all indexes defined on the table and re-create them after deletion.
>>Hi sarajuma !
>[quoted text clipped - 14 lines]
>> regards|||Well, so do you see that an optimizer is available to use the index
"sarajuma" <u16630@.uwe> wrote in message news:58c386580ef4a@.uwe...
> Dear,
> I already have a where clause
> and I can't remove the indexes , since I want to execute this in the
> program frequently
> regards
> Uri Dimant wrote:
>>Hi
>>You have to have a WHERE condition in your DELETE statement . Also you can
>>remove all indexes defined on the table and re-create them after
>>deletion.
>>Hi sarajuma !
>>[quoted text clipped - 14 lines]
>> regards|||I would also try to look at the statstics of this query, how much
impact it has on the logical / physical reads, and (like another poster
stated before) if it uses the indexes to execute the query.
HTH, Jens Suessmeyer.|||In addition to the other posts: Are you saying that you have foreign key referring to this table? If
so, did you create indexes over those foreign key columns in the other tables?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c30c2d37b52@.uwe...
> Hi,
> I want to execute a delete statement for one table , which is very common
> and so many tables refers to it
> when the delete is hapening all the users will be blocked and the delete
> never happens
>
> Is there any way that I can delete rows without blocking other users '?
> regards|||Dear ,
yes , and so I'm in a loop , many users are locked
regards
Tibor Karaszi wrote:
>In addition to the other posts: Are you saying that you have foreign key referring to this table? If
>so, did you create indexes over those foreign key columns in the other tables?
>> Hi,
>[quoted text clipped - 6 lines]
>> regards|||So do you have indexes on your foreign key columns? This can help delete and update on the
referenced table a *lot*.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c446123256a@.uwe...
> Dear ,
> yes , and so I'm in a loop , many users are locked
> regards
> Tibor Karaszi wrote:
>>In addition to the other posts: Are you saying that you have foreign key referring to this table?
>>If
>>so, did you create indexes over those foreign key columns in the other tables?
>> Hi,
>>[quoted text clipped - 6 lines]
>> regards|||are there any indexed views involved?|||On Tue, 13 Dec 2005 07:26:42 GMT, "sarajuma" <u16630@.uwe> wrote:
>I want to execute a delete statement for one table , which is very common
>and so many tables refers to it
>when the delete is hapening all the users will be blocked and the delete
>never happens
>Is there any way that I can delete rows without blocking other users '?
One would expect it to complete.
Can you show us the SQL for the delete, and/or the table declaration?
Are there any joins involved in the delete?
J.

Delete statement of one table is blocking all other users

Hi,
I want to execute a delete statement for one table , which is very common
and so many tables refers to it
when the delete is hapening all the users will be blocked and the delete
never happens
Is there any way that I can delete rows without blocking other users '?
regardsHi sarajuma !
You could use a more granualar lock like ROWLOCK specified after the
Tablename in the Delete Statement. This will produce more overhead but
could solve you problem in some ways.
DELETE FROM SomeTable WITH (ROWLOCK)
HTH, Jens Suessmeyer.|||Jens wrote:
>Hi sarajuma !
>You could use a more granualar lock like ROWLOCK specified after the
>Tablename in the Delete Statement. This will produce more overhead but
>could solve you problem in some ways.
>DELETE FROM SomeTable WITH (ROWLOCK)
>HTH, Jens Suessmeyer.
Dear ,
thanks for your reply , but still other users are locked although I 've adde
d
WITH (ROWLOCK)
to the delete statement .
Is there anythingelse I need to do ?
regards|||Hi
You have to have a WHERE condition in your DELETE statement . Also you can
remove all indexes defined on the table and re-create them after deletion.
"sarajuma" <u16630@.uwe> wrote in message news:58c357579b93e@.uwe...
> Jens wrote:
>
> Dear ,
> thanks for your reply , but still other users are locked although I 've
> added
> WITH (ROWLOCK)
> to the delete statement .
> Is there anythingelse I need to do ?
> regards|||Dear,
I already have a where clause
and I can't remove the indexes , since I want to execute this in the
program frequently
regards
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>You have to have a WHERE condition in your DELETE statement . Also you can
>remove all indexes defined on the table and re-create them after deletion.
>
>[quoted text clipped - 14 lines]|||Well, so do you see that an optimizer is available to use the index
"sarajuma" <u16630@.uwe> wrote in message news:58c386580ef4a@.uwe...[vbcol=seagreen]
> Dear,
> I already have a where clause
> and I can't remove the indexes , since I want to execute this in the
> program frequently
> regards
> Uri Dimant wrote:|||I would also try to look at the statstics of this query, how much
impact it has on the logical / physical reads, and (like another poster
stated before) if it uses the indexes to execute the query.
HTH, Jens Suessmeyer.|||In addition to the other posts: Are you saying that you have foreign key ref
erring to this table? If
so, did you create indexes over those foreign key columns in the other table
s?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c30c2d37b52@.uwe...
> Hi,
> I want to execute a delete statement for one table , which is very common
> and so many tables refers to it
> when the delete is hapening all the users will be blocked and the delete
> never happens
>
> Is there any way that I can delete rows without blocking other users '?
> regards|||Dear ,
yes , and so I'm in a loop , many users are locked
regards
Tibor Karaszi wrote:[vbcol=seagreen]
>In addition to the other posts: Are you saying that you have foreign key re
ferring to this table? If
>so, did you create indexes over those foreign key columns in the other tabl
es?
>
>[quoted text clipped - 6 lines]|||So do you have indexes on your foreign key columns? This can help delete and
update on the
referenced table a *lot*.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sarajuma" <u16630@.uwe> wrote in message news:58c446123256a@.uwe...[vbcol=seagreen]
> Dear ,
> yes , and so I'm in a loop , many users are locked
> regards
> Tibor Karaszi wrote: