Showing posts with label subscriber. Show all posts
Showing posts with label subscriber. Show all posts

Monday, March 26, 2012

msreplication_queue table growing on subscriber - why

Have a live and standby server and failed over to the standby after (i
thought) using EM to turn off replication.
Now find out that the ms_replication_table on the subscriber is growing -
what have I missed ?
The very least I need to do is remove/delete this table as I now don't have
enough disk space for a backup and currently no tape drive
Suggestions please
Thanks
Luci
I suppose the question is "do you need to send this data back to the
publisher?". If so, then running the queue reader agent will prepare the
table for truncation. If you aren't interested in this data, then you'll need
to drop the subscription. If the publisher is no longer available, then you
can use sp_removedbreplication to remove this table and any other remnants of
replication.
Cheers,
Paul Ibison
|||Thanks for quick reply Paul.
No we don't need this data (we're not replicating at the moment) not
even sure where its coming from as the original live is not in use !
Tried running sp_removedbreplication on the subscriber yesterday but the
transaction log just doubled ( and we have no space).
To stop it growing then, do I just go on the original publisher and just
drop the subscription - presume thats a an option in EM ? or do I have to run
a stored procedure on there ?
Cheers
Luci
"Paul Ibison" wrote:

> I suppose the question is "do you need to send this data back to the
> publisher?". If so, then running the queue reader agent will prepare the
> table for truncation. If you aren't interested in this data, then you'll need
> to drop the subscription. If the publisher is no longer available, then you
> can use sp_removedbreplication to remove this table and any other remnants of
> replication.
> Cheers,
> Paul Ibison
|||Hi Luci,
I was suggesting sp_removedbreplication for the case where the publisher was
not available, but this should still be ok. If you run sp_dropesubscription
on the publisher we should be back to normal. The balooning of the
transaction log on the subscriber is normal when you are deleting so much
data. You might want to backup the log and then shrink it, or if the log is
not required for the backup strategy, you could use simple recovery mode and
then shring the log file.
HTH,
Paul Ibison
|||Thanks again Paul
Plan to do this tonight out of hours tonight, going with your second
suggestion by
putting db in simple mode and trying sp_removedbreplication on the
subscriber first.
Cheers
Luci
PS Glad you answered as I found this forum through your replicationanswers
website Thank you again !
"Paul Ibison" wrote:

> Hi Luci,
> I was suggesting sp_removedbreplication for the case where the publisher was
> not available, but this should still be ok. If you run sp_dropesubscription
> on the publisher we should be back to normal. The balooning of the
> transaction log on the subscriber is normal when you are deleting so much
> data. You might want to backup the log and then shrink it, or if the log is
> not required for the backup strategy, you could use simple recovery mode and
> then shring the log file.
> HTH,
> Paul Ibison
>
|||What I'd probably try is to delete the queue records in batches. The batch
size could be 10000 and each time round the loop you backup the log with
truncate_only set. this way the records will get removed without balooning
the log, and you can drop the subscription at the end...
HTH,
Paul Ibison
|||Hi
Just to let you know we got there in the end.
Ended up truncating the msreplication_queue_table, then tried
sp_removedbreplication again which failed as it couldn't drop tables and the
tempdb couldn't extend. So, manually dropped the tables in question, after
which
the sp_removedbreplication did work. Thank you for your help. Cheers Luci
"Paul Ibison" wrote:

> What I'd probably try is to delete the queue records in batches. The batch
> size could be 10000 and each time round the loop you backup the log with
> truncate_only set. this way the records will get removed without balooning
> the log, and you can drop the subscription at the end...
> HTH,
> Paul Ibison
>

MSreplication_queue getting dropped in SQL 2005

I have set up an Updateable Subscriber replication between databases A and B.
This creates and uses the MSreplication_queue system table in the destination
(B) database.
I have lot of other tables being replicated from A to B, all standard
Transactional replication. e.g. Table C is replication from A to B,
transaction replication. When I dropped the publication for table C from A,
it dropped the following objects in B as well:
MSreplication_queue
MSrepl_queuedtraninfo
This caused the Updateable Subscription to fail. I got around this by
creating another (dummy) Updateable Subscriber replication which recreated
the above tables, and then dropped the dummy publication.
This sounds like a serious bug, because why would dropping a simple
transactional replication drop these two tables, especially since dropping an
UPDATEABLE SUBSCRIBER replication doesn't touch these tables AND a
transactional publication has nothing to do with these tables ?
We did not experience this problem in SQL 2000.
Has anyone else come across this problem ? Is there a fix for it, as it's
causes a serious problem is our organisation because a critical process
depends on the updateable subsciption working.
Yes this seems to be an issue with the way the subscription is dropped,
please open a case with PSS so that we can look into this
"Pranil" wrote:

