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
Showing posts with label removal. Show all posts
Showing posts with label removal. Show all posts
Monday, March 26, 2012
Removal of SQL 6.5 after upgrading to 7.
I have recently upgraded a SQL Server 6.5 server to 7.0. I am planning to use
the Supplied kill6x.exe utility. As I have not used this utility before I
just wanted to check that i does not remove the folders that contain the SQL
6.5 database device files, as this is where the sql 7 mdf files are. They are
not in the default path.
Hi
It leaves data files and directories intact, but, always run this after you
have a good backup of the data and server.
"Russell" wrote:
> I have recently upgraded a SQL Server 6.5 server to 7.0. I am planning to use
> the Supplied kill6x.exe utility. As I have not used this utility before I
> just wanted to check that i does not remove the folders that contain the SQL
> 6.5 database device files, as this is where the sql 7 mdf files are. They are
> not in the default path.
the Supplied kill6x.exe utility. As I have not used this utility before I
just wanted to check that i does not remove the folders that contain the SQL
6.5 database device files, as this is where the sql 7 mdf files are. They are
not in the default path.
Hi
It leaves data files and directories intact, but, always run this after you
have a good backup of the data and server.
"Russell" wrote:
> I have recently upgraded a SQL Server 6.5 server to 7.0. I am planning to use
> the Supplied kill6x.exe utility. As I have not used this utility before I
> just wanted to check that i does not remove the folders that contain the SQL
> 6.5 database device files, as this is where the sql 7 mdf files are. They are
> not in the default path.
Removal of SQL 6.5 after upgrading to 7.
I have recently upgraded a SQL Server 6.5 server to 7.0. I am planning to use
the Supplied kill6x.exe utility. As I have not used this utility before I
just wanted to check that i does not remove the folders that contain the SQL
6.5 database device files, as this is where the sql 7 mdf files are. They are
not in the default path.Hi
It leaves data files and directories intact, but, always run this after you
have a good backup of the data and server.
"Russell" wrote:
> I have recently upgraded a SQL Server 6.5 server to 7.0. I am planning to use
> the Supplied kill6x.exe utility. As I have not used this utility before I
> just wanted to check that i does not remove the folders that contain the SQL
> 6.5 database device files, as this is where the sql 7 mdf files are. They are
> not in the default path.
the Supplied kill6x.exe utility. As I have not used this utility before I
just wanted to check that i does not remove the folders that contain the SQL
6.5 database device files, as this is where the sql 7 mdf files are. They are
not in the default path.Hi
It leaves data files and directories intact, but, always run this after you
have a good backup of the data and server.
"Russell" wrote:
> I have recently upgraded a SQL Server 6.5 server to 7.0. I am planning to use
> the Supplied kill6x.exe utility. As I have not used this utility before I
> just wanted to check that i does not remove the folders that contain the SQL
> 6.5 database device files, as this is where the sql 7 mdf files are. They are
> not in the default path.
Removal of punctuation from fields
Hey,
I require ALL the punctuation in a field to be removed. By all punctuation,
i mean \ / ' - ( ) * &^ % $ ￡ " ! etc etc.
i know i could always do a 'REPLACE' on these and just replace them with
blank spaces, but just wondering if theres any function that would allow me
to remove all the punctuation, as i dont really want to do a replace on ever
y
bit of punctuation.
Cheers
Rob.Hi
SELECT * INTO #temp
FROM
(
SELECT ' ', SPACE(1) UNION
SELECT '?', SPACE(0) UNION
SELECT '!', SPACE(0) UNION
SELECT '#', SPACE(0) UNION
SELECT '*', SPACE(0)
......
......
) AS a (Dirty, Good)
DECLARE @.String VARCHAR(50)
SELECT @.String = 'We? are* the #world!'
UPDATE #temp
SET @.String = REPLACE(@.String, Dirty, Good )
WHERE CHARINDEX(Dirty , @.String) > 0
PRINT @.String
"MACNR" <MACNR@.discussions.microsoft.com> wrote in message
news:B60345AB-9347-40A3-895F-CB4FA37EC0D3@.microsoft.com...
> Hey,
> I require ALL the punctuation in a field to be removed. By all
> punctuation,
> i mean \ / ' - ( ) * &^ % $ " ! etc etc.
> i know i could always do a 'REPLACE' on these and just replace them with
> blank spaces, but just wondering if theres any function that would allow
> me
> to remove all the punctuation, as i dont really want to do a replace on
> every
> bit of punctuation.
> Cheers
> Rob.sql
I require ALL the punctuation in a field to be removed. By all punctuation,
i mean \ / ' - ( ) * &^ % $ ￡ " ! etc etc.
i know i could always do a 'REPLACE' on these and just replace them with
blank spaces, but just wondering if theres any function that would allow me
to remove all the punctuation, as i dont really want to do a replace on ever
y
bit of punctuation.
Cheers
Rob.Hi
SELECT * INTO #temp
FROM
(
SELECT ' ', SPACE(1) UNION
SELECT '?', SPACE(0) UNION
SELECT '!', SPACE(0) UNION
SELECT '#', SPACE(0) UNION
SELECT '*', SPACE(0)
......
......
) AS a (Dirty, Good)
DECLARE @.String VARCHAR(50)
SELECT @.String = 'We? are* the #world!'
UPDATE #temp
SET @.String = REPLACE(@.String, Dirty, Good )
WHERE CHARINDEX(Dirty , @.String) > 0
PRINT @.String
"MACNR" <MACNR@.discussions.microsoft.com> wrote in message
news:B60345AB-9347-40A3-895F-CB4FA37EC0D3@.microsoft.com...
> Hey,
> I require ALL the punctuation in a field to be removed. By all
> punctuation,
> i mean \ / ' - ( ) * &^ % $ " ! etc etc.
> i know i could always do a 'REPLACE' on these and just replace them with
> blank spaces, but just wondering if theres any function that would allow
> me
> to remove all the punctuation, as i dont really want to do a replace on
> every
> bit of punctuation.
> Cheers
> Rob.sql
removal of merge subscriptions
I try to restore a set of replicas where the main server has been stolen.
Fortunately I have the databases at another server but when i try to push a
merge replication to a new main server i can not create a merge publication
beacuse the database has merge subscriptions that are anonymous or that use
the priority of the publisher to solve conflicts. Drop...
the problem is that I can not drop the subscriptions. If I delete it in
enterprise manager I still get the same error messages. I also used the
sp_dropmergesubscription and sp_mergesubscription_cleanup but that does not
help. Is there anyone who got any idea?
Thanks for your help in advance
Mats
Best wishes
Was the stolen server a publisher?
"Mats" wrote:
> I try to restore a set of replicas where the main server has been stolen.
> Fortunately I have the databases at another server but when i try to push a
> merge replication to a new main server i can not create a merge publication
> beacuse the database has merge subscriptions that are anonymous or that use
> the priority of the publisher to solve conflicts. Drop...
> the problem is that I can not drop the subscriptions. If I delete it in
> enterprise manager I still get the same error messages. I also used the
> sp_dropmergesubscription and sp_mergesubscription_cleanup but that does not
> help. Is there anyone who got any idea?
> Thanks for your help in advance
> Mats
> --
> Best wishes
|||Yes it was!
"Jim Breffni" wrote:
[vbcol=seagreen]
> Was the stolen server a publisher?
>
> "Mats" wrote:
|||If you have no other publishers you must drop all subscriptions.
Backup a subscription database.
Restore subscription database to NEW publisher.
Re-create publication.
Re-create Subscriptions
Note: If you think your subscriptions contain slightly different data then
you will need to manually sync these up to the publication before creating
the new subscriptions.
DON'T FORGET TO MAKE A BACKUP OF ANY DATABASES BEFORE MAKING ANY CHANGES!
Hilary also posted a link that helps to remove subsciptions
You
"Mats" wrote:
[vbcol=seagreen]
> Yes it was!
> "Jim Breffni" wrote:
|||Thanks for your answer:
Could you please tell me where is the link you mentioned
Mats
"Jim Breffni" wrote:
[vbcol=seagreen]
> If you have no other publishers you must drop all subscriptions.
> Backup a subscription database.
> Restore subscription database to NEW publisher.
> Re-create publication.
> Re-create Subscriptions
> Note: If you think your subscriptions contain slightly different data then
> you will need to manually sync these up to the publication before creating
> the new subscriptions.
> DON'T FORGET TO MAKE A BACKUP OF ANY DATABASES BEFORE MAKING ANY CHANGES!
> Hilary also posted a link that helps to remove subsciptions
> You
> "Mats" wrote:
|||On 2004-12-07, Mats <Mats@.discussions.microsoft.com> wrote:
> Thanks for your answer:
> Could you please tell me where is the link you mentioned
>
Can you please post the resolution of your problem, sucssesfull or not?
I had similair situation where I needed to set up replication between
publisher and subscriber where subscriber was previousley subscribed to
another publisher. I needed to issue sp_dropsubscriber at the subscriber (or
some other stored procedure, can't find it now) befure I was able to push
the snapshots to those subscribers.
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||Here you go...
http://groups-beta.google.com/group/...a?dmode=source
"Mats" wrote:
[vbcol=seagreen]
> Thanks for your answer:
> Could you please tell me where is the link you mentioned
> Mats
> "Jim Breffni" wrote:
|||I thank you very much for your effort! But when i run the script it works OK.
When I then try to create a merge publication I get an error 208 when
creating the articles which means that a reference is missing. Any ideas?
Mats
"Jim Breffni" wrote:
[vbcol=seagreen]
> Here you go...
> http://groups-beta.google.com/group/...a?dmode=source
>
>
> "Mats" wrote:
Fortunately I have the databases at another server but when i try to push a
merge replication to a new main server i can not create a merge publication
beacuse the database has merge subscriptions that are anonymous or that use
the priority of the publisher to solve conflicts. Drop...
the problem is that I can not drop the subscriptions. If I delete it in
enterprise manager I still get the same error messages. I also used the
sp_dropmergesubscription and sp_mergesubscription_cleanup but that does not
help. Is there anyone who got any idea?
Thanks for your help in advance
Mats
Best wishes
Was the stolen server a publisher?
"Mats" wrote:
> I try to restore a set of replicas where the main server has been stolen.
> Fortunately I have the databases at another server but when i try to push a
> merge replication to a new main server i can not create a merge publication
> beacuse the database has merge subscriptions that are anonymous or that use
> the priority of the publisher to solve conflicts. Drop...
> the problem is that I can not drop the subscriptions. If I delete it in
> enterprise manager I still get the same error messages. I also used the
> sp_dropmergesubscription and sp_mergesubscription_cleanup but that does not
> help. Is there anyone who got any idea?
> Thanks for your help in advance
> Mats
> --
> Best wishes
|||Yes it was!
"Jim Breffni" wrote:
[vbcol=seagreen]
> Was the stolen server a publisher?
>
> "Mats" wrote:
|||If you have no other publishers you must drop all subscriptions.
Backup a subscription database.
Restore subscription database to NEW publisher.
Re-create publication.
Re-create Subscriptions
Note: If you think your subscriptions contain slightly different data then
you will need to manually sync these up to the publication before creating
the new subscriptions.
DON'T FORGET TO MAKE A BACKUP OF ANY DATABASES BEFORE MAKING ANY CHANGES!
Hilary also posted a link that helps to remove subsciptions
You
"Mats" wrote:
[vbcol=seagreen]
> Yes it was!
> "Jim Breffni" wrote:
|||Thanks for your answer:
Could you please tell me where is the link you mentioned
Mats
"Jim Breffni" wrote:
[vbcol=seagreen]
> If you have no other publishers you must drop all subscriptions.
> Backup a subscription database.
> Restore subscription database to NEW publisher.
> Re-create publication.
> Re-create Subscriptions
> Note: If you think your subscriptions contain slightly different data then
> you will need to manually sync these up to the publication before creating
> the new subscriptions.
> DON'T FORGET TO MAKE A BACKUP OF ANY DATABASES BEFORE MAKING ANY CHANGES!
> Hilary also posted a link that helps to remove subsciptions
> You
> "Mats" wrote:
|||On 2004-12-07, Mats <Mats@.discussions.microsoft.com> wrote:
> Thanks for your answer:
> Could you please tell me where is the link you mentioned
>
Can you please post the resolution of your problem, sucssesfull or not?
I had similair situation where I needed to set up replication between
publisher and subscriber where subscriber was previousley subscribed to
another publisher. I needed to issue sp_dropsubscriber at the subscriber (or
some other stored procedure, can't find it now) befure I was able to push
the snapshots to those subscribers.
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||Here you go...
http://groups-beta.google.com/group/...a?dmode=source
"Mats" wrote:
[vbcol=seagreen]
> Thanks for your answer:
> Could you please tell me where is the link you mentioned
> Mats
> "Jim Breffni" wrote:
|||I thank you very much for your effort! But when i run the script it works OK.
When I then try to create a merge publication I get an error 208 when
creating the articles which means that a reference is missing. Any ideas?
Mats
"Jim Breffni" wrote:
[vbcol=seagreen]
> Here you go...
> http://groups-beta.google.com/group/...a?dmode=source
>
>
> "Mats" wrote:
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.
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.
Removal of Dimension Name from Data Sample Viewer
Hi All,
I have created a DrillThrough Action in my cubes. When I right-clicked on the data cell and click the Drillthrough action, the Data Sample Viewer pop-up. The Data Sample Viewer always contain a $ sign and the dimension name. Is there a way to remove the dimension name?
Thanks
Chen Khoon Seah
What application you are using to browse your cube?
Try installing beta of Office 2007 and trying invoking your drillthough action in Excel.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Removal from sys.transmission_queues on exceeding LIFETIME
Hello,
We needed to detach a database that contained a target ServiceBroker service and wanted to do this without impacting the rest of our system. The idea was the ServiceBroker would enqueue message to this service to be pickedup when the database was re-attached. Our sequence to do this was:
1. Disable the queue on the target service forcing ServiceBroker to queue in the initating side transmission queue.
2. Detach the database.
3. Re-attach the database.
4. Re-enable the queue
Messages sent to the service have a specified LIFETIME. What appeared to happen was that messages were being stored in the transmission queue as expected but on hiting the LIFETIME period they were being removed from the transmission queue and were therfore "lost".
Has anyone else experienced this or can anyone suggest what we are doing wrong.
Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)
Any suggestions gratefully recieved.
We have now removed the LIFETIME property from our dialogs in order to reduce the risk of this happening and it all seems fine. My only concern now is that the LIFETIME EXCEEDED message is a Broker error, so does this mean that the same will hold true for all Broker errors that are generated whilst a message is in the transmission queue?|||Prior to SP2 when receiving an Error or EndDialog message the sys.transmission_queue was drained for the that dialog. The idea was that applications that needed to look at the messages sent in case of error whould use queue RETENTION = ON. With SP2 we introduced a change in which the messages stay in sys.transmission_queue but ar enot being attempted to delivery and they are deleted only after the applications issues the END CONVERSATION.
So yes, any error message will cause the behavior your observed. Either use retention to save the messages sent or upgrade to SP2.
|||
Ian Mitchen wrote:
Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)
Also restore of a backup will disable the broker. SSB is intedend primarily for distributed applications and one has to consider the problems that can happen when only one side of the conversation is moved (i.e. database is detached/attached): routes have to be fixed, endpoint security has to be set up again for the new host etc. When a backup is restored, the situation is more dramatical, as the peer might be in a more advanced stage of a conversation (i.e. restorer db has next sequence number on a conversation N, but peer is already expecting N+2). In this case the only solution is to error the conversation.
For these considerations the restore and attach operations disable the service broker in the database. One has to inspect the database, ensure is in a consistent state with it's peers and then enable it.
|||Thanks for a great response, that's filled another gap in my knowledge.
Cheers,
Ian
Friday, March 23, 2012
Removal from sys.transmission_queues on exceeding LIFETIME
Hello,
We needed to detach a database that contained a target ServiceBroker service and wanted to do this without impacting the rest of our system. The idea was the ServiceBroker would enqueue message to this service to be pickedup when the database was re-attached. Our sequence to do this was:
1. Disable the queue on the target service forcing ServiceBroker to queue in the initating side transmission queue.
2. Detach the database.
3. Re-attach the database.
4. Re-enable the queue
Messages sent to the service have a specified LIFETIME. What appeared to happen was that messages were being stored in the transmission queue as expected but on hiting the LIFETIME period they were being removed from the transmission queue and were therfore "lost".
Has anyone else experienced this or can anyone suggest what we are doing wrong.
Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)
Any suggestions gratefully recieved.
We have now removed the LIFETIME property from our dialogs in order to reduce the risk of this happening and it all seems fine. My only concern now is that the LIFETIME EXCEEDED message is a Broker error, so does this mean that the same will hold true for all Broker errors that are generated whilst a message is in the transmission queue?|||Prior to SP2 when receiving an Error or EndDialog message the sys.transmission_queue was drained for the that dialog. The idea was that applications that needed to look at the messages sent in case of error whould use queue RETENTION = ON. With SP2 we introduced a change in which the messages stay in sys.transmission_queue but ar enot being attempted to delivery and they are deleted only after the applications issues the END CONVERSATION.
So yes, any error message will cause the behavior your observed. Either use retention to save the messages sent or upgrade to SP2.
|||
Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)
Also restore of a backup will disable the broker. SSB is intedend primarily for distributed applications and one has to consider the problems that can happen when only one side of the conversation is moved (i.e. database is detached/attached): routes have to be fixed, endpoint security has to be set up again for the new host etc. When a backup is restored, the situation is more dramatical, as the peer might be in a more advanced stage of a conversation (i.e. restorer db has next sequence number on a conversation N, but peer is already expecting N+2). In this case the only solution is to error the conversation.
For these considerations the restore and attach operations disable the service broker in the database. One has to inspect the database, ensure is in a consistent state with it's peers and then enable it.
|||Thanks for a great response, that's filled another gap in my knowledge.
Cheers,
Ian
sql
Subscribe to:
Posts (Atom)