September 27, 2005 at 1:48 pm
I am trying to replicate an 80GB spatial database from one SQL Server to another.
I've managed to successfully do this between 2 other servers using different data.
Here are the particulars..
Both servers are identical in terms of OS/SQLServer versions and patches.
I have...
1) I've set up the source server as both the distributor and publisher.
2) Defined a publication with the correct article defaults and origin / destination owner & other attributes.
3) Set up a push subscription to the destination server.
However 2/3 of the way through the snapshot process, I get the following error...
The process could not bulk copy out of table '[dbo].[syncobj_0x4144413334463736]'.
Event Viewer Text:
Event Type: Information
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 203
Date: 9/23/2005
Time: 4:44:06 PM
User: N/A
Computer: NCGCCERT01
Description:
SubSystem Message - Job 'NCGCCERT01-ssurgo-ssurgo-1' (0x845CEE63251B7C40906E6B2CD8F388FA),
step 2 - The process could not bulk copy out of table '[dbo].[syncobj_0x4144413334463736]'.
I have perused the SQL Server and Application logs and I have found no issues with full datafiles, logs or filegroups.
However from google searches it seems the general consensus is that this is an operating system level error.
At this point I am requesting those who have experienced this error in implementing transactional replication to
offer feedback on any causes for this error that I've not addressed.
Thanks in advance..
David
September 27, 2005 at 3:37 pm
Just a thought but change the agent profile to use smaller bcp batch size and a higher login time out and a higher query time out. You might also want to change the refresh rate and settings so the inactivity threshold is higher.
The agent may be taking a long time to bcp out to disk, if there is no responce after a certain time the agent will be classed as unresponsive and be marked as suspect.
May not be you issue but worth a try.
September 28, 2005 at 1:20 am
The BCP out from the table will be to the OS filesystem, so you won't see any errors in the SQL Server error log about full databases.
Have you run out of space on the drive that the BCP files are being written to?
October 5, 2005 at 4:52 pm
No the disk space for that location has plenty of room.
I am trying out Andrew's suggestions re: bcp batch size, login time out and query time out values for the Snapshot Agent profile. We shall see.
As an aside... Is there a way to override deletion of the contents of the snapshot folder upon job failure ? I'd like to see how many .idx, .sch and .bcp files it creates there before it dies.
Regards
Dave
June 19, 2006 at 9:57 am
Hello David,
I'm seeing this error in one of our SQL environments and was wondering what was done in your case to resolve this "The process could not bulk copy out of table" issue?
June 19, 2006 at 1:23 pm
Have you tried looking at the debug output using -output and -outputverboselevel?
June 20, 2006 at 2:34 pm
Not sure if this applies ... but ...
When I replicated large amounts of data, I had to use a different Distribution Agent Profile. For example, I created a profile with: Time-out increased from 300 to 8000; Skip Errors; Verbose changed from 1 to 2; Commit batch 100 to 1000; Trans per Hist 100 to 1000.
It took a bit of experimenting way back when I set it up.
July 17, 2006 at 7:01 am
Hi Andy -
I can't seem to find these in either 2000 BOL or 2005 Help.
Could you please elaborate?
Thanks!
January 5, 2008 at 7:55 am
Just a matter of adding the switches to step#2 of the job. Details on the switches in BOL at ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/repref9/html/2028ba45-4436-47ed-bf79-7c957766ea04.htm.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply