Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


transactional replication: Snapshot fails at 99%


transactional replication: Snapshot fails at 99%

Author
Message
Aashini Shah
Aashini Shah
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 231
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%.
:-D
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

=====================================================
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4905 Visits: 7365
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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search