> I have set up an Updateable Subscriber replication between databases A and B.
> This creates and uses the MSreplication_queue system table in the destination
> (B) database.
> I have lot of other tables being replicated from A to B, all standard
> Transactional replication. e.g. Table C is replication from A to B,
> transaction replication. When I dropped the publication for table C from A,
> it dropped the following objects in B as well:
> MSreplication_queue
> MSrepl_queuedtraninfo
> This caused the Updateable Subscription to fail. I got around this by
> creating another (dummy) Updateable Subscriber replication which recreated
> the above tables, and then dropped the dummy publication.
> This sounds like a serious bug, because why would dropping a simple
> transactional replication drop these two tables, especially since dropping an
> UPDATEABLE SUBSCRIBER replication doesn't touch these tables AND a
> transactional publication has nothing to do with these tables ?
> We did not experience this problem in SQL 2000.
> Has anyone else come across this problem ? Is there a fix for it, as it's
> causes a serious problem is our organisation because a critical process
> depends on the updateable subsciption working.

MSrepl_commands question

Hi,
I have 750,000 commands in the table MSrepl_commands. The
cleanup agent hasn't removed them, although they have all
gone to my one subscriber, and I don't have anonymous
subscribers. What am I missing??
Rgds,
BB
You should be able to delete them manually. You will have to delete them
from the msrepl_transactions and msrepl_commands tables.
Before you do this, I would try to run the clean up manually.
EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 0
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:2a4401c4a54e$21714500$a501280a@.phx.gbl...
> Hi,
> I have 750,000 commands in the table MSrepl_commands. The
> cleanup agent hasn't removed them, although they have all
> gone to my one subscriber, and I don't have anonymous
> subscribers. What am I missing??
> Rgds,
> BB
|||Thanks Hilary,
running
EXEC dbo.sp_MSdistribution_cleanup @.min_distretention =
0, @.max_distretention = 0
has me worried - what if another command comes in just
before I run it - presumably it will be deleted?
Also, have you ever seen this behaviour before - ie
commands retained in this table? Any idea what causes it?
Thanks,
BB
|||only commands that are replicated to the subscriber will be deleted.
Undistributed commands will not.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:43d401c4a55b$1749b550$a401280a@.phx.gbl...
> Thanks Hilary,
> running
> EXEC dbo.sp_MSdistribution_cleanup @.min_distretention =
> 0, @.max_distretention = 0
> has me worried - what if another command comes in just
> before I run it - presumably it will be deleted?
> Also, have you ever seen this behaviour before - ie
> commands retained in this table? Any idea what causes it?
> Thanks,
> BB
>
|||btw you can always pipe the msrepl_commands and msrepl_transaction tables to
backup tables before you do the deletes or manually run the clean up
command.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:43d401c4a55b$1749b550$a401280a@.phx.gbl...
> Thanks Hilary,
> running
> EXEC dbo.sp_MSdistribution_cleanup @.min_distretention =
> 0, @.max_distretention = 0
> has me worried - what if another command comes in just
> before I run it - presumably it will be deleted?
> Also, have you ever seen this behaviour before - ie
> commands retained in this table? Any idea what causes it?
> Thanks,
> BB
>
|||Thanks Hilary - any idea why it happens?
TIA,
BB
|||Sometimes the distribution clean up agent gets locked and is picked as the
dead lock victim. This normally leads to even more locking as the cleanup
agent has to do more work. You can try to run your distribution agent at a
greater frequency, ie every minute, or stop your log reader and distribution
agent and then run it.
On high volume systems when you constantly get this type or error you might
consider a remote distributor.
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:2bed01c4a564$10f7bb80$a501280a@.phx.gbl...
> Thanks Hilary - any idea why it happens?
> TIA,
> BB
|||I think you would be best to use a maxium_retention period of 24. Paul
Ibison pointed out to me that a value of 0 will remove undistributed
transactions.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:43d401c4a55b$1749b550$a401280a@.phx.gbl...
> Thanks Hilary,
> running
> EXEC dbo.sp_MSdistribution_cleanup @.min_distretention =
> 0, @.max_distretention = 0
> has me worried - what if another command comes in just
> before I run it - presumably it will be deleted?
> Also, have you ever seen this behaviour before - ie
> commands retained in this table? Any idea what causes it?
> Thanks,
> BB
>
sql

