Help! Trans log is growing and Log Reader can't connect

I have a SQL 2000 box with latest service pack as a publisher and
distributor. The log reader agent is not able to connect and as a result the
transaction log is growing.
On the other hand, there is a connection to the distrubtion database and it
appears to be an orphan connection from the log reader agent. I have killed
the orphan connection several times but that has not resolved the issue.
Following is the error I am getting:
SPID 30: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
I believe if I can kill the connection, I will be able to start the log
reader agent successfully. I had the exact same issue yesterday and rebooting
the server did the trick. It is a production box so I can't afford to reboot
everyday like this.
What happens when you do a sp_repltrans in publisher database? You need to
set up a verboselevel to 2 and see what kind of output you get in output
file. That would be the start.
"Adam" wrote:

> I have a SQL 2000 box with latest service pack as a publisher and
> distributor. The log reader agent is not able to connect and as a result the
> transaction log is growing.
> On the other hand, there is a connection to the distrubtion database and it
> appears to be an orphan connection from the log reader agent. I have killed
> the orphan connection several times but that has not resolved the issue.
> Following is the error I am getting:
> SPID 30: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> I believe if I can kill the connection, I will be able to start the log
> reader agent successfully. I had the exact same issue yesterday and rebooting
> the server did the trick. It is a production box so I can't afford to reboot
> everyday like this.
>
|||it probably is a phantom. See if you can start up the log reader agent
again.
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
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:C47F8C71-4D3A-4823-84AE-1948DAFAF850@.microsoft.com...
>I have a SQL 2000 box with latest service pack as a publisher and
> distributor. The log reader agent is not able to connect and as a result
> the
> transaction log is growing.
> On the other hand, there is a connection to the distrubtion database and
> it
> appears to be an orphan connection from the log reader agent. I have
> killed
> the orphan connection several times but that has not resolved the issue.
> Following is the error I am getting:
> SPID 30: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> I believe if I can kill the connection, I will be able to start the log
> reader agent successfully. I had the exact same issue yesterday and
> rebooting
> the server did the trick. It is a production box so I can't afford to
> reboot
> everyday like this.
>

Help! SQL Server services and Agent Service Keep restarting

There are no indications as to why. Every 8 minutes or so the event log
records:
Event Type:Information
Event Source:Service Control Manager
Event Category:None
Event ID:7035
Date:5/29/2007
Time:10:15:49 AM
User:MEMOPROD\cluster
Computer:MEMOSQL1
Description:
The SQL Server Agent (MSSQLSERVER) service was successfully sent a stop
control.
THEN...
Event Type:Error
Event Source:ClusSvc
Event Category:Failover Mgr
Event ID:1069
Date:5/29/2007
Time:10:15:49 AM
User:N/A
Computer:MEMOSQL1
Description:
Cluster resource 'SQL Server' in Resource Group 'Disks' failed.
Event Type:Error
Event Source:Service Control Manager
Event Category:None
Event ID:7034
Date:5/29/2007
Time:10:16:50 AM
User:N/A
Computer:MEMOSQL1
Description:
The SQL Server Agent (MSSQLSERVER) service terminated unexpectedly. It has
done this 5 time(s).
Event Type:Error
Event Source:Service Control Manager
Event Category:None
Event ID:7011
Date:5/29/2007
Time:10:17:46 AM
User:N/A
Computer:MEMOSQL1
Description:
Timeout (30000 milliseconds) waiting for a transaction response from the
MSSQLSERVER service.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Any Ideas?
Hello Paul is there anything in the cluster log?
John Vandervliet
"Paul Riker" wrote:

> There are no indications as to why. Every 8 minutes or so the event log
> records:
> Event Type:Information
> Event Source:Service Control Manager
> Event Category:None
> Event ID:7035
> Date:5/29/2007
> Time:10:15:49 AM
> User:MEMOPROD\cluster
> Computer:MEMOSQL1
> Description:
> The SQL Server Agent (MSSQLSERVER) service was successfully sent a stop
> control.
> THEN...
>
> Event Type:Error
> Event Source:ClusSvc
> Event Category:Failover Mgr
> Event ID:1069
> Date:5/29/2007
> Time:10:15:49 AM
> User:N/A
> Computer:MEMOSQL1
> Description:
> Cluster resource 'SQL Server' in Resource Group 'Disks' failed.
> Event Type:Error
> Event Source:Service Control Manager
> Event Category:None
> Event ID:7034
> Date:5/29/2007
> Time:10:16:50 AM
> User:N/A
> Computer:MEMOSQL1
> Description:
> The SQL Server Agent (MSSQLSERVER) service terminated unexpectedly. It has
> done this 5 time(s).
> Event Type:Error
> Event Source:Service Control Manager
> Event Category:None
> Event ID:7011
> Date:5/29/2007
> Time:10:17:46 AM
> User:N/A
> Computer:MEMOSQL1
> Description:
> Timeout (30000 milliseconds) waiting for a transaction response from the
> MSSQLSERVER service.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> Any Ideas?

