Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am searching for an explanation of a replication snapshot being in excess of 5 times what we're predicting

Following a replication failure we are attempting to reinitialise subscriptions with a new snapshot. The size of the database is 135GB, one table within the database is 60GB - we have excluded this article from the publication. This meant we had an expectation of snapshot size being ~75GB.

We've had several attempts at creating the snapshot and these have failed due to lack of disk space. Last night the snapshot filled a disk with 340GB of free space.

I 'd welcome any explanation for this huge size of snapshot.

In my examination of possible causes I note that the snapshot format is set to "Character - Required if a publisher or subscriber is not running SQL Server". This despite the fact that at both ends of this transnational replication is native SQL Server. Is there a different in size between formats?

Thanks in advance.

Native mode file Size:105 MB
C:>bcp IVM_ArchiveTest.dbo.Event out D:\NOBACKUP\UseOnce\EventNative.dat -T -n 
218977 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) 
Total : 7878 Average : (27796.01 rows per sec.) 

Character mode file Size:66 MB 
C:\>bcp IVM_ArchiveTest.dbo.Event out D:\NOBACKUP\UseOnce\EventChar.dat -T -c 
218977 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) 
Total : 1654 Average : (132392.38 rows per sec.) 
share|improve this question
1  
Have you looked into - Initialize replication from backup ? –  Kin Sep 19 '13 at 12:54
    
Thanks Kin, we're considering this as an alternative –  reticentKoala Sep 19 '13 at 15:52

2 Answers 2

up vote 6 down vote accepted

Note that Character representation of any data will almost always exceed the original size. Depending on type, it can exceed by a large margin. Eg. an int column takes 4 bytes, but the character representation of 1000000 takes ~16 bytes as Unicode, including delimiters. That is a x4 increase right there. Dates, floats, numerics all will usually increase, and by even bigger factors than x4.

The native format alternative is the bcp utility native format (since snapshots are applied using bcp). This format is only slightly longer than the native SQL storage when comparing individual values, but it can result in actually smaller files than SQL Server required storage because the bcp file is dense: there is not page header overhead, nor partially filled pages (fragmentation, fill-factor). See Use Native Format to Import or Export Data.

Additionally you should also compress the snapshots. See Enhance General Replication Performance:

  • Use a native mode snapshot unless a character mode snapshot is required. Use the default native mode snapshot for all Subscribers except non-SQL Server Subscribers and Subscribers running SQL Server Compact, which require a character mode snapshot.
  • Use a single snapshot folder for a publication. When specifying the publication properties related to snapshot location, you can choose to generate snapshot files to the default snapshot folder, to an alternate snapshot folder, or to both. Generating snapshot files in both locations requires additional disk space and more processing when the Snapshot Agent runs.
  • Place the snapshot folder on a drive local to the Distributor that is not used to store database or log files. The Snapshot Agent performs a sequential write of data to the snapshot folder. Placing the snapshot folder on a separate drive from any database or log files reduces contention among the disks and helps the snapshot process complete faster.
  • When you create the subscription database at the Subscriber, consider specifying a recovery model of simple or bulk-logged. This allows minimal logging of the bulk inserts performed during the application of the snapshot at the Subscriber. After the snapshot has been applied to the subscription database, you can change to a different recovery model if necessary (replicated databases can use any of the recovery models). For more information about selecting a recovery model, see Restore and Recovery Overview (SQL Server).
  • Consider using the alternate snapshot folder and compressed snapshots on removable media for low-bandwidth networks. Compressing snapshot files in the alternate snapshot folder can reduce snapshot disk storage requirements and make it easier to transfer snapshot files on removable media. Compressed snapshots can, in some cases, improve the performance of transferring snapshot files across the network. However, compressing the snapshot requires additional processing by the Snapshot Agent when generating the snapshot files, and by the Distribution Agent or Merge Agent when applying the snapshot files. This may slow down snapshot generation and increase the time it takes to apply a snapshot in some cases. Additionally, compressed snapshots cannot be resumed if a network failure occurs; therefore they are not suitable for unreliable networks. Consider these tradeoffs carefully when using compressed snapshots across a network. For more information, see Alternate Snapshot Folder Locations and Compressed Snapshots.
share|improve this answer
    
Thanks! This confirms my suspicions, however, my tests don't seem to support this –  reticentKoala Sep 19 '13 at 10:52
    
Native mode C:>bcp IVM_ArchiveTest.dbo.Event out D:\NOBACKUP\UseOnce\EventNat ive.dat -T -n 218977 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 7878 Average : (27796.01 rows per sec.) file Size:66 MB Character mode C:\>bcp IVM_ArchiveTest.dbo.Event out D:\NOBACKUP\UseOnce\EventCha r.dat -T -c 218977 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1654 Average : (132392.38 rows per sec.) file Size:105 MB –  reticentKoala Sep 19 '13 at 10:53
    
I put the comment in the post to ease the read, but what I see is clearly the native file is smaller than character file. The factor will depend on the actual column types and data values. And you need to compare it with original data size (as reported by SQL). –  Remus Rusanu Sep 19 '13 at 11:21
    
Sorry, copy/paste error. The opposite seems true - Native larger than Character. Corrected file sizes in post. –  reticentKoala Sep 19 '13 at 11:35
    
It will depend on the data. eg. having many 0s or single digint numbers will represent better in Character than in native int, but then in such case your overall snapshot will be smaller than the SQL data. Make sure your test is a representative sample of the real data. Btw, I assume your SQL tables are not compressed, if they are compressed then all comparisons are off. –  Remus Rusanu Sep 19 '13 at 11:48

@RemusRusanu has best explained the native and character mode for bcp. +1 for that from me.

To deal with your situation, you can take either of the 2 different approaches.

The first one is to Initialize the subscription from a backup. Refer to How to: Initialize a Transactional Subscriber from a Backup (Replication Transact-SQL Programming). This is self explanatory and BOL has a good write up on this.

Kendal Van Dyke blogged about the second approach at Reduce Transactional Replication Synchronization Time By Applying Snapshots Manually which is excellent and is a time and life saver. Its a bit of manual process, but I have dealt with it and its a win-win approach.

share|improve this answer
    
Thanks Kin, Kendal Van Dyke's approach is interesting. –  reticentKoala Sep 23 '13 at 7:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.