Showing posts with label commands. Show all posts
Showing posts with label commands. Show all posts

Monday, March 26, 2012

msrepl_transactions

Hassan,
if you have anonymous subscribers or if not all of your
subscribers have synchronized, the commands will stay
there until the cleanup agent runs and the retention
period is reached.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I have SQL subscribers .. How do I know if they are setup as anonymous ?
Also all my subscribers are uptodate and why do these tables have
commands/transactions in them that are 2 days old . Its definitely cleaning
up cos today when i looked, the min entry_time has increased since yesterday
but still around 52 hrs behind. This behaviour is only on one distributor..
The other is fine and deletes data that has been subscribed
Please advice..
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:170101c4e8d4$919bbcb0$a501280a@.phx.gbl...
> Hassan,
> if you have anonymous subscribers or if not all of your
> subscribers have synchronized, the commands will stay
> there until the cleanup agent runs and the retention
> period is reached.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||sp_helpsubscription will return the subscription type to
determine if it is anonymous.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

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

Monday, March 12, 2012

Msg 208 / Temp Tables

I'm Using SQL 2000.
I issue the following commands in the Query Analyzer:
Select * from Prod into #Temp1
Insert into #Temp1 Select * from OldProd
I Get the following error:
Server: Msg 208, Level 16, State 1, Line 121
Invalid object name '#Temp1'.
Any ideas?
Thanks,
KFTo create the table using SELECT...INTO the syntax should be:
Select * into #Temp1 from Prod
Then run
Insert into #Temp1 Select * from OldProd
- Vishal|||Below executes OK on my machine...
Select * into #Temp1 from authors
Insert into #Temp1 Select * from authors
If you post a repro which we can execute, we might be able to help...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Kflash" <kflash@.ameriflashtech.com> wrote in message
news:1f36401c389c5$9b9bfdc0$a601280a@.phx.gbl...
> I know. I got the syntax wrong on the original post but
> its right in the Query analyzer.
> When I run these commands:
> Select * into #Temp1 from Prod
> Insert into #Temp1 Select * from OldProd
> I get this Result:
> Server: Msg 208, Level 16, State 1, Line 121
> Invalid object name '#Temp1'.
> If I highlight the above commands and run them by
> themselves, it returns the correct result.
> It seems that Microsoft has issues with Temp tables. I'm
> hoping that there is a work around.
> Keith
>
> >--Original Message--
> >To create the table using SELECT...INTO the syntax
> should be:
> >
> >Select * into #Temp1 from Prod
> >
> >Then run
> >
> >Insert into #Temp1 Select * from OldProd
> >
> >
> >--
> >- Vishal
> >
> >
> >.
> >