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
>

DELETE rows in MSDE

Hi..

DELETE FROM table1 WHERE projektID=5

there are 500000 rows that has projektID=5.. and when i run the query the hardrive is working for a couple of minutes and then stops. and NOTHING has happened. not a single row has been deleted?.. cant the DELETE statement handle that many rows or?. or is there another way i can delete these rows?.yeah, it can handle that. I've deleted more than 500,000 rows before (sometimes by accident!)

DELETE table1 WHERE projektID = 5

are you running this from query analyzer or from ASP.NET?|||im running it from asp.net, there is no query analyser for MSDE.
but it doesnt happen anything when do it, except for my harddrive gets occupied for 10 min or so.|||> there is no query analyser for MSDE.

sort of. MSDE being the SQL engine, you can connect to it with SQL Server client tools, and many people do.

do me a favour. install the 180 day SQL Server trial - client tools only. then run it through QA

Delete Replication per TSQL

Hello,
i use MSDE and try to setup a merge replication. I want to drop the
replicated db, but get some error. Sorry for the german error msg:
1> sp_helpdistributor
2> go
distributor
distribution database
directory
account
min distrib retention
max distrib retention history retention history cleanup agent
distribution cleanup agent
rpc server name
rpc login name
---
---
---
---
---
--- --
-- --
--
---
---
distrib
D:\Program Files\MSSQL\Data\MSSQL$SUDSQLDB\RepData
LocalSystem
0
72 48 Agentverlaufscleanup:
distrib
Verteilungscleanup: distrib
repl_distributor
distributor_admin
1> sp_helpserver
2> go
name
network_name status
id
collation_name
connect_timeout query_timeout
---
-- --
---
-- --
rpc,rpc out,use remote collation
0
NULL
0 0
repl_distributor
rpc,dist,rpc out,system,use remote collation
1
NULL
0 0
1> sp_dropdistributor
2> go
Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
sp_dropdistributor, Zeile 150
Der Verteiler 'XXX' konnte nicht gelscht werden. Dieser Verteiler
besitzt zugeordnete Verteilungsdatenbanken.
1> sp_dropdistributiondb distrib
2> go
Meldung 14120, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistributiondb, Zeile 49
Die distrib-Verteilungsdatenbank konnte nicht gelscht werden. Diese
Verteilerdatenbank ist einem Verleger zugeordnet.
1> sp_dropdistpublisher
2> go
Meldung 201, Ebene 16, Status 3, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistpublisher, Zeile 0
Die sp_dropdistpublisher-Prozedur erwartet den @.publisher-Parameter;
dieser wurde nicht bergeben.
1> sp_dropdistpublisher 'BW1H049C\SUDSQLDB'
2> go
Meldung 21033, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistpublisher, Zeile 99
Der Server 'BW1H049C\SUDSQLDB' kann nicht als Verteilungsverleger
gelscht werden, da auf diesem Server Datenbanken fr die Replikation
aktiviert
sind.
1> sp_dropmergepublication @.publication=N'TTPMerge'
2> go
Meldung 21147, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_MSCheckmergereplication, Zeile 12
Die master-Datenbank wurde nicht fr die Mergereplikation publiziert.
1> sp_helpmergepublication
2> go
How can i delete safely all replication entries so i can drop the db ?
Are there any docs beside BOL to Replication per TSQL ? I see the book
of Hilary and quick check the testchapter , are there more chapter
about replication with TSQL ?
Thx Baumgart
right click on your publication and select delete. Then go to Tools,
Replication, configure distributor, publishers, and subscribers and select
the publication databases tab. Uncheck the sahred databse you wish to drop.
Accept the defaults. Then you should be able to drop the database.
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
"Alexander Baumgart" <al.baumgart@.gmx.de> wrote in message
news:d586cm$g87$1@.mail1.sbs.de...
> Hello,
> i use MSDE and try to setup a merge replication. I want to drop the
> replicated db, but get some error. Sorry for the german error msg:
> 1> sp_helpdistributor
> 2> go
> distributor
> distribution database
> directory
>
> account
>
> min distrib retention
> max distrib retention history retention history cleanup agent
> distribution cleanup agent
> rpc server name
> rpc login name
>
>
>
>
> --- --
> -- --
> --
> --
>
> distrib
> D:\Program Files\MSSQL\Data\MSSQL$SUDSQLDB\RepData
>
> LocalSystem
>
> 0
> 72 48 Agentverlaufscleanup:
> distrib
> Verteilungscleanup: distrib
> repl_distributor
> distributor_admin
> 1> sp_helpserver
> 2> go
> name
> network_name status
> id
> collation_name
> connect_timeout query_timeout
> --
> -- --
> -- --
> rpc,rpc out,use remote collation
> 0
> NULL
> 0 0
> repl_distributor
> rpc,dist,rpc out,system,use remote collation
> 1
> NULL
> 0 0
> 1> sp_dropdistributor
> 2> go
> Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
> sp_dropdistributor, Zeile 150
> Der Verteiler 'XXX' konnte nicht gelscht werden. Dieser Verteiler
> besitzt zugeordnete Verteilungsdatenbanken.
> 1> sp_dropdistributiondb distrib
> 2> go
> Meldung 14120, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistributiondb, Zeile 49
> Die distrib-Verteilungsdatenbank konnte nicht gelscht werden. Diese
> Verteilerdatenbank ist einem Verleger zugeordnet.
> 1> sp_dropdistpublisher
> 2> go
> Meldung 201, Ebene 16, Status 3, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistpublisher, Zeile 0
> Die sp_dropdistpublisher-Prozedur erwartet den @.publisher-Parameter;
> dieser wurde nicht bergeben.
> 1> sp_dropdistpublisher 'BW1H049C\SUDSQLDB'
> 2> go
> Meldung 21033, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistpublisher, Zeile 99
> Der Server 'BW1H049C\SUDSQLDB' kann nicht als Verteilungsverleger
> gelscht werden, da auf diesem Server Datenbanken fr die Replikation
> aktiviert
> sind.
> 1> sp_dropmergepublication @.publication=N'TTPMerge'
> 2> go
> Meldung 21147, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_MSCheckmergereplication, Zeile 12
> Die master-Datenbank wurde nicht fr die Mergereplikation publiziert.
> 1> sp_helpmergepublication
> 2> go
> How can i delete safely all replication entries so i can drop the db ?
> Are there any docs beside BOL to Replication per TSQL ? I see the book
> of Hilary and quick check the testchapter , are there more chapter
> about replication with TSQL ?
> Thx Baumgart
|||Hilary Cotter wrote:
+AD4- right click on your publication and select delete. Then go to
Tools,
+AD4- Replication, configure distributor, publishers, and subscribers
and
+AD4- select the publication databases tab. Uncheck the sahred databse
you
+AD4- wish to drop.
+AD4-
+AD4- Accept the defaults. Then you should be able to drop the database.
I didnt have any tools , like i told +ADs-(( Only MSDE. I set all per
SP ,
and tried the reverse way to drop the replication but that didnt work.
What i tried, error msg got translate by me from german to english:
a) 1+AD4- sp+AF8-dropmergepublication +AEA-publication+AD0-N'TTPMerge'
Meldung 21147, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-MSCheckmergereplication, Zeile 12
Die master-Datenbank wurde nicht f+APw-r die Mergereplikation
publiziert.
(the master database didnt get shared per mergereplication)
b) 1+AD4- sp+AF8-dropdistpublisher 'BW1H049C+AFw-SUDSQLDB'
2+AD4- go
Meldung 21033, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-dropdistpublisher, Zeile 99
Der Server 'xxx+AFw-SUDSQLDB' kann nicht als Verteilungsverleger
gel+APY-scht werden, da auf diesem Server Datenbanken f+APw-r die
Replikation
aktiviert sind.
(the server xxx+AFw-instancename couldnt be delete as distributor,
there are database active for replication)
c) 1+AD4- sp+AF8-dropdistributiondb distrib
2+AD4- go
Meldung 14120, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-dropdistributiondb, Zeile 49
Die distrib-Verteilungsdatenbank konnte nicht gel+APY-scht werden. Diese
Verteilerdatenbank ist einem Verleger zugeordnet.
(the distrib - distributiondb couldnt be deleted, its assigned to a
distributor)
d) 1+AD4- sp+AF8-dropdistributor
2+AD4- go
Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
sp+AF8-dropdistributor, Zeile 150
Der Verteiler 'XXX' konnte nicht gel+APY-scht werden. Dieser Verteiler
besitzt zugeordnete Verteilungsdatenbanken.
(the distributor +ACI-xxx+AFw-instancename+ACI- couldnt be deleted ,
its assigned with distribution-databases)
so how can i delete a merge-replication per osql.exe (TSQL) ?
Thx for your help Hilary , i will see if i can use the GUI way to find
the right SP's to delete the merge replication.
|||Hilary Cotter wrote:
+AD4- right click on your publication and select delete. Then go to
Tools,
+AD4- Replication, configure distributor, publishers, and subscribers
and
+AD4- select the publication databases tab. Uncheck the sahred databse
you
+AD4- wish to drop.
+AD4-
+AD4- Accept the defaults. Then you should be able to drop the database.
Hello,
after 2 day of work i was able to drop the merge replication. The
important cmd was sp+AF8-removedbreplication, as mentioned in
http://support.microsoft.com/default...en-us+ADs-3244
01
I check your book specially the example chapter where u described a way
to drop a replication, that also didnt work for me, because there was a
replication active. How deep u discusss manual setup of replication in
your book (in the example chapter u say more in chapter 6) ?
thx Baumgart