Aug 16, 2011
tom

SQL 2005 Replication Error – Upgraded SP3

Question

I recently upgraded my SQL 2005 server to SQL 2005 SP3. I have replication from SQL 2005 to SQL 2005 both are standard edition. During the upgrade I experienced no issues and the replication that was already setup continue to work great. I recently, tried to create a new publication for a new TEST account and I am getting the following error:

“The concurrent snapshot for publication ‘TESTACCT’ is not available because it has not been fully generated or the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted, the Snapshot Agent for the publicat”

I have tried to drop the replication on the DB.
exec sp_replicationdboption @dbname = N’TESTACCT’, @optname = N’publish’, @value = N’false’

I than recreated publication and and subscription.

The snapshot was created successfully and the Log Reader Agent is running. When I view the subscription synchronization status, I get the error message above. I turned on logging and did not see anything that stood out.

010-07-19 19:43:53.065 Microsoft SQL Server Log Reader Agent 9.00.4035.00
2010-07-19 19:43:53.065 Copyright (c) 2005 Microsoft Corporation
2010-07-19 19:43:53.065 Microsoft SQL Server Replication Agent: logread
2010-07-19 19:43:53.065
2010-07-19 19:43:53.065 The timestamps prepended to the output lines are expressed in terms of UTC time.
2010-07-19 19:43:53.065 User-specified agent parameter values:
-Publisher TEST-APP-DB
-PublisherDB TESTTEST
-Distributor TEST-APP-DB
-DistributorSecurityMode 1
-Continuous
-OutputVerboseLevel 2
-Output E:Distribution_Agent2.log
-XJOBID 0x046D19B54A2ACF43A7AD2BF20D05CAAF
-XJOBNAME TEST-APP-DB-TESTTEST-15
-XSTEPID 2
-XSUBSYSTEM LogReader
-XSERVER TEST-APP-DB
-XCMDLINE 0
-XCancelEventHandle 00000440
-XParentProcessHandle 0000047C
2010-07-19 19:43:53.205 Connecting to OLE DB TEST-APP-DB at datasource: ‘TEST-APP-DB’, location: ”, catalog: ‘TESTTEST’, providerstring: ” using provider ‘SQLNCLI’
2010-07-19 19:43:53.221 OLE DB TEST-APP-DB: TEST-APP-DB
DBMS: Microsoft SQL Server
Version: 09.00.4053
catalog name: TESTTEST
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: ”
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2010-07-19 19:43:53.221 OLE DB TEST-APP-DB ‘TEST-APP-DB': select is_srvrolemember(‘sysadmin’), is_member (‘db_owner’)
2010-07-19 19:43:53.221 OLE DB TEST-APP-DB ‘TEST-APP-DB': select db_id()
2010-07-19 19:43:53.221 Disconnecting from OLE DB TEST-APP-DB ‘TEST-APP-DB’
2010-07-19 19:43:53.221 Parameter values obtained from agent profile:
-pollinginterval 5000
-historyverboselevel 1
-logintimeout 15
-querytimeout 1800
-readbatchsize 500
-readbatchsize 500000
2010-07-19 19:43:53.237 Connecting to OLE DB Publisher at datasource: ‘TEST-APP-DB’, location: ”, catalog: ‘TESTTEST’, providerstring: ” using provider ‘SQLNCLI’
2010-07-19 19:43:53.237 OLE DB Publisher: TEST-APP-DB
DBMS: Microsoft SQL Server
Version: 09.00.4053
catalog name: TESTTEST
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: ”
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2010-07-19 19:43:53.237 OLE DB Publisher: TEST-APP-DB
DBMS: Microsoft SQL Server
Version: 09.00.4053
catalog name: TESTTEST
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: ”
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2010-07-19 19:43:53.237 OLE DB Publisher ‘TEST-APP-DB': select @@SERVERNAME
2010-07-19 19:43:53.237 Connecting to OLE DB DISTOLE at datasource: ‘TEST-APP-DB’, location: ”, catalog: ‘distribution’, providerstring: ” using provider ‘SQLNCLI’
2010-07-19 19:43:53.237 OLE DB DISTOLE: TEST-APP-DB
DBMS: Microsoft SQL Server
Version: 09.00.4053
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: ”
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2010-07-19 19:43:53.237 OLE DB DISTOLE: TEST-APP-DB
DBMS: Microsoft SQL Server
Version: 09.00.4053
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: ”
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2010-07-19 19:43:53.237 OLE DB DISTOLE ‘TEST-APP-DB': select datasource, srvid from master..sysservers where upper(srvname) = upper(N’TEST-APP-DB’)
2010-07-19 19:43:53.237 OLE DB Publisher ‘TEST-APP-DB': sp_MSgetversion
2010-07-19 19:43:53.252 Status: 4096, code: 20024, text: ‘Initializing’.
2010-07-19 19:43:53.252 The agent is running. Use Replication Monitor to view the details of this agent session.
2010-07-19 19:43:53.252 OLE DB DISTOLE ‘TEST-APP-DB': sp_MSget_last_transaction @publisher_id = 0, @publisher_db = N’TESTTEST’, @for_truncate = 0x0
2010-07-19 19:43:53.252 OLE DB DISTOLE ‘TEST-APP-DB': sp_MSquery_syncstates 0, N’TESTTEST’
2010-07-19 19:43:53.252 OLE DB DISTOLE ‘TEST-APP-DB': sp_MSget_last_transaction @publisher_id = 0, @publisher_db = N’TESTTEST’, @for_truncate = 0x1
2010-07-19 19:43:53.252 Publisher: {call sp_repldone ( 0x00022b330000086000a2, 0x00022b330000086000a2, 0, 0)}
2010-07-19 19:43:53.252 Publisher: {call sp_replcmds (500, 0, 0, , 0, 500000)}
2010-07-19 19:43:53.252 OLE DB DISTOLE ‘TEST-APP-DB': sp_MSget_last_transaction @publisher_id = 0, @publisher_db = N’TESTTEST’, @for_truncate = 0x1
2010-07-19 19:43:53.252 Publisher: exec sp_replcounters N’TESTTEST’
2010-07-19 19:43:53.252 OLE DB Publisher ‘TEST-APP-DB': exec sp_replcounters N’TESTTEST’
2010-07-19 19:43:53.252 Status: 16384, code: 20007, text: ‘No replicated transactions are available.’.
2010-07-19 19:43:58.252 Publisher: {call sp_replcmds (500, 0, 0, , 5000, 500000)}
2010-07-19 19:44:03.252 Publisher: {call sp_replcmds (500, 0, 0, , 5000, 500000)}
2010-07-19 19:44:08.268 Publisher: {call sp_replcmds (500, 0, 0, , 5016, 500000)}
2010-07-19 19:44:13.268 Publisher: {call sp_replcmds (500, 0, 0, , 5000, 500000)}
2010-07-19 19:44:18.268 Publisher: {call sp_replcmds (500, 0, 0, , 5000, 500000)}
2010-07-19 19:44:23.268 Publisher: {call sp_replcmds (500, 0, 0, , 5000, 500000)}
2010-07-19 19:44:28.268 Publisher: {call sp_replcmds (500, 0, 0, , 5000, 500000)}

Any suggestions would be greatly appreciated.

Thanks,
Brennan

Answer

I cleared out the problematic records from the MSsubscriptions table in the distribution database.

BEGIN TRANSACTION
DELETE FROM dbo.MSsubscriptions
WHERE publisher_db = ‘TESTDB’

SELECT * FROM dbo.MSsubscriptions
WHERE publisher_db = ‘TESTDB’

ROLLBACK/COMMIT

Related posts:

  1. Sql Server 2005 Replication to Sql Server 2008 R2 Express
  2. How can I tell if SP3 is installed for my SQL Server 2005 instance?
  3. Creating indexes on tables filled from transactional replication – SQL 2005
  4. SQL Server 2008 – Performance impact of transactional replication?
  5. Error when running a query on a linked server on SQL 2008 that connects to a SQL 2000 database

Leave a comment