HELP! SQL Server Agent not working.

I was wondering if someone would be so kind as to tell me if i'm
missing something or just plain dumb:
I am running a 2000 Advanced Server and SQL 2000 Enterprise SP3. I
changed the SQL SERVER SERVICE account to use a Local NT User account
with no enhanced privileges standard user and another account with the
same level of permissions for the SQL SERVER AGENT.
Both services start fine and I configured the SQL server to start using
the service account thru enterprise manager. As well as the
configuration for SQL SERVER AGENT was also done in Enterprise Manager.
The file system and Program files directories are configured with the
default ACL's. There is no GPO dictating who has the "log on as service
right" and both accounts (SERVER & AGENT) have the sysadmin role in
SQL.
HOWEVER: whenever I try to run a job thru SQL SERVER AGENT it does not
run and i get a message stating that the service is not running. This
message dissapears as soon as i make the accounts members of the local
administrators group.
Would there be any way to run these 2 services under the local USER
permissions and have the jobs execute sucessfully?
ANY HELP would be appreciated. I have been doing research for more than
5 days now and can't seem to find an answer.
THANks!
Miguel A. Escalante, MCSEIs the error actually:
Error 22022: SQLServerAgent is not currently running so it
cannot be notified of this action.
Some things to check - Are you running SQL Server in fiber
mode (lightweight pooling)? Have you ensured that SQL Agent
is truly running and not hung up in a starting status? Have
you checked your sqlagent.out file?
In addition to lightweight pooling being enabled, Agent can
also get hung up due to a bad mail profile.
-Sue
On 2 Nov 2005 20:13:15 -0800, miguel.a.escalante@.gmail.com
wrote:

>I was wondering if someone would be so kind as to tell me if i'm
>missing something or just plain dumb:
>I am running a 2000 Advanced Server and SQL 2000 Enterprise SP3. I
>changed the SQL SERVER SERVICE account to use a Local NT User account
>with no enhanced privileges standard user and another account with the
>same level of permissions for the SQL SERVER AGENT.
>Both services start fine and I configured the SQL server to start using
>the service account thru enterprise manager. As well as the
>configuration for SQL SERVER AGENT was also done in Enterprise Manager.
>
>The file system and Program files directories are configured with the
>default ACL's. There is no GPO dictating who has the "log on as service
>right" and both accounts (SERVER & AGENT) have the sysadmin role in
>SQL.
>HOWEVER: whenever I try to run a job thru SQL SERVER AGENT it does not
>run and i get a message stating that the service is not running. This
>message dissapears as soon as i make the accounts members of the local
>administrators group.
>Would there be any way to run these 2 services under the local USER
>permissions and have the jobs execute sucessfully?
>ANY HELP would be appreciated. I have been doing research for more than
>5 days now and can't seem to find an answer.
>THANks!
>Miguel A. Escalante, MCSEsql

help! - could not execute sp_replcmds

