Removal of superflous replication data

Hi,
Ive got a server which replicates an awful lot of constantly changing data - the db itself could be around 2gb in size, however with the replication data in it it has ballooned to 6gb. On top of this, there is all the data in the D:\MSSQL\REPLDATA\unc\ directories which seem to represent another log of some description.

Can I delete this data? I cant see what it would be used for and it totals some 25Gb! Obviously I cannot have this lying around on my server.

Does anyone know what it is used for?
thanks
Pete StoreyThe REPLDATA directory contains snapshot data for the replication. Each time you initialize the subscription, replication agent will write data onto this directory. Yes, you can delete them. But I think your replication setup is incorrect because sql delte those data right after the refresh is complete.|||Hi!

This depends on the type of MSSQL server you use. In MSSQL 7 there's unfortunately no standard functionality which cleans up these replication logings (automatically).
I seem to remember though u can find a Stored Procedure example on the Microsoft Website which does just that...removing older loggings from the Replication loging table.

A crude method would be of course to delete certain publications with the Replication-menu option in Enterprise manager and then go to the subscribing sql server with (for example Query analyzer) go into the database which 'receives' the replicated data and use the sp_deletemergeconflictrow or better yet the sp_mergesubscription_cleanup Stored Procedure to clean up some of the database.

I'm sorry I can't be more specific since it's been quite a while since I had to look into this problem.
Good luck anyway!

Vincent JS|||FYI is SQL Server 2000 SP4.
Cant really be bothered to mess around manually with it all - Im not too impressed overall with replication because it seems to have so many problems with it and things that simply should have been sorted but never were.
Hmm anyone else got any ideas how to delete the data from the replication tables?
Thanks joe for the advice will get rid of that data now
cheers
Pete|||I agree with Joe, check your configuration. Replication might be failing when generating these files - which in that case they would not be deleted.|||Originally posted by rnealejr
I agree with Joe, check your configuration. Replication might be failing when generating these files - which in that case they would not be deleted.

The snapshots appear to be generated correctly and I dont get any errors coming through so not sure what might be wrong with the config.
Any possible pointers?
thanks
Pete

Removal of merge replication.

I've got this SQL2K/WIN2K setup and a database having merge replication
setup. It's been running there for more than couple of years. Now due to
strategic changes we don't need this replication on the database.
When I tried to remove it, it started blocking normal user activity that is
mostly on the same tables which are published. After 15-20 minutes I had to
cancle it. The database is serving 24/7 and I want to remove replication with
minimal disruption to regular opration. Is there any easier way to remove
replication and metadata generated by it.?
I would appreciate any help/pointers.
Have you tried to call sp_removedbreplication? This proc should remove the
replication from the database.
Yi
"adi" <adi@.discussions.microsoft.com> wrote in message
news:7B04463C-FFA2-4BEA-8339-EB3340FEE5A2@.microsoft.com...
> I've got this SQL2K/WIN2K setup and a database having merge replication
> setup. It's been running there for more than couple of years. Now due to
> strategic changes we don't need this replication on the database.
> When I tried to remove it, it started blocking normal user activity that
> is
> mostly on the same tables which are published. After 15-20 minutes I had
> to
> cancle it. The database is serving 24/7 and I want to remove replication
> with
> minimal disruption to regular opration. Is there any easier way to remove
> replication and metadata generated by it.?
> I would appreciate any help/pointers.

Remote Server question

sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisRThis is a multi-part message in MIME format.
--090909090705070004050507
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Is it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
>sql2k sp3
>
>Ive always used Linked Servers and as such dont really know about Remote
>Servers. Anyways, here is the deal.
>Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
>is a Subscriber to Box1.
>So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
>as SA. But I get the message:
>Server: Msg 18483, Level 14, State 1, Line 1
>Could not connect to server 'BLA'because 'sa' is not defined as a remote
>login at the server.
>The SA password is the same on both these boxes, so while logged in as SA I:
>
>sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
> , @.useself = 'true'
>but that doesnt make my problem go away. The really funny thing here is that
>I googled it, saw that I posted the same question years ago, see that I got
>it, but cant read the answer.
>TIA, ChrisR
>
>
>
--090909090705070004050507
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Is it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
ChrisR wrote:
<blockquote cite="mid%230BGYuarFHA.3424@.TK2MSFTNGP14.phx.gbl"
type="cite">
<pre wrap="">sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR
</pre>
</blockquote>
</body>
</html>
--090909090705070004050507--|||This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C5AE09.DF950070
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thanks Mike, but no thats not the case.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:O$vQwAdrFHA.908@.tk2msftngp13.phx.gbl...
Is it possible that your remote server is set up to use Windows =Authentication only? (Even though you can set the 'sa' password, that =doesn't imply you can actually login using that standard SQL login.)
--
mike hodgson
blog: http://sqlnerd.blogspot.com=20
ChrisR wrote: sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote =
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, =which is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while =logged in as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote =
login at the server.
The SA password is the same on both these boxes, so while logged in as =SA I:
sp_addlinkedsrvlogin @.rmtsrvname =3D 'cpdbovs01'
, @.useself =3D 'true'
but that doesnt make my problem go away. The really funny thing here is =that I googled it, saw that I posted the same question years ago, see that I =got it, but cant read the answer.
TIA, ChrisR
--=_NextPart_000_0008_01C5AE09.DF950070
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks Mike, but no thats not the case.
"Mike Hodgson" wrote in message news:O$vQwAdrFHA.908@.t=k2msftngp13.phx.gbl...Is it possible that your remote server is set up to use Windows =Authentication only? (Even though you can set the 'sa' password, that doesn't =imply you can actually login using that standard SQL login.)
--mike =hodgsonblog: http://sqlnerd.blogspot.com ChrisR wrote: sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote =Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, =which is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while =logged in as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote =login at the server.
The SA password is the same on both these boxes, so while logged in as =SA I:
sp_addlinkedsrvlogin @.rmtsrvname =3D 'cpdbovs01'
, @.useself =3D 'true'
but that doesnt make my problem go away. The really funny thing here is =that I googled it, saw that I posted the same question years ago, see that I =got it, but cant read the answer.
TIA, ChrisR

---

--=_NextPart_000_0008_01C5AE09.DF950070--

Remote scan on linked server

I've got an interesting situation. I have two SQL 2000 servers and a proc on
one that updates data on the other. If I use literal values in the where
clause of the update statement then the query plan uses a "remote query" to
do the update. If I use variables then it uses a "remote scan" and filters
locally. Obviously that takes too long.
Here is some sample script.
-- This uses a remote query
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = 'ROB'
WHERE OrderNumber = 12345
-- This uses a remote scan
DECLARE @.Salesperson CHAR(3)
DECLARE @.OrderNumber INT
SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
-- This uses a remote query; same table and index structure; linked server
defined the same
UPDATE [Server3].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
I can't figure out any reason why it does it that way. To make things more
odd, that proc updates a couple other servers in the same way for the same
table and those use remote queries. The linked servers are defined the same,
the table structure and indexes are the same.
Does anyone have any idea why it would work this way?
Thanks.
Dale.
Is it possible the datatypes of those declared variables are not equal to
the corresponding data types on the linked server? In any case you should
try using OPENQUERY() as it will pass thru all queries so they are always
interpreted on the other side just as you wrote them with no odbc
intervention.
Andrew J. Kelly SQL MVP
"Dale M." <DaleM@.discussions.microsoft.com> wrote in message
news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> I've got an interesting situation. I have two SQL 2000 servers and a proc
> on
> one that updates data on the other. If I use literal values in the where
> clause of the update statement then the query plan uses a "remote query"
> to
> do the update. If I use variables then it uses a "remote scan" and
> filters
> locally. Obviously that takes too long.
> Here is some sample script.
> -- This uses a remote query
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = 'ROB'
> WHERE OrderNumber = 12345
> -- This uses a remote scan
> DECLARE @.Salesperson CHAR(3)
> DECLARE @.OrderNumber INT
> SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> -- This uses a remote query; same table and index structure; linked server
> defined the same
> UPDATE [Server3].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> I can't figure out any reason why it does it that way. To make things
> more
> odd, that proc updates a couple other servers in the same way for the same
> table and those use remote queries. The linked servers are defined the
> same,
> the table structure and indexes are the same.
> Does anyone have any idea why it would work this way?
> Thanks.
> Dale.
>
|||Thanks for the reply.
The data types are the same.
I've tried several variations of OPENQUERY syntax but it doesn't like
variables in its parameters, so I'm not sure how to get the update done.
Plus, I'd sorta like to know why anyways.
"Andrew J. Kelly" wrote:

> Is it possible the datatypes of those declared variables are not equal to
> the corresponding data types on the linked server? In any case you should
> try using OPENQUERY() as it will pass thru all queries so they are always
> interpreted on the other side just as you wrote them with no odbc
> intervention.
> --
> Andrew J. Kelly SQL MVP
> "Dale M." <DaleM@.discussions.microsoft.com> wrote in message
> news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
>
>

Remote MS Sql Server

Hi collegues,
I've to managing some "sqlserver server" on shared hosting on CrystalTech
structure.
Now I use "enterprise manager 2000" but it's very slow in every operations
also If I've a 1Mbit DSL, and I see all db in every server, not only mine
db.
Are there some other tools to manage remotly sql server, more fast?
Thanks
Merlinox (Italy)
http://merlinox.splinder.it
http://www.merlinox.tk (per l'Aido)
Merlinox (Italy)
http://merlinox.splinder.it
http://www.merlinox.tk (per l'Aido)
> Are there some other tools to manage remotly sql server, more fast?
Query Analyzer
|||But to use query analyzer I've to manage all with T-SQL.
There aren't visual tools.
Right?
On Tue, 27 Apr 2004 17:20:29 +0300, Bojidar Alexandrov
<bojo_do_not_spam@.kodar.net> wrote:

> Query Analyzer
>
Merlinox (Italy)
http://merlinox.splinder.it
http://www.merlinox.tk (per l'Aido)
|||Merlinox typed:

> Are there some other tools to manage remotly sql server, more fast?
Have a look to myLittleAdmin For MS SQL
More info on http://www.myLittleTools.net/mla_sql
Live demo on http://www.myLittleTools.net/livedemo/mla_sql
More products available on
http://www.microsoft.com/sql/msde/partners/default.asp
Best regards
Elian Chrebor
// myLittleTools.net : leading provider of web-based applications.
// myLittleAdmin : online MS SQL manager
// http://www.mylittletools.net
// webmaster@.mylittletools.net
|||Do you tested it?
I see may download lite version.
Can't testing full version?
On Tue, 27 Apr 2004 16:33:08 +0200, el.c. - myLittleTools.net
<NOwebmasterSPAM@.mylittletools.net> wrote:

> Merlinox typed:
>
> Have a look to myLittleAdmin For MS SQL
> More info on http://www.myLittleTools.net/mla_sql
> Live demo on http://www.myLittleTools.net/livedemo/mla_sql
> More products available on
> http://www.microsoft.com/sql/msde/partners/default.asp
> Best regards
> Elian Chrebor
>
> --
> // myLittleTools.net : leading provider of web-based applications.
> // myLittleAdmin : online MS SQL manager
> // http://www.mylittletools.net
> // webmaster@.mylittletools.net
>
Merlinox (Italy)
http://merlinox.splinder.it
http://www.merlinox.tk (per l'Aido)
|||You can try SQLExecMS,
It is designed to be fast. Full version is available for download.
JS
"Merlinox" <merlinox@.dontspamhotmail.com> wrote in message
news:opr64b6au8zpbug4@.msnews.microsoft.com...
> Hi collegues,
> I've to managing some "sqlserver server" on shared hosting on CrystalTech
> structure.
> Now I use "enterprise manager 2000" but it's very slow in every operations
> also If I've a 1Mbit DSL, and I see all db in every server, not only mine
> db.
> Are there some other tools to manage remotly sql server, more fast?
> Thanks
> --
> Merlinox (Italy)
> http://merlinox.splinder.it
> http://www.merlinox.tk (per l'Aido)
> --
> Merlinox (Italy)
> http://merlinox.splinder.it
> http://www.merlinox.tk (per l'Aido)
|||Thank you John.
Just I 've 2 minutes I'll try it.
I hope is this:
http://www.laplas-soft.com/
On Tue, 27 Apr 2004 20:23:26 -0400, John Smith <wp456@.mail.ru> wrote:

> You can try SQLExecMS,
> It is designed to be fast. Full version is available for download.
> JS
>
> "Merlinox" <merlinox@.dontspamhotmail.com> wrote in message
> news:opr64b6au8zpbug4@.msnews.microsoft.com...
>
Merlinox (Italy)
http://merlinox.splinder.it
http://www.merlinox.tk (per l'Aido)
|||I try it.
It seems very fast, but there aren't any wizard to create objects.
It's like query analyzer.
And so, it isn't free.
Thank you
On Tue, 27 Apr 2004 20:23:26 -0400, John Smith <wp456@.mail.ru> wrote:

> You can try SQLExecMS,
> It is designed to be fast. Full version is available for download.
> JS
>
> "Merlinox" <merlinox@.dontspamhotmail.com> wrote in message
> news:opr64b6au8zpbug4@.msnews.microsoft.com...
>
Merlinox (Italy)
http://merlinox.splinder.it
http://www.merlinox.tk (per l'Aido)
|||Merlinox typed:
> Do you tested it?
> I see may download lite version.
> Can't testing full version?
The full version can be tested online in the live demo section.
Best regards
Elian Chrebor
// myLittleTools.net : leading provider of web-based applications.
// myLittleAdmin : online MS SQL manager
// http://www.mylittletools.net
// webmaster@.mylittletools.net

Remote Database

Dear ALL,
I've a customer of mine asked me for a web hosting , and internet site
contains a SQL database.
It seems to be very simple but customer wants database at his office, so my
problem is what is the most suitable solution to make communicate web and
database remotely in order to accomplish security requirements ?
Is it enough SQL SP3 installed and tcp 1433 opened or I have to establish a
VPN for example ?
Regards
Alberto
Brivio
Alberto Brivio
(tel. 1 9 9 2 4 9 8 9 8)
Exposing SQL Server on the net is not a good idea at all, but your customer
has at least three options to secure it:
- Using IP filtering rules to prevent connects from addresses other than web
server's address. He also generally need to implement data encryption using
SSL to do not expose plain data (and espesially SQL passwords!) to public
network during transfer. The simplest method but doesn't implement caller
authentication
- Using IPSec to encrypt/filter and authenticate such connections
- Using VPN which also doing authentication and encryption
WBR, Evergray
Words mean nothing...
"Alberto Brivio" <a.brivio@.adb.it> wrote in message
news:uX5MAIMMGHA.2992@.tk2msftngp13.phx.gbl...
> Dear ALL,
> I've a customer of mine asked me for a web hosting , and internet site
> contains a SQL database.
> It seems to be very simple but customer wants database at his office, so
> my problem is what is the most suitable solution to make communicate web
> and database remotely in order to accomplish security requirements ?
> Is it enough SQL SP3 installed and tcp 1433 opened or I have to establish
> a VPN for example ?
>
> Regards
> Alberto
> Brivio
> --
>
> Alberto Brivio
> (tel. 1 9 9 2 4 9 8 9 8)
>

Remote Connection problems, I've read many a thread on this and nothing has worked!

Hi, I am trying to enable remote access on a SQL Server 2005 standard edition instance. I have enabled it in the properties using the Management studio and have enabled it under Sql Server Surface Area Configuration under "Database engine".

But it didn't work. I know for a fact that my connection string is correct (I tested it on the actual machine)

So I checked everything again and I noticed in the Sql Server Surface Area under "Analysis Services" there is another "remote connection" checkbox to be enabled, however it won't let me enable it there because it says the "Sql Browser" is not started. I checked and the Sql Browser is started.

I'm thinking maybe the problem there is that I have an instance of SQL 2000 running and perhaps it is the sql browser for that (I can remotely connect ot my SQL 2000 instance).

I checked under Computer management -> services and their is no SQL Browser for the instance of SQL 2005. Not even a thing to enable. Just the Default instance SQL Browser, which is for my SQL 2000 instance. Am I missing something here?

Also there is no SQL Browser to enable in the management or Surface Area Configuration programs.

I have also tried to connect using the default port number (1433) and the IP (to bypass the need for the SQL Browser). It apparently finds my Server 2000 instance because it doesn't accept my user name and password and connot find the specific database (note: I verified the username and pswd are correct by testing the values on the SQL machine). How do I find what port number a specific instance of SQL Server is running on?

Hi,

the fact that you are using the default instance of SQL Server 2000 on your computer and that you can connect to the SQL Server 2k with the default settings (1433) indicates that SQL Server 2l5 is installed on another port than 1433, because ports can′t be shared. So try to see on which port you installed SQL Server 2k and on therefore on which port it is listening. Then specify the port within your connection string with the following syntax:

MachineName\instanceName,Portnumber

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de
|||

I've tried figuring out what port the SQL 2005 is running on by running a port scan, but nothing really jumped out at me other than the 1433, which the SQL 2000 is running on.

What is a good way to find out what port the SQL 2005 is running on?

|||

Hi,

you can have a look at:

http://support.microsoft.com/default.aspx?scid=kb;en-us;823938

The port which SQL Server is listening to can be retrieved via looking in the eventlog or using the statement xp_readerrorlog which shows up something like "SQL Server listening on xxx.xxx.xxx.xxx:PortNumber"

HTH, Jens Suessmeyer.

http//www.sqlserver2005.de