Friday, March 23, 2012

MSMerge_tombstone cleanup

Hi to everybody.
Situation:
1) merge replica
2) Two publications with many subscribers (about 60 for each publication).
3) One subscriber has a monodirectional agent (data only from publisher to
subscriber)
4) In this subscriber I delete data older than one month
5) MSMerge_Tombstone of this subscriber is very huge (more than 10 million
of records)
How can I clean these data? Can I use 'DELETE FROM MSmerge_tombstone' in the
subscriber?
Thanks for your answers and help.
Marco
Marco,
sounds like sp_mergecleanupmetadata should fix this. Have a look in BOL.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Monday, February 20, 2012

MSDN SQL 2005 versions

I'm a MSDN subscriber and recently installed SQL 2005 developer edition and
noticed it installed a version of visual studio. I'd prefer to install VS
pro edition seperate from SQL 2005. Should I install SQL 2005 Standard
version instead of developer edition and then install Visual Studio Pro
edition?
I couldn't find in the SQL 2005 developer edition install options where to
choose not to install the visual studio version.If you do not want the VS part to be installed, during installation, you can
opt out of installing the Business Intelligence Development Studio. But, you
can install this becuase it gives you access to SSIS, SQLCLR, Analysis
Services projects. Once you install the VS 2005 version, you can use either
of them to do your work.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%23Mc5DHY8FHA.4076@.tk2msftngp13.phx.gbl...
> I'm a MSDN subscriber and recently installed SQL 2005 developer edition
> and noticed it installed a version of visual studio. I'd prefer to install
> VS pro edition seperate from SQL 2005. Should I install SQL 2005 Standard
> version instead of developer edition and then install Visual Studio Pro
> edition?
> I couldn't find in the SQL 2005 developer edition install options where to
> choose not to install the visual studio version.
>|||"SriSamp" <ssampath@.sct.co.in> wrote in
news:ucxBvVY8FHA.3952@.TK2MSFTNGP12.phx.gbl:

> If you do not want the VS part to be installed, during installation,
> you can opt out of installing the Business Intelligence Development
> Studio. But, you can install this becuase it gives you access to SSIS,
> SQLCLR, Analysis Services projects.
Actually, I do not believe the VS isntalled with SQL Server gives you
access to the SQLCLR projects - I'm not 100% though.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels Berglund <nielsb@.develop.com> wrote in
news:Xns9719520FAE996nielsbdevelopcom@.20
7.46.248.16:

> Actually, I do not believe the VS isntalled with SQL Server gives you
> access to the SQLCLR projects - I'm not 100% though.
I am. You cannot open SQLCLR projects with BIDS.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Sorry about that. Since what SQL Server installs is anyways BI related, I
guess SQLCLR is not allowed.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns971961B226E4Dolekristianbangaas@.
207.46.248.16...
> Niels Berglund <nielsb@.develop.com> wrote in
> news:Xns9719520FAE996nielsbdevelopcom@.20
7.46.248.16:
>
> I am. You cannot open SQLCLR projects with BIDS.
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||If I install VS 2005 1st, will SQL 2005 pick up on that and not install it's
VS version?
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:ucxBvVY8FHA.3952@.TK2MSFTNGP12.phx.gbl...
> If you do not want the VS part to be installed, during installation, you
> can opt out of installing the Business Intelligence Development Studio.
> But, you can install this becuase it gives you access to SSIS, SQLCLR,
> Analysis Services projects. Once you install the VS 2005 version, you can
> use either of them to do your work.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:%23Mc5DHY8FHA.4076@.tk2msftngp13.phx.gbl...
>|||"Scott" <sbailey@.mileslumber.com> wrote in message
news:uCaM73d8FHA.2644@.TK2MSFTNGP10.phx.gbl...
> If I install VS 2005 1st, will SQL 2005 pick up on that and not install
> it's VS version?
>
Yes, and vice versa. Both products share the VS shell and just install
different components.
David