Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unhappy Unanswered: Failed to create snapshot while bulk copy large table with TEXT/IMAGE field

    Dear all,

    Please help us on the snapshot bulk copy issue with a TEXT
    field. The snapshot agent was failed with below message:

    The process could not bulk copy into table '"Enclosure"'

    We have a big table about 170,000 records that needs to
    replicate to other sites. There is a TEXT field in this
    table. Here is the table schema..

    CREATE TABLE [dbo].[Enclosure] (
    [Identifier] [uniqueidentifier] NOT NULL ,
    [sCommentId] [varchar] (15) COLLATE
    Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
    [sEncName] [varchar] (50) COLLATE
    Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
    [sUserId] [varchar] (15) COLLATE
    Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
    [dUpdateTime] [datetime] NOT NULL ,
    [sContent] [text] COLLATE Chinese_Taiwan_Stroke_CI_AS
    NULL ,
    [msrepl_tran_version] [uniqueidentifier] NOT NULL ,
    [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
    ) ON [EXT_1] TEXTIMAGE_ON [EXT_1]

    The "Identifier" field is an auto-generated primary key
    (by newid()) of this table. The table is built on SQL
    server 2000 SP3 over Windows 2000 sever SP4.

    For this problem, I've searched in the news group and
    google for the solution. I would summarize them as below:

    1. Check disk space:
    Our disk space should be enough. We have 3GB in system
    partition and 120GB in data partition. All snapshot and
    database file are located on the data partition. The data
    file of this database that need to replicate is only 170MB.

    2. Try BCP.exe command line
    We tried to execute
    [ bcp "select * from repl_test.dbo.Enclosure" queryout
    snapshot.txt ]
    in the command line. It can finish successfully but it
    showed that it only bulk copy 36739 records out of 170,000
    records. We do not have any idea about this result.

    3. Make the TEXT field to be non-NULL
    We fill an empty character ('') for the NULL value in the
    TEXT field but the snapshot still cannot create
    successfully.

    4. Change configuration "max text repl size (B)" with
    sp_configure
    We check the maximum length of the TEXT field by [ select
    max(datalength(sContent)) from Enclosure ]. Then, we
    use "sp_configure" to set the "configure_value" of "max
    text repl size (B)" entry to be 2 times of the maximum
    length of the TEXT field. However, it shill cannot work!

    Above suggestions did not work in our problem. The
    snapshot still cannot create successfully. Finally I try
    to modify the data type of TEXT field to be VARCHAR(7500)
    then it can create snapshot successfully. Although it can
    work, it's not really what we need.

    I also tried to reduce the row number to see if bulk copy
    has row limitation. When the row number become smaller
    (about 10,000 records), the snapshot can create
    successfully with the TEXT field.

    I am not sure it's a bug or limitation of SQL server
    replication. Could anybody help us on this urgent issue?
    We are exhausted on this problem :~~. We need your advice
    about this issue.

    Thanks for your help!!

    Best regards,
    Louis
    .

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    Check the disk space for the Distribution database - if its small ( monitor space taken for 5000 rows then 10000 rows ) , try moving it to a bigger drive if need be.

    You may also want to check the snap shot replication folder location - again it could be a space issue.

    See:
    http://msdn.microsoft.com/library/de...lplan_1l4e.asp

    How to specify alternate snapshot locations (Enterprise Manager):

    In SQL Server Enterprise Manager, expand the Replication and Publications directory, select a publication, right-click the publication, and then click Properties.

    On the Snapshot Location tab, select Generate snapshots in the following location option, and then type a Universal Naming Convention path or click the browse button (...) and browse for the location where you want to save snapshot files.

    Cheers

    SG

  3. #3
    Join Date
    Oct 2003
    Posts
    4

    Unhappy

    HI SG,

    Thanks for your answer. I just adjust the file location of "distribution" and snapshot folder to a 120GB free disk partition. The source database is only 170MB. Beside this, the winnt\temp directory has 3GB free in system partition. I think the disk space should be OK.

    I just tried the bcp.exe command again. I found it only can bulk copy 36188 records out of original 170000 records. All data length of the TEXT file and total data rows are under snapshot agent's defaults.

    bcp command:

    bcp repl_test..Enclosure out enclosure.bcp /Usa

    Defaults of snapshot agent :

    M i c r o s o f t S Q L S e r v e r S n a p s h o t A g e n t 8 . 0 0 . 7 6 0
    C o p y r i g h t ( c ) 2 0 0 0 M i c r o s o f t C o r p o r a t i o n
    M i c r o s o f t S Q L S e r v e r R e p l i c a t i o n A g e n t : C R M 2 - r e p l _ t e s t - r e p l _ t e s t - 1 1

    S e r v e r :

    D B M S : M i c r o s o f t S Q L S e r v e r
    V e r s i o n : 0 8 . 0 0 . 0 7 6 0
    u s e r n a m e : d b o
    A P I c o n f o r m a n c e : 2
    S Q L c o n f o r m a n c e : 1
    t r a n s a c t i o n c a p a b l e : 2
    r e a d o n l y : N
    i d e n t i f i e r q u o t e c h a r : "
    n o n _ n u l l a b l e _ c o l u m n s : 1
    o w n e r u s a g e : 3 1
    m a x t a b l e n a m e l e n : 1 2 8
    m a x c o l u m n n a m e l e n : 1 2 8
    n e e d l o n g d a t a l e n : Y
    m a x c o l u m n s i n t a b l e : 1 0 2 4
    m a x c o l u m n s i n i n d e x : 1 6
    m a x c h a r l i t e r a l l e n : 5 2 4 2 8 8
    m a x s t a t e m e n t l e n : 5 2 4 2 8 8
    m a x r o w s i z e : 5 2 4 2 8 8

  4. #4
    Join Date
    Oct 2003
    Posts
    4

    Talking

    Additional information for the bcp.exe. I think I find the way to solve my problem.

    First, I executed

    bcp repl_test..Enclosure out enclosure.bcp /Usa

    It only can bulk copy 36188 records out of original 170000 records. So I try non-text native bulk copy with /N switch of bcp.exe

    bcp repl_test..Enclosure out enclosure.bcp /Usa /N

    With non-text native bulk copy, it can copy all 170000 records successfully. Then, I modify snapshot settings in Enterprise manager to use non-text native mode to create snapshot. The snapshop then can finished successfully!!

    I am not sure if any side effect to use non-text native snapshot creation for TEXT and IMAGE field. I will double check the result and post it for everyone that interested on this issue.

    Thanks for your concern and help!!

    Louis

  5. #5
    Join Date
    Oct 2003
    Posts
    1

    Cool Also... try setting the batch size...

    Hello louisyfwang,

    With bcp you can set the batch size ( -b option) so that it commits blocks of rows, and breakes the bulk into multiple transaction instead of attempting to transfer all at once. Essence this is mainly done in large size tables.

    Hope this helps.

    Jose R.



    Originally posted by louisyfwang
    Additional information for the bcp.exe. I think I find the way to solve my problem.

    First, I executed

    bcp repl_test..Enclosure out enclosure.bcp /Usa

    It only can bulk copy 36188 records out of original 170000 records. So I try non-text native bulk copy with /N switch of bcp.exe

    bcp repl_test..Enclosure out enclosure.bcp /Usa /N

    With non-text native bulk copy, it can copy all 170000 records successfully. Then, I modify snapshot settings in Enterprise manager to use non-text native mode to create snapshot. The snapshop then can finished successfully!!

    I am not sure if any side effect to use non-text native snapshot creation for TEXT and IMAGE field. I will double check the result and post it for everyone that interested on this issue.

    Thanks for your concern and help!!

    Louis

  6. #6
    Join Date
    Oct 2003
    Posts
    4

    Smile Re: Also... try setting the batch size...

    Hi Jose,

    Thanks for your suggestion. I can use the option of "-b" in the bcp.exe command line. Is there any way to specify this options in snapshot agent? I cannot find any "bcp.exe" command in the agent properties.

    Best regards,
    Louis

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •