Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

transactional replication: Snapshot fails at 99% Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2012 12:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:23 AM
Points: 43, Visits: 227
Publisher is sql server 2008 R2 SP2
Remote distrbutor is sql server 2008 R2 SP2

For snapshot folder I am using UNC path and all permissions are granted properly.

We have 92 tables for publication. I created test snapshot for 10 articles and synchronized subscription. It worked successfully. So, I added remaining 82 tables and reinitialized subscription with creating new snapshot.

It is consistently failing at 99%. I get the following error and snapshot agent retries and start all over, again reaches 99%, fails and retries.

I again brought the no of articles down to 20, created new snapshot and synchronized subscription. It worked successfully without issue. Again added remaining tables back, snapshot agent started retrying after reaching 99%.

So, at least I know that permissions are ok. Drive where UNC folder is 1TB. Total table size is less than 70 GB. So, we have plenty of space. we have other replication in the environmet with more than 1 TB data synchronizing. So, I know size is not an issue. It probably has something to do with time elapsed while snapshop agent bcp data out. But what?

Any idea what should I look for or what needs to be changed?

I came across some articles regarding 'Initial snapshot failes due to network caching redirect on snapshot folder' but they were applicable only to sql server 2000 and 7.0 vs I am on version 2008 R2.

======================================================
Error messages:
Message: The specified network name is no longer available.
Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint, String strWhereClause, Boolean useTableLockHint, Int32 bcpFileFormatVersion)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.BcpOutThreadProvider.DoWork(WorkItem workItem)
at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()
at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 0)
Get help: http://help/0

=====================================================
Post #1389382
Posted Wednesday, November 28, 2012 8:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:33 PM
Points: 3,938, Visits: 7,162
So, at least I know that permissions are ok. Drive where UNC folder is 1TB. Total table size is less than 70 GB. So, we have plenty of space. we have other replication in the environmet with more than 1 TB data synchronizing. So, I know size is not an issue. It probably has something to do with time elapsed while snapshop agent bcp data out. But what?
What type of storage device does this UNC point to? An external USB harddrive, a NAS? a SAN? Etc?

How much time has occurred when you receive the error that the specified network name is no longer valid?

Sounds like your storage device is having issues, but you have a couple of options: 1) you could create multiple publications (say with articles A-M, N-Z), then create 2 different snapshots. Or you could use your existing publication (adding in say 80 articles to start with), generate your snapshot, then go back and add the remaining articles, then start the snapshot agent again manually (this actually works quite well however, requires that you have tweaked a few replication settings not inherent in the default set up for transactional replication)


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1389852
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse