SQL Agent terminates, then replication can't restart.
in [Replication]
|
Prev: suscription could not be created-mismatch between replication meta
Next: Rube Goldberg triviality when adding rows that have non-NULL rowguid columns
From: sqlboy2000 on 21 Jun 2007 11:49 Hi all, Our setup is pretty simple, one transactional box, a remote distributor, and a reporting server using one way transactional replication, push subscription, no immediate updating. (All boxes SQL 2005) Around 3am the sql server agent died on the distribution sever with this message in the event log: Source: .NET Runtime 2.0 Error Faulting application sqlagent90.exe, version 2005.90.2047.0, stamp 443f5c88, faulting module kernel32.dll, version 5.2.3790.2756, stamp 44c60f39, debug? 0, fault address 0x00015e02. The SQL Agent is set to restart itself and did so, however it just kept retrying and failing to restart replication with this message: (turned on verbose logging) 'Another logreader agent for the subscription(s) is running or the server is working on a previous request by the same agent.' So I kind of get the agent died and the log reader process seemed to be orphaned and the agent couldn't start another log reader process. Once I killed the old processes, it started up again. But I have 2 questions: 1) Has anyone seen that error before that killed the sql agent? I can't find anything on it. 2) Is there anyway to get replication to keep working in the event the sql agent dies like that? I'd hate to think that replication can't recover when the agent stops unexpectedly. Thanks in advance.
From: Hilary Cotter on 21 Jun 2007 13:53 1) I've seen error messages like this one, normally when binary data gets into char or text columns. Check the rows of the base table to see if there are garbage characters there. 2) I haven't found a good solution to the rogue log reader agents. I have had to resort to killing all agents in task manager. I once wrote a script that exported a list log reader agents using tlist and then a batch file which would kill them, but this did not work well for me. I think your best bet is to schedule the log reader agents to restart every minute, or try to have the log reader agent loop back to step one on failure. I have had better success with a schedule as opposed to having it loop around to step 1 again. If I do the loop I get the another log reader agent is running message. -- Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com "sqlboy2000" <sqlboy2000(a)discussions.microsoft.com> wrote in message news:F8C7744C-3302-4F15-84A9-CAED9F6CB61D(a)microsoft.com... > Hi all, > Our setup is pretty simple, one transactional box, a remote distributor, > and > a reporting server using one way transactional replication, push > subscription, no immediate updating. (All boxes SQL 2005) Around 3am the > sql > server agent died on the distribution sever with this message in the event > log: > > Source: .NET Runtime 2.0 Error > Faulting application sqlagent90.exe, version 2005.90.2047.0, stamp > 443f5c88, > faulting module kernel32.dll, version 5.2.3790.2756, stamp 44c60f39, > debug? > 0, fault address 0x00015e02. > > The SQL Agent is set to restart itself and did so, however it just kept > retrying and failing to restart replication with this message: (turned on > verbose logging) > > 'Another logreader agent for the subscription(s) is running or the server > is > working on a previous request by the same agent.' > > So I kind of get the agent died and the log reader process seemed to be > orphaned and the agent couldn't start another log reader process. Once I > killed the old processes, it started up again. > > But I have 2 questions: > > 1) Has anyone seen that error before that killed the sql agent? I can't > find > anything on it. > > 2) Is there anyway to get replication to keep working in the event the sql > agent dies like that? I'd hate to think that replication can't recover > when > the agent stops unexpectedly. > > Thanks in advance. > > >
From: sqlboy2000 on 21 Jun 2007 15:20 Thanks for the info, I was afraid of that. We actually had the sql agent die on 3 different servers, 2 of which weren't involved in replicaton. The only thing I can think is some kind of network issue, they all use the same network login. Does restarting the log reader agent every minute slow down replication or cause other issues? "Hilary Cotter" wrote: > 1) I've seen error messages like this one, normally when binary data gets > into char or text columns. Check the rows of the base table to see if there > are garbage characters there. > 2) I haven't found a good solution to the rogue log reader agents. I have > had to resort to killing all agents in task manager. I once wrote a script > that exported a list log reader agents using tlist and then a batch file > which would kill them, but this did not work well for me. I think your best > bet is to schedule the log reader agents to restart every minute, or try to > have the log reader agent loop back to step one on failure. I have had > better success with a schedule as opposed to having it loop around to step 1 > again. If I do the loop I get the another log reader agent is running > message. > > -- > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > "sqlboy2000" <sqlboy2000(a)discussions.microsoft.com> wrote in message > news:F8C7744C-3302-4F15-84A9-CAED9F6CB61D(a)microsoft.com... > > Hi all, > > Our setup is pretty simple, one transactional box, a remote distributor, > > and > > a reporting server using one way transactional replication, push > > subscription, no immediate updating. (All boxes SQL 2005) Around 3am the > > sql > > server agent died on the distribution sever with this message in the event > > log: > > > > Source: .NET Runtime 2.0 Error > > Faulting application sqlagent90.exe, version 2005.90.2047.0, stamp > > 443f5c88, > > faulting module kernel32.dll, version 5.2.3790.2756, stamp 44c60f39, > > debug? > > 0, fault address 0x00015e02. > > > > The SQL Agent is set to restart itself and did so, however it just kept > > retrying and failing to restart replication with this message: (turned on > > verbose logging) > > > > 'Another logreader agent for the subscription(s) is running or the server > > is > > working on a previous request by the same agent.' > > > > So I kind of get the agent died and the log reader process seemed to be > > orphaned and the agent couldn't start another log reader process. Once I > > killed the old processes, it started up again. > > > > But I have 2 questions: > > > > 1) Has anyone seen that error before that killed the sql agent? I can't > > find > > anything on it. > > > > 2) Is there anyway to get replication to keep working in the event the sql > > agent dies like that? I'd hate to think that replication can't recover > > when > > the agent stops unexpectedly. > > > > Thanks in advance. > > > > > > > > >
From: Hilary Cotter on 22 Jun 2007 14:01 You should run the log reader agent continuously (this is the default), but then schedule it to run every minute. This way on restart it will run until failure, and then sleep until the next schedule. Another option is to have it return to job step one on failure of job step 2 or 3. -- Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com "sqlboy2000" <sqlboy2000(a)discussions.microsoft.com> wrote in message news:5E930B16-1D5A-4603-99A3-BC6B5CDEDDF5(a)microsoft.com... > Thanks for the info, I was afraid of that. We actually had the sql agent > die > on 3 different servers, 2 of which weren't involved in replicaton. The > only > thing I can think is some kind of network issue, they all use the same > network login. Does restarting the log reader agent every minute slow down > replication or cause other issues? > > > > "Hilary Cotter" wrote: > >> 1) I've seen error messages like this one, normally when binary data gets >> into char or text columns. Check the rows of the base table to see if >> there >> are garbage characters there. >> 2) I haven't found a good solution to the rogue log reader agents. I have >> had to resort to killing all agents in task manager. I once wrote a >> script >> that exported a list log reader agents using tlist and then a batch file >> which would kill them, but this did not work well for me. I think your >> best >> bet is to schedule the log reader agents to restart every minute, or try >> to >> have the log reader agent loop back to step one on failure. I have had >> better success with a schedule as opposed to having it loop around to >> step 1 >> again. If I do the loop I get the another log reader agent is running >> message. >> >> -- >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> "sqlboy2000" <sqlboy2000(a)discussions.microsoft.com> wrote in message >> news:F8C7744C-3302-4F15-84A9-CAED9F6CB61D(a)microsoft.com... >> > Hi all, >> > Our setup is pretty simple, one transactional box, a remote >> > distributor, >> > and >> > a reporting server using one way transactional replication, push >> > subscription, no immediate updating. (All boxes SQL 2005) Around 3am >> > the >> > sql >> > server agent died on the distribution sever with this message in the >> > event >> > log: >> > >> > Source: .NET Runtime 2.0 Error >> > Faulting application sqlagent90.exe, version 2005.90.2047.0, stamp >> > 443f5c88, >> > faulting module kernel32.dll, version 5.2.3790.2756, stamp 44c60f39, >> > debug? >> > 0, fault address 0x00015e02. >> > >> > The SQL Agent is set to restart itself and did so, however it just kept >> > retrying and failing to restart replication with this message: (turned >> > on >> > verbose logging) >> > >> > 'Another logreader agent for the subscription(s) is running or the >> > server >> > is >> > working on a previous request by the same agent.' >> > >> > So I kind of get the agent died and the log reader process seemed to be >> > orphaned and the agent couldn't start another log reader process. Once >> > I >> > killed the old processes, it started up again. >> > >> > But I have 2 questions: >> > >> > 1) Has anyone seen that error before that killed the sql agent? I can't >> > find >> > anything on it. >> > >> > 2) Is there anyway to get replication to keep working in the event the >> > sql >> > agent dies like that? I'd hate to think that replication can't recover >> > when >> > the agent stops unexpectedly. >> > >> > Thanks in advance. >> > >> > >> > >> >> >>
From: Hilary Cotter on 22 Jun 2007 14:01
To answer your second questions - the over head of restarting a job frequently is not significant. -- Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com "sqlboy2000" <sqlboy2000(a)discussions.microsoft.com> wrote in message news:5E930B16-1D5A-4603-99A3-BC6B5CDEDDF5(a)microsoft.com... > Thanks for the info, I was afraid of that. We actually had the sql agent > die > on 3 different servers, 2 of which weren't involved in replicaton. The > only > thing I can think is some kind of network issue, they all use the same > network login. Does restarting the log reader agent every minute slow down > replication or cause other issues? > > > > "Hilary Cotter" wrote: > >> 1) I've seen error messages like this one, normally when binary data gets >> into char or text columns. Check the rows of the base table to see if >> there >> are garbage characters there. >> 2) I haven't found a good solution to the rogue log reader agents. I have >> had to resort to killing all agents in task manager. I once wrote a >> script >> that exported a list log reader agents using tlist and then a batch file >> which would kill them, but this did not work well for me. I think your >> best >> bet is to schedule the log reader agents to restart every minute, or try >> to >> have the log reader agent loop back to step one on failure. I have had >> better success with a schedule as opposed to having it loop around to >> step 1 >> again. If I do the loop I get the another log reader agent is running >> message. >> >> -- >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> "sqlboy2000" <sqlboy2000(a)discussions.microsoft.com> wrote in message >> news:F8C7744C-3302-4F15-84A9-CAED9F6CB61D(a)microsoft.com... >> > Hi all, >> > Our setup is pretty simple, one transactional box, a remote >> > distributor, >> > and >> > a reporting server using one way transactional replication, push >> > subscription, no immediate updating. (All boxes SQL 2005) Around 3am >> > the >> > sql >> > server agent died on the distribution sever with this message in the >> > event >> > log: >> > >> > Source: .NET Runtime 2.0 Error >> > Faulting application sqlagent90.exe, version 2005.90.2047.0, stamp >> > 443f5c88, >> > faulting module kernel32.dll, version 5.2.3790.2756, stamp 44c60f39, >> > debug? >> > 0, fault address 0x00015e02. >> > >> > The SQL Agent is set to restart itself and did so, however it just kept >> > retrying and failing to restart replication with this message: (turned >> > on >> > verbose logging) >> > >> > 'Another logreader agent for the subscription(s) is running or the >> > server >> > is >> > working on a previous request by the same agent.' >> > >> > So I kind of get the agent died and the log reader process seemed to be >> > orphaned and the agent couldn't start another log reader process. Once >> > I >> > killed the old processes, it started up again. >> > >> > But I have 2 questions: >> > >> > 1) Has anyone seen that error before that killed the sql agent? I can't >> > find >> > anything on it. >> > >> > 2) Is there anyway to get replication to keep working in the event the >> > sql >> > agent dies like that? I'd hate to think that replication can't recover >> > when >> > the agent stops unexpectedly. >> > >> > Thanks in advance. >> > >> > >> > >> >> >> |