Deleting Publication With No Agents Available
Mar 6, 2007
I have a case of SQL Server 2005 production db's publication which does not have either Log reader, snapshot or distribution agents.
I need to modify primary key in one of the tables, and existing publication does not allow this.
When I try to start snapshot job in GUI I am getting of course error:
"Could not find the regular snapshot job for the spicified publication. Supply either @job_id or @job_name to identify the job, error 20678."
Is there any way to erase it with system st procs or SQL?
Help is greatly appreciated.
View 3 Replies
ADVERTISEMENT
Mar 6, 2007
I have the case where a SQL Srv 2K5 publication has no agents - agents were accidentally erased during backup/restore. I was abel to erase subscription.
The error is "The specified @job_name <jab name> does not exist. (Microsft SQL Server, Error: 14262)
Is there any way to remove publication?
Help will be greatly appreciated!
Vladimir
View 4 Replies
View Related
May 1, 2006
Hi All,I have a problem, a user deleted some tables from a couple db's dealingwith replication. I now get this error on those db's.The process could not execute 'sp_repldone/sp_replcounters onservernameI have deleted one of the database's and neither are listed underpublications but they still show up under replication monitor.Any ideas on how to delete them would be great. When I try I getError 208: Invalid object name 'sysarticles'Invalid object name 'sysschemaarticles'Could not use view or function 'sysextendedarticlesview' because ofbinding errors.TIADave
View 1 Replies
View Related
Sep 21, 2007
Hi, everyone,
I am new in SQL server 2005. I had setup SQL server 2005 P2P replication. Somehow it did not work one of two way replication. I tried to delete the publication. However I could not do it. have the same problem. When I tried to delete the publication, I got the publication " " does not exist.[SQL server error: 20026]. I tried to use sp_droppublication, it gave me error "the database is not enabled for publication". Nevertheless, I can see the publication in MS SQL Management Studio and Publication monitor with OK status. I could not find the distribution database either.
Could you anyone has ideas to delete this publication? I am sorry I am not a programmer. Please give me more detail explanation if you can. Thanks.
View 6 Replies
View Related
Aug 9, 2007
Hi
i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table.
regards
vinod.t.v
View 9 Replies
View Related
Jul 18, 2014
/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ServiceLogPurge]
-- Purge records dbo.ServiceLog older than 3 months:
-- Purge records in small portions to avoid locking production tables
-- for a long time. The process takes longer, but can co-exist with
-- normal usage of the tables.
[Code] ...
*** Getting this error below when executing the code ***
Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45
Incorrect syntax near 'Failed:'.
View 9 Replies
View Related
Sep 14, 1999
I am using MS SQL 6.5 SP5, NT4.0. running Tivoli Service Desk. Two of the four agents on the Applications server were not running so I recycled the server and now NONE of the agents are coming up. Generally the agents show up on the application server control panel but they have not started. The status of the server is 'initializing' and the Watchdog has started but only the 'dispatcher'started. I have hard booted the aps server and recycled the aps and db servers both. I know that there is a way to suspend the agents but that has not been done and the 'resume' button is not available to use. I tried restarting the dispatcher but it didn't do anything. I have also gone into 'config' on the control panel and it shows that '4' agents are set to start up.
We run a Web version also so I have my help desk analysts using it but they cannot refer back to tickets that they have just opened, etc.
ANY SUGGESTIONS - I have been waiting on Tivoli to call me back for almost an hour and a half.
Thanks - Mary
812-485-7876
mkeown@dc-is.org
View 1 Replies
View Related
Oct 8, 2007
I have around 25 sql servers(sql server 2000) all on windows server 2003. i would like to know if anyone has a script that will poll all the servers and check to make sure the agent is running.
thanks for your help
View 2 Replies
View Related
Sep 14, 2006
Which replication agents are available in Express edition?
abdul
View 1 Replies
View Related
May 22, 2006
Is it possible to obtain the status, running or not running, of the distribution agents that appear under Replication MonitorAgentsDistribution Agents in Enterprise Manager using WMI? If yes, can you please tell me the WMI namespace and objects that can be used?
Thanks
View 1 Replies
View Related
Dec 5, 2006
Hi all replication experts...
I have created transactional replication on my servers (production server and backup server) and created pull subscription at the backup server. On my production server that also is a distributor and publisher there exist two agents, Snapshot agent and Log reader in the Publishers folder under replication Monitor.
There are also another agent with the backup server name and database name: BackServ:DBName having the type Pull and 'No replicated transactions are available' as Last Action. My question is: What is this? Must it be there?
Grateful for answers
Best,
/M
View 3 Replies
View Related
Sep 20, 2006
Hi all,
I have encountered some problems in scheduling the replication agents but before explaining that let me give a small background of the current DB landscape.
It is implemented as Star topology or re-publisher topology with the publisher and the subscriber configured on SQL Server 2000 and it consists of ShareDB on SQL Server 2005 which acts as the re-publisher of data. All other databases publish articles to ShareDB which in turn re-publishes to the databases which subscribes for the article. The topology is using a remote distributor on SQL Server 2005 to offload the replication agent jobs processing from the OLTP environment. The distributor job agent is running continuously and it is shared for a specific database in case of Snapshot replication. And all the Transactional publications will have a separate distributor agent. At present the replications agents are scheduled on both the publisher and ShareDB.
Now, I am trying to enforce one way scheduling on the publisher side which will start the snapshot agent for the corresponding publication on ShareDB after the distributor agent transfers the data from the snapshot folder to the subscribers. This approach is effective and more reliable in the sense that data synchronization will happen immediately after the distributor agent transfers the data to ShareDB.
The following are the issues related to scheduling the replication agent on the publisher only.
1. I have included sp_start_job stored procedure after the run agent step in the shared distributor job agent steps to start the snapshot agent of the corresponding publication on ShareDB after the distributor agent transfers the data to ShareDB. Since the distributor agent is running continuously in our scenario, the next step is not getting executed and so the job agent of the corresponding publication on ShareDB is not started.
2. It is also necessary to specify either the job name or the job id in sp_start_job stored procedure to start the job agent. With the replication setup configured in the aforementioned fashion, how is it possible to invoke the snapshot job agent of the corresponding publication on the second link (from ShareDB) dynamically after the distributor agent on the first link is completed?
Thanks a lot,
Barry
View 1 Replies
View Related
Sep 22, 2006
Hi all,
I have encountered some problems in scheduling the replication agents but before explaining that let me give a small background of the current DB landscape.
It is implemented as Star topology or re-publisher topology with the publisher and the subscriber configured on SQL Server 2000 and it consists of ShareDB on SQL Server 2005 which acts as the re-publisher of data. All other databases publish articles to ShareDB which in turn re-publishes to the databases which subscribes for the article. The topology is using a remote distributor on SQL Server 2005 to offload the replication agent jobs processing from the OLTP environment. The distributor job agent is running continuously and it is shared for a specific database in case of Snapshot replication. And all the Transactional publications will have a separate distributor agent. At present the replications agents are scheduled on both the publisher and ShareDB.
Now, I am trying to enforce one way scheduling on the publisher side which will start the snapshot agent for the corresponding publication on ShareDB after the distributor agent transfers the data from the snapshot folder to the subscribers. This approach is effective and more reliable in the sense that data synchronization will happen immediately after the distributor agent transfers the data to ShareDB.
The following are the issues related to scheduling the replication agent on the publisher only.
1. I have included sp_start_job stored procedure after the run agent step in the shared distributor job agent steps to start the snapshot agent of the corresponding publication on ShareDB after the distributor agent transfers the data to ShareDB. Since the distributor agent is running continuously in our scenario, the next step is not getting executed and so the job agent of the corresponding publication on ShareDB is not started.
2. It is also necessary to specify either the job name or the job id in sp_start_job stored procedure to start the job agent. With the replication setup configured in the aforementioned fashion, how is it possible to invoke the snapshot job agent of the corresponding publication on the second link (from ShareDB) dynamically after the distributor agent on the first link is completed?
Thanks a lot,
Barry
View 1 Replies
View Related
Sep 18, 2006
Ideally I am looking for a procedure that I can call in T-SQL to verify that the Log Reader, Snapshot, and Sql Agents are all up and running on a given server. I found the undocumented sp_MSrepl_distributionagentstatussummary proc and got it to work for the distribution agent. I also found sp_MSrepl_agentstatusummary, but I've yet to get this to work. Overall, I'd really like to have a better way to do this.
This absolutely must be something callable from the sqlcmd interface. It can not be through a UI nor anthing more complicated than standard T-SQL.
Any suggestions?
View 6 Replies
View Related
Apr 14, 2000
Before I started with this employer the server support staff had planned a backup strategy that included using database agents for Oracle and SQL Server. Nothing is really set up yet for SQL Server so I can still change this direction. Has anyone seen a definite benefit to using database backup agents? If so, what benefits have you seen? There doesn't seem to be much value added by paying for and using an additional product when the database's own utilities are so easy to use, and all the backup files can be backed up to tape with the basic backup software. I've not worked with it, though, so perhaps I am missing something. These are small databases so space is not an issue. Any opinions/comments are appreciated.
View 2 Replies
View Related
Feb 24, 2003
Can I have multiple SQL Server Agents running concurrently?
I would like to have 10 instances of database running on my local SQL Server - each of these are Merge Replication to a unique database located offsite.
Do they all run through one SQL Server Agent?
If they do would this be a slow process as, I think, the SQL Server Agent queues the replications pending.
Thanks,
Bianca
View 1 Replies
View Related
Jan 21, 1999
Any thoughts or experiences (good or bad) on using the SQL agents from Arcserve or BackupExec for backups as opposed to standard dumps to disk?
I have always used dumps and taken the position that these tools work well, reliably and do not depend on another device ( tape device) for backups. Also they should be quicker and not cause any problem with table / log concurrency or delays in updates while the backup takes place.
Also, does anyone know how these products actualy maintain concurrency? Has anyone ever used one of these backups as a recovery?
Thanks in advance.
View 1 Replies
View Related
Jul 23, 2002
Hi
By default, the Log Reader Agent and Distribution Agent use the account the starts SQL Server Agent to logon into the Publisher SQL Server when performing SQL Server Transactional replication. Is there a way of configuring them so they use a different account.
Thanks
Tariq
View 1 Replies
View Related
Oct 28, 2005
Hi all,
I have 5 laptops, 1 laptop with SQL Server 2000 and the other 4 with SQL Server MSDE Rel A. I had a "Merge Replication" scheme that I had to set up. The laptop with 2000 will be the main Publisher/distributor and publishes/merges to two of the MSDE laptops(these laptops have pull subscriptions that pull from the 2000 laptop). These 2 MSDE laptops in turn Re-publish their data to the last 2 laptops and my scheme ends there. I have succesfully been able to Script this entire "Merge Replication" scheme, and I ran it. It creates all Components needed for my scheme to work.
1 issue though. It doesnt kick start the agents :eek: , so the replication scheme is just dormant.
Does anyone have any examples of scripts that I could use to kick off all the agents (snapshot,merge agents etc) and maybe to stop them too. I seriously need this, because we will need to have the entire process scripted.
Thanks
'Wale
p.s.
Also does anyone have sample scripts to alter settings for the SQL SERVER AGENT, for example altering its "Log On" parameter to allow a particular user with Admin privileges and also to switch its start mode to "Automatic"
View 1 Replies
View Related
Jun 23, 2008
We have Lots of databases wich typically have three Trasactional Publications and two subscribers. Some DB's use a shared distribution Agent for all three Pubs whilst some DB's use Independent Agents. The reason for this set up seems to be purely by chance.
BOL says "Sharing the same Distribution Agent reduces the resource demands and increases performance at the Distributor". Whilst some resources on the net say "IF you use independant Agents, you can get performance improvements while replicating your data". This seems to be conflicting information.
I know I should go with BOL but having seperate distribution streams does make more sense (although I now have three jobs per subsciber instead of one using this method). Also more importantly, if one publications fails with dodgy data then this does not affect the other Publications. What do other people think?
Thanks in advance
View 1 Replies
View Related
Feb 20, 2008
All:
I am aware that I am raising an issue/question that has quite a number of ancestors in this forum. In reviewing some of the threads I still believe my situation has a bit of a twist; but that could just be me.
The process I used until a change I made recently worked just fine. A handful of my packages connect to our ERP system that only supports an ODBC connection. I set the Protection Level to the default, and then deploy the packages to the server. I use an agent to run the jobs that include these packages as steps. I have hardcoded the userID and password in the SQL jobs and so they have run fine.
In an effort to reduce maintenance on the packages I decided to run the packages from the File System instead of deploying them to the server. Now, the packages are not running as I have not changes the Protection Level yet. I did test running one of the packages using a Proxy I have created but that does not work either.
Based upon what I have read it appears that the first thing I need to do is change the Protection Level to DoNotSaveSensitive. How do I then pass the ID and password to the agent?
a. Create a confirguation file?
b. Create a package template?
c. Both of the above
To reiterate I do not wish to deploy the packages to the server; I prefer to run the packages from the File System. Further, I just have one box on which everything happens; there are no migration issues across servers.
Some insights from this group will be greatly appreciated.
Thank you!
View 8 Replies
View Related
Jun 23, 2007
hi !
I am using sql server 2005 sp1 standard edition.
I have a transactional replication set up from one server to another.
From last couple of days i am noticing two distribution agents running for the same subscription in the replication monitor. Before this only one distribution agent was running. Is there any problem having two distribution agents running in parallel for the same subscription on a server?
Any help would be appreciated.
Thanks
View 1 Replies
View Related
Apr 30, 2007
I hope this is an easy question.
I am setting up my replication as it is in 2000... I want to set most publications to share an agent but I also want a few select table to have an independent agent... My question is if I have ones set up as independent they stay independent correct?? I mean once I select a publication to be shared it does not share with an agent that is marked to be independant right? it creates another agent for all the shared publications...
ok... I hope that was not confusing... I just want to confirm what I was thinking.
Thanks ahead of time.
View 1 Replies
View Related
May 31, 2000
I'm going back and forth on an issue and was looking for some outside observations. I have over a dozen SQL NT servers, both 6.5 and 7.0. On some servers I'm dumping the databases and backing up the dumps with Veritas BackupExec, on 4 (two SQL 6.5, two v7) I'm backing up with the Veritas SQL Backup Agent.
Obviously, if you don't have room to dump a database, you must use the backup agent. That is the case on one of my servers and becoming so on a second. But my personal preference is to do the dump/backup.
(as a side note, one server is not backing up correctly with the backup agent but ultimately that box will require it due to DB growth, so it is something that I have to resolve)
I like the dump system for a few reasons. I find it easier to load from a dump, particularly a single table. Likewise, I find it easier to copy a database by loading from a dump rather than going from a backup, but that's mainly because of BackupExec being a little bit strange on redirecting restores.
Here's my clincher. I find restores via the backup agent to be ridiculously slow. Let's say I have a 5gig DB that has 1.5gig in it. The dump size will probably be somewhere below 2gig. The restore via backup agent carefully writes the entire 5gig even though 3.5g of that is empty. This takes a lot of time. Add to that the "post-restore DBCC". I had such a restore take something on the order of 13 HOURS which, needless to say, conflicted with my nightly DBCC's and backups.
OK. End of rant. Any suggestions or thoughts on the subject?
View 1 Replies
View Related
Aug 12, 2004
I've created (via Enterprise Manager) a merge replication and configured the publisher, distributer, and push subscription (via the wizards).
The Snapshot Agent is configured to autorun once the publication has been completed.
The Merge Agent has a schedule of 'Start automitically when SQL server agent starts', ie it runs continuously.
After setting this all via the wizards it all executes correctly with both the snap shot and merge agents firing and doing their jobs.
I then use the option Tools->Replication->Generate SQL Script. The 'script creation of replication jobs' option is checked. The script is created ok.
I then disable (completely) the replication, delete the database, and readd it cleanly.
From a command prompt I then fire:
osql -E -S <server> -d <db name> -i <script name>
This correctly sets up all the replication components and jobs BUT both the snap shot and merge agents haven't been started. Why haven't they been auto executed? Also, the merge agent schedule is defaulting to 'once per week' and no the selection i'd originally chosen. Does the script generation miss certain configuration choices?
If this is the case then what scripting additions do I need to make the agents fire and with the correct scheduling?
This is getting desperate as the SQL script needs to go into our InstallShield installation.
Please help,
Jez
View 2 Replies
View Related
Aug 21, 2006
This is the most frustrating error I've ever encountered.
I can get no more info from sql server besides "server execution failed". It gives this error when starting the snapshot, distribution, and log reader agents.
This server has been working in this configuration for over a year. We made no changes except restarted the server. This is a windows 2003 server with sql server 2000 running all the latest patches and windows updates.
View 4 Replies
View Related
Jul 20, 2005
I have a situation with a 6 server setup whereby merge agentswill not run if other merge agents are alreay running. Basicallywe have a 5 laptop, one tower config with the tower being thepublisher. In theory we want to be able to hook a laptop upand have it sync within, say, 5 mins. Trouble is, if agents forthe other laptops are running (trying and failing to connect becausethose laptops aren't connected) then the agents for the laptop that*is* connected won't run. It seems to be a 'maximum number of runningagents' issue. Is this is user defined setting, and if so where can I set it?Many thanks, sorry if this has been answered many many times before.Jim--Find me at http://www.ursaMinorBeta.co.uk"Brace yourself, this might make your eyes water."
View 3 Replies
View Related
Jan 19, 2006
Guys,
I get the error message: Queue reader aborting. The steps failed...in queue reader agents of SQL 2000 replication. Could any one give me a hint of how to troubleshoot this problem? Thanks a lot in advance.
View 1 Replies
View Related
Aug 23, 2006
During an upgrade, we had problems with PK errors on sp_MSget_repl_commands. I recreated the SP with the varbinary characteristic as we have seen in many posts and it was sucessful in starting. However, the sp now appears as a 'user' sp, not a 'system' sp, however I an not sure that is a real issue.
The problem I am seeing is that none of the agents are appearing in replication monitor under their agent folders. Replication appears to be working fine and the publications are showing on the distributor in the Replication Monitor - Publication folder under the appropriate publisher. But all of the agent specific folders (i.e. Snapshot agent, distribution agent, etc) are empty.
Another interesting point, is that if replication fails, the failure 'X' shows up on the Agents folder as well as the publisher, but again, no sign of the agent in the folder.
Thoughts?
View 1 Replies
View Related
Aug 21, 2007
Hi all
I have SQL 2000 SP4 running on a W2K server SP4.
The server acts as a subcriber and publisher and acts as its own distributor.
Each publication uses its own distribution agent and is set to loop.
There are 4 queued updating subscriptions on this box.
When running 3rd party tools to determine open active ports, the replication executables (distrib and logreader) are connecting to local server multiple times. Easily 15+ established connections plus many more waits.
Everything connects to 1433 then opens a new port.
Is this because of the queued updating?
Replication is functioning as it should without any problems.
I even checked our 2 main distribution servers which only act as distributors and there is not one connection from replication to itself.
Any insight would be appreciated
Thx
John
View 3 Replies
View Related
Jun 20, 2006
I have a publication on my SQL Server.
The first time my application syncs with the server it works fine.
If I try to sync again I get "Permission Denied Number 80040E09 NativeErr: 0"
Other publications work fine, it just seems to be this one.
Any ideas?
View 5 Replies
View Related
Oct 30, 2006
Hi!
I have a strange error, when I try to create publication
Msg 3933, Level 16, State 1, Procedure sp_MSrepl_addpublication, Line 1222
Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
What is it?
P. S. The error arise with all types of replication.
View 6 Replies
View Related