I have been getting this message in the past couple of hours. I rebooted
publisher and subscriber. Even if I restart the agent - it doesn't help. I
rebuilt transactional replication from scratch and I still got this error
message. I set for the agent to output to the file and this is what I get
Microsoft SQL Server Log Reader Agent 8.00.2039
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: KOCL384017SQL-DL-69
Connecting to Publisher 'KOCL384017SQL.DL'
Server:
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[12/21/2007 10:10:53 AM]KOCL384017SQL.DL: select @.@.SERVERNAME
[12/21/2007 10:10:53 AM]KOCL384017SQL.DL: sp_MSgetversion
Status: 4096, code: 20024, text: 'Initializing'.
The agent is running. Use Replication Monitor to view the details of this
agent session.
Publisher: {call sp_repldone ( 0x000301d3000088650001,
0x000301d3000088650001, 0, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 2, code: 0, text: 'The process could not execute 'sp_replcmds' on
'KOCL384017SQL'.'.
The process could not execute 'sp_replcmds' on 'KOCL384017SQL'.
Status: 2, code: 0, text: 'Timeout expired'.
Disconnecting from Publisher 'KOCL384017SQL'
The agent failed with a 'Retry' status. Try to run the agent at a later time.
Microsoft SQL Server Log Reader Agent 8.00.2039
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: KOCL384017SQL-DL-69
Connecting to Publisher 'KOCL384017SQL.DL'
Server:
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[12/21/2007 10:16:54 AM]KOCL384017SQL.DL: select @.@.SERVERNAME
[12/21/2007 10:16:54 AM]KOCL384017SQL.DL: sp_MSgetversion
Status: 4096, code: 20024, text: 'Initializing'.
The agent is running. Use Replication Monitor to view the details of this
agent session.
Publisher: {call sp_repldone ( 0x000301d3000088650001,
0x000301d3000088650001, 0, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 2, code: 0, text: 'The process could not execute 'sp_replcmds' on
'KOCL384017SQL'.'.
The process could not execute 'sp_replcmds' on 'KOCL384017SQL'.
Status: 2, code: 0, text: 'Timeout expired'.
Disconnecting from Publisher 'KOCL384017SQL'
The agent failed with a 'Retry' status. Try to run the agent at a later time.
Microsoft SQL Server Log Reader Agent 8.00.2039
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: KOCL384017SQL-DL-69
Connecting to Publisher 'KOCL384017SQL.DL'
Server:
DBMS: Microsoft SQL Server
Version: 08.00.2040
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[12/21/2007 10:22:55 AM]KOCL384017SQL.DL: select @.@.SERVERNAME
[12/21/2007 10:22:55 AM]KOCL384017SQL.DL: sp_MSgetversion
Status: 4096, code: 20024, text: 'Initializing'.
The agent is running. Use Replication Monitor to view the details of this
agent session.
Publisher: {call sp_repldone ( 0x000301d3000088650001,
0x000301d3000088650001, 0, 0)}
Publisher: {call sp_replcmds (500, 0)}
As you can see you are getting a timeout. Set querytimeout on your logreader
agent to something large and stop the distribution clean up task. You might
also want to stop the distribution agents as well.
http://www.zetainteractive.com - Shift Happens!
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
"T" <T@.discussions.microsoft.com> wrote in message
news:C612E192-E828-4715-9A5C-5368D2719223@.microsoft.com...
>I have been getting this message in the past couple of hours. I rebooted
> publisher and subscriber. Even if I restart the agent - it doesn't help.
> I
> rebuilt transactional replication from scratch and I still got this error
> message. I set for the agent to output to the file and this is what I get
> Microsoft SQL Server Log Reader Agent 8.00.2039
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent: KOCL384017SQL-DL-69
> Connecting to Publisher 'KOCL384017SQL.DL'
> Server:
> DBMS: Microsoft SQL Server
> Version: 08.00.2040
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [12/21/2007 10:10:53 AM]KOCL384017SQL.DL: select @.@.SERVERNAME
> [12/21/2007 10:10:53 AM]KOCL384017SQL.DL: sp_MSgetversion
> Status: 4096, code: 20024, text: 'Initializing'.
> The agent is running. Use Replication Monitor to view the details of this
> agent session.
> Publisher: {call sp_repldone ( 0x000301d3000088650001,
> 0x000301d3000088650001, 0, 0)}
> Publisher: {call sp_replcmds (500, 0)}
> Status: 2, code: 0, text: 'The process could not execute 'sp_replcmds' on
> 'KOCL384017SQL'.'.
> The process could not execute 'sp_replcmds' on 'KOCL384017SQL'.
> Status: 2, code: 0, text: 'Timeout expired'.
> Disconnecting from Publisher 'KOCL384017SQL'
> The agent failed with a 'Retry' status. Try to run the agent at a later
> time.
> Microsoft SQL Server Log Reader Agent 8.00.2039
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent: KOCL384017SQL-DL-69
> Connecting to Publisher 'KOCL384017SQL.DL'
> Server:
> DBMS: Microsoft SQL Server
> Version: 08.00.2040
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [12/21/2007 10:16:54 AM]KOCL384017SQL.DL: select @.@.SERVERNAME
> [12/21/2007 10:16:54 AM]KOCL384017SQL.DL: sp_MSgetversion
> Status: 4096, code: 20024, text: 'Initializing'.
> The agent is running. Use Replication Monitor to view the details of this
> agent session.
> Publisher: {call sp_repldone ( 0x000301d3000088650001,
> 0x000301d3000088650001, 0, 0)}
> Publisher: {call sp_replcmds (500, 0)}
> Status: 2, code: 0, text: 'The process could not execute 'sp_replcmds' on
> 'KOCL384017SQL'.'.
> The process could not execute 'sp_replcmds' on 'KOCL384017SQL'.
> Status: 2, code: 0, text: 'Timeout expired'.
> Disconnecting from Publisher 'KOCL384017SQL'
> The agent failed with a 'Retry' status. Try to run the agent at a later
> time.
> Microsoft SQL Server Log Reader Agent 8.00.2039
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent: KOCL384017SQL-DL-69
> Connecting to Publisher 'KOCL384017SQL.DL'
> Server:
> DBMS: Microsoft SQL Server
> Version: 08.00.2040
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [12/21/2007 10:22:55 AM]KOCL384017SQL.DL: select @.@.SERVERNAME
> [12/21/2007 10:22:55 AM]KOCL384017SQL.DL: sp_MSgetversion
> Status: 4096, code: 20024, text: 'Initializing'.
> The agent is running. Use Replication Monitor to view the details of this
> agent session.
> Publisher: {call sp_repldone ( 0x000301d3000088650001,
> 0x000301d3000088650001, 0, 0)}
> Publisher: {call sp_replcmds (500, 0)}
>