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
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