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 using Win Server 2008 R2 / SQL Server 2008 R2 and i am trying to add about 2K + articles to the existing publication and the screen hangs @ the articles screen.

The screen has been there and hanged since 12 hours ago .

Based on the server spec : 256GB Ram with loads of core and threads, running on SSD/SAS.

it's highly unlikely due to hardware, it has to be due to some bug in the software.

When such scenario happens, what should I do to rectify or analyze the problem ?

Update :

Did some script via sp_addarticle but it still takes around 40 seconds per article.

I in fact have over 6k articles.

Sample script I used :

   EXEC Sp_addarticle 
              @publication = 'DB_live_Pub', 
              @article = N'vw_AuditLog', 
              @source_owner = N'dbo', 
              @source_object = N'vw_AuditLog', 
              @type = N'view schema only', 
              @description = NULL, 
              @creation_script = NULL, 
              @pre_creation_cmd = N'drop', 
              @schema_option = 0x0000000008000001, 
              @destination_table = N'vw_AuditLog', 
              @destination_owner = N'dbo' 

Update 2 :

I killed the job, stop all related agents, drop all publications, drop all subscriptions, Recreated new subscription from scratch. Surprisingly, everything speeds up fast. I managed to create 5k+ Articles in 2 hours. After that i proceed with the snapshot, after some error debugging, managed to create a snapshot.

Now i am creating a subscription to the publisher.

share|improve this question
    
You may want to try adding the articles via script, i.e. sp_addarticle. Does it still hang then? –  Brandon Williams Oct 17 '13 at 1:10
    
Do you have example script for views and user defined function ? It's a little complicated for me . –  Melvin Oct 17 '13 at 1:13
    
sp_addarticle (technet.microsoft.com/en-us/library/ms173857.aspx) has the parameter list and definitions, as well as an example. The type parameter will depend on the type of view for views and type will be func schema only for user defined functions I think. To be sure, you can walk through the New Publication Wizard and only select 1 or a few articles of a specific type and at the end of the wizard there is an option Generate a script file with steps to create the publication. Generate the script and inspect the sp_addarticle calls for the exact parameters. –  Brandon Williams Oct 17 '13 at 1:21
    
Another option is to use the New Publication Wizard to create the publication but only select a few articles. Then go back to the Publication Properties, right-click the publication -> Properties, and then add the remaining articles a few at a time, i.e. like 100 hundred at a time. –  Brandon Williams Oct 17 '13 at 1:23
    
i generated the script via New Publication Wizard . Did some coding and generated the rest of the scripts. now running over 1.8k views for replication. fingers crossed. –  Melvin Oct 17 '13 at 2:23

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.