I have a publisher, remote distributor and subscriber all running SS2000.
Under replication monitor on the distributor, under replication alerts i have enabled the "replication agent failure" alert.
All i need to know is should this alert trigger if the distribution agent that runs on the subscriber not the distributor fails?
I have add occurences of the distribution agent failing and the alert is not triggerred, is this because it only triggers for the agents that run on the distributor, ie: snapshot and log reader agents?
how i can setup an e-mail alert for a specific agent job, such that it sends an e-mail report when the run duration exceeds 30 minutes.
The agent job in question kicks off our ETL, and it runs every hour. It's happened before where a job running elsewhere(Not best practice, but we run reports from our datawarehouse) creates a deadlock on 1 of the tables being updated in the ETL, but i only get notified when i get calls from end users saying their reports aren't returning results.
I was thinking of creating a new job that query either the sysjobhistory or sysjobactivty table in msdb, but i would need it to refer to the run duration value of the job as it's still running( I think i can assume it's not updating that value every second...)
Hi, I need to set an alert when a replication-conflict occurs rather that to check for conflicts manually. How can i accomplish this? I couldn't find the particular error-message to trap in an alert.
We just moved source server to newer, bigger box ... Windows 2003 and Active Directory ... Snapshot agent worked but distribution failed ... Same login as on older machine, login is sysadm, used DCOMCNFG to allow ability to launch process ... What are we missing?
I have created a job with several SSIS packages included in it. There is a failure when I schedule and run the job. Where can I find the information why it is failing? When I run the packages individually, they run fine. Thanks in advance.
I have an issue on SQL Agent and wish you to advice:
Servers Environment:
The SQL Server and SQL Agent Service is installed on clustered Server1 where the Integration Services (IS) is installed on Server2. Deployment of SSIS packages have been done on Server1 and IS on Server2 is pointing to it.
Case:
1. In SQL Managament Studio, connection on Database Engine is up on Server1, connection on IS is up on Server2.
2. Execution of all packages via IS connection on Server2 is working well. No issue on running.
3. SQL Agent Job is created under Server1. The job failed on running. Message from the Log File Viewer:
"The job failed. The Job was invoked by User domainhost. The last step to run was step 1 (tewst)." "Executed as user: domainhost. ...n 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Code: 0xC0048020 Description: The version of component "Update to DB" (828) is not compatible with this version of the DataFlow."
HELP>>> I am getting desperate we recently had a failure which ended up wrecking my replication. The basic layout is from a source server -> Distribution Server then out to several other servers. Long story short the distribution database had to be moved to a new drive and I am now getting the following error message
The specified LSN (0003d4f0:0000721e:001b) for repldone log scan occurs before the current start of replication in the log (0003d63a:00002803:0001). (Source: fsdpdcis (Data source); Error number: 18768) --------------------------------------------------------------------------------------------------------------- The process could not set the last distributed transaction. (Source: fsdpdcis-DPDDATA-1 (Agent); Error number: 22017)
I have reinitialized all subscriptions on the fsdpdcis server and run subscription validation (with no response). The data did re-load during the reinitialization but does not seem to be getting updated. This is driving me crazy and I am desperate. Any suggestions would be greatly appreciated. I have search MS knowledge base with no results and BOL has not been any help so far.
I've noticed that the 14150 event is no longer showing up in the Application Log. The 14151 event does. We tried modifying the alert: sp_altermessage 14150, 'WITH_LOG', 'true';
But this of course doesn't work on messages under 50000. We utilized this alert in SQL 2000 to send emails upon the success of a merge replication. Any idea how to get this to work? Is there a workaround or did MS drop the ball on this event? If they dropped it - sure wish they would let us know and also remove the ability to setup the alert for it...
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
I am running transactional replication between a pair of SQL Server 2005 machines using a remote distributor. Occasionally the log reader agent fails with the error
"Replication-Replication Transaction-Log Reader Subsystem: agent * failed. The process could not execute 'sp_MSpub_adjust_identity' on '*'."
The agent starts back up again almost immediately on its own with seemingly no problems. I haven't been able to find any documentation on this error from Microsoft. Any ideas?
It is not a permissions issue as the log reader agent (and all of the other agents at that) are running under windows accounts with domain admin access for the domain that both the publisher and the distributor SQL Servers are on. (I know, I know, that's horrible. I've only been here a week and a half and I just found out about this today, so I didn't have anything to do with such a glaring security issue.) All of the SQL Servers involved with the replication are still granting sysadmin access to the windows administrators group.
I have scheduled a job in SQL Server Agent, as it executes it fails Immediatly. The error history displays the following error details, which i copied from the log file
"Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.
The system cannot find the path specified.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. End Error ... The package could not be f... The step failed."
I am using the File System Type to load my package.
I'm new to sql server administration so please show some understanding!
I installed sql server 2000 personal edition (upgrated to sp4) on an windows xp pro OS, but I cannot get the sql server agent service running. I get a message " the service did not start due to a logon failure". The server is my local pc, I'm using windows authentication and the login is "domain/loginname" where domain is my pc name.
Does it have to be installed on a server OS to have this service running?
D:WINDOWSMicrosoft.NETFrameworkv2.0.50727InstallUtil.exe SQLAgent.exe by running it from a command prompt.
and got an error that the system could not find the file or one of its components. It is a farily new install. I do not think I've lost any files any time.
Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm
After upgrading a server from SQL 2000 to SQL 2005, I can't get any SQL Agent jobs to run, giving logon failed messages. MSSQLSERVER and SQLSERVERAGENT services are set up to run as the same domain account which has administrative rights to the server and is a SQL login with the sysadmin role. For basic testing purposes, I created a test job that all it does is run a T-SQL command 'SELECT @@VERSION'. The job is also set to run as the same account that the Agent is running under. Below are the logs (with trace messages on) from the point of starting the Agent to my attempt to run the job (user and server names have been changed to protect the innocent):
2007-10-22 15:31:15 - ? [297] SQLServer Message: 15457, Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ? [297] SQLServer Message: 15457, Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ? [297] SQLServer Message: 15457, Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ? [100] Microsoft SQLServerAgent version 9.00.3042.00 (x86 unicode retail build) : Process ID 5168 2007-10-22 15:31:15 - ? [101] SQL Server TESTSERVER version 9.00.3042 (0 connection limit) 2007-10-22 15:31:15 - ? [102] SQL Server ODBC driver version 9.00.3042 2007-10-22 15:31:15 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is 2007-10-22 15:31:15 - ? [310] 4 processor(s) and 3328 MB RAM detected 2007-10-22 15:31:15 - ? [339] Local computer is TESTSERVER running Windows NT 5.2 (3790) Service Pack 2 2007-10-22 15:31:15 - ? [431] Populating subsystems cache... 2007-10-22 15:31:15 - ? [432] There are 11 subsystems in the subsystems cache 2007-10-22 15:31:15 - ? [124] Subsystem 'TSQL' successfully loaded (maximum concurrency: 80) 2007-10-22 15:31:16 - ? [124] Subsystem 'ActiveScripting' successfully loaded (maximum concurrency: 40) 2007-10-22 15:31:16 - ? [124] Subsystem 'CmdExec' successfully loaded (maximum concurrency: 40) 2007-10-22 15:31:16 - ? [124] Subsystem 'Snapshot' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'LogReader' successfully loaded (maximum concurrency: 100) 2007-10-22 15:31:16 - ? [124] Subsystem 'Distribution' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'Merge' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'QueueReader' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'ANALYSISQUERY' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'ANALYSISCOMMAND' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'SSIS' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [129] SQLSERVERAGENT starting under Windows NT service control 2007-10-22 15:31:16 - + [260] Unable to start mail session (reason: No mail profile defined) 2007-10-22 15:31:16 - ? [174] Job scheduler engine started (maximum worker threads: 400) 2007-10-22 15:31:16 - ? [193] Alert engine started (using Eventlog Events) 2007-10-22 15:31:16 - ? [146] Request servicer engine started 2007-10-22 15:31:16 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect 2007-10-22 15:31:16 - ? [110] Starting SQLServerAgent Monitor using '' as the notification recipient... 2007-10-22 15:31:16 - ? [133] Support engine started 2007-10-22 15:31:16 - ? [167] Populating job cache... 2007-10-22 15:31:16 - ? [297] SQLServer Message: 0, SQLServerAgent Monitor started successfully. [SQLSTATE 01000] 2007-10-22 15:31:16 - ? [168] There are 2 job(s) [0 disabled] in the job cache 2007-10-22 15:31:16 - ? [170] Populating alert cache... 2007-10-22 15:31:16 - ? [171] There are 0 alert(s) in the alert cache 2007-10-22 15:32:59 - ? [177] Job Test1 has been requested to run by User DOMAINmyaccount 2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp) 2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp) 2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp) 2007-10-22 15:32:59 - ? [184] Job completion for Test1 is being logged to sysjobhistory 2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp)
I have a package from SQL Server 2008 R2, that loads data from .xlsx file to database table.There are total 15 columns and 14000 rows in the .xlsx. The package runs fine in BIDS. But the same package in SQL Agent fails with error "omponent "Excel Source" (1)" failed validation and returned validation status "VS_ISBROKEN".
When I tried to run the package by deleting the half of the records for first 7000 rows it ran successfully in agent. Then the second half (last 7000 rows) also succeed from agent job. So, there is no issue with the data/datatypes.The agent job is able to run with record upyo 11000 rows in .xlsx. When I am running for 12000 rows it is failing.Is there any problem with the number of records in .xlsx or size through SQL Agent?
I am running the package from a Proxy account in sql agent job.
ERROR: Error: Executed as user: PROXY_ID. Microsoft (R) SQL Server Execute Package Utility Version 10.50.6000.34 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 10:36:09 AM Error: 2015-08-10 10:36:10.87 Code: 0xC0202009 Source: XX Connection manager "Excel Connection Manager 1"
Below the Error Log Reported in SQL Agent (64 bit SQLServer 2008R2 Enterprise Edition Service pack 2 ,32Core CPU Numa Node and 64GB Ram  [298] SQLServer Error: 10054, TCP Provider: An existing connection was forcibly closed by the remote host. [SQLSTATE 08S01] [298] SQLServer Error: 10054, Communication link failure [SQLSTATE 08S01] [165] ODBC Error: 0, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001] [382] Logon to server '(local)' failed (ConnUpdateStartExecutionDate)
By Accident One table got truncated on the subscriber which is one of the table in Published articles. Now all the Update inserts and delete are failing . and other tables are not being replicated either. what could be done to fix this . Publisher gets a lots of updates every hour how ever because of this problem all the tables are not getting any transactins replicated. Please help ASAP
I am fed up of getting the below error message of repl. atleast once a day.
JOB RUN: 'REPL:1001' was run on 3/23/2007 at 3:00:01 AM DURATION: 1 hours, 11 minutes, 11 seconds STATUS: Failed MESSAGES: The job failed. The Job was invoked by Schedule 421 (Replication agent schedule.). The last step to run was step 3 (Detect nonlogged agent shutdown.).
Please see below for the description of my Replication Job: ------------------------------------ Steps of Job: 1. Distribution Agent startup message. db: distribution cmd: sp_MSadd_distribution_history @perfmon_increment = 0, @agent_id = 126, @runstatus = 1, @comments = 'Starting agent.' 2. Run agent. cmd: -Subscriber [GIZMO] -SubscriberDB [TelehopBilling] -Publisher [ROGER] -Distributor [ROGER] -DistributorSecurityMode 1 -PublisherDB [TelehopBilling] 3. Detect nonlogged agent shutdown. db: distribution cmd: sp_MSdetect_nonlogged_shutdown @subsystem = 'Distribution', @agent_id = 126
This Job is executed every hourly and an error is fired atleast once a day notifying failure of this process. Please help me in resolving this issue.
Recently I Restored a database from Live to Test Instance for implementing CDC feature on sql server 2008 X64 Enterprise Edition on Windows server 2008r2 X64.
Meant there was NO CDC Enabled in Production Nor Replication Features Its stand Alone.Â
Initially i Enabled CDC on Database level and next for 3 Tables, after that i inserted records for all the tables but the same was not getting reflected to CDC tables. then i checked in agent service-> CDC capture Job -> view history, then i got the following below error..
Message
Msg 22859, Level 16, State 2, Log Scan process failed in processing log records. Refer to previous errors in the current session to identify the cause and correct any associated problems. For more information, query the sys.dm_cdc_errors dynamic management view.
Date 14-Dec-13 8:07:35 AM Log Job History (cdc.HC_UAT_DB_V4_capture)
Step ID 2
Server ADDCSVHC05 Job Name cdc.HC_UAT_DB_V4_capture Step Name Change Data Capture Collection Agent Duration 00:34:29 Sql Severity 16 Sql Message ID 22859 Operator Emailed Operator Net sent Operator Paged Retries Attempted 11
Message
Executed as user: Testhcuat. Could not locate text information records for the column "ExMaritalStatus",Â
ID 189 during command construction. [SQLSTATE 42000] (Error 18773) Â The Log-Scan Process failed to construct aÂ
replicated command from log sequence number (LSN) {00232390:00001fc9:0015}. Back up the publication database andÂ
contact Customer Support Services. [SQLSTATE 42000] (Error 18805) Â Log Scan process failed in processing logÂ
records. Refer to previous errors in the current session to identify the cause and correct any associatedÂ
problems. [SQLSTATE 42000] (Error 22859)
what is the cause for the below error and how i can get rid of this.
"ExMaritalStatus",column -> Nvarchar datatype
Note :Â issue can be solved by disabling & enabling CDC at database level but i need the root cause, again CDC capture job fails for capturing other Nvarchar columns when i Re-Enable at the database level. This cause is getting cycled/looping in capturing other colums.
We replicate from London to New York and Hong Kong. The New York replication is working fine but everytime we try the Hong Kong replication it starts doing it but when it gets to the bulk copying stage it'll still keep going but on a random table will suddenly fail with a communication link failure message ?? It used to do this and because it keeps trying it used to eventually succeed but recently it never succeeds ?? It keeps trying but we end up having to stop it as it just doesn't succeed anymore ?? Our link to Hong Kong appears fine, we can ping and PC Duo into their servers.
Any ideas would be greatly appreciated as we can't think what it may be ??
I'm getting the error message below when trying to synchronise a sql compact database to a sql server 2005 db. Can anyone suggest a reason for this?
RDA works fine, permissions look ok. I've run process monitor and can see the IN/OUT files being created, no ACCESS DENIED messages etc. help!
New Subscription Wizard
- Beginning Synchronization (Success)
- Synchronizing Data (100%) (Error)
Messages
Initializing SQL Server Reconciler has failed. Try again. HRESULT 0x80045901 (29045)
The merge process could not initialize the message file from Subscriber 'D:InetpubwwwrootSync30.59FA6BD9893A_9BFA53BB-1D7C-4E84-967A-799C6FEBFCE3.IN:D:InetpubwwwrootSync30.59FA6BD9893A_9BFA53BB-1D7C-4E84-967A-799C6FEBFCE3.OUT'. HRESULT 0x80045901 (0)
I seem to be having trouble with the distribution agent not doing what I want it to do. I have set up a few publications to run all the time but when I want to schedule a publication to run once a day this doesn't happen it defaults to run all the time. Can the distrubition agent run some publications continusly and some publications scheduled?
Please, i need some help with the replication i had configured. I have two publications to the same subscriber, one with data filters and other not. The one without data filters i have test manualy. But now after i have created the other one, the log reader says that '145 transactions with 347 commands' were delivered, but the distribution agents says that 'no replicated transactions are avaiable'. I know that have many things to be replicated, but doesn´t work. The Sql server agent is working on the distributor. There is something that can be causing this error ?????
What happens to these REPLication agents if SQL Agent is stopped and started: Snapshot Agents Merge Agents Misc Maintenance Agents
Can the agent be stopped and started with no thought to the status of replication, or should the replication state be modified in some way before any change to the status of the Agent?
(I know REPL depends on agent, so wuestion is, can REPL simply resume or not when agent is re-started.)
-- RESULT: Server: Msg 14101, Level 11, State 1, Procedure sp_MSaddpub_snapshot, Line 73 The publication 'publication_name' already has a Snapshot Agent defined.
During testing, the log for tempdb filled up. That caused the replication agents to get an error and stop. Is there an easy way to have the agents wait a minute or 2 then try to restart?
I'm "trying" to set up Replication. The Publishing/Distribution server is in one domain, and the subscribing server is in another. Both domains are fully trusted.
The synchronization step builds the .tmp file, but the repl_subscriber Distribution task bites the dust with an error message, "28000[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed".
The setting on the distribution options dialog box is ODBC, SQL Server. I'm not using a special login/password. I've even tried putting a user name and password there, and it doesn't work. The ODBC connections test out fine on both servers. Any suggestions where I've gone wrong?
