none
SSRS Data Driven Subscription not work partially

    質問

  • I have configured SSRS of SQL Server 2012 SP2 on Windows Server 2008 R2.
    Then, I tried to configure Data-Driven Subscription.
    But I had a problem that Data-Driven subscriptions do not work partially and got an EventID 107.
    For instance, i query 10 addresses at data-driven subscription. then 1 to 3 addresses fail, others succeed. 
    All queried addresses do not fail.

    The following is error message (I translated it from Japanese into English.)
    ----------------------------------------------------
    Log nmae: Application
    Event Source: Report Server Windows Service ([myInstance])
    Event Data: 2015/11/12 18:00:57
    Event ID: 107
    Event Category: Management 
    Event Level: error
    Event Keyword: classic
    User: N/A
    Computer: myServer
    Description:
    Report Server Windows Service ([myInstance]) cannot connect to the report server database.
    ----------------------------------------------------

    And I also have SSRS Error in ReportServerService Logs:
    System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.

    System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
    System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
    System.Data.SqlClient.SqlConnection.Open()
    Microsoft.ReportingServices.Library.ConnectionManager.OpenConnection()
    ----------------------------------------------------
    From the above error, I have to adjust the parameters related to the time-out.
    <Report Manager>
    ・Report time-out: no use 
    ・Data-Driven Command time-out: 3000sec
    <rsreportserver.config>
    ・DatabaseQueryTimeout: 120(sec)
    ・RecycleTime: 720(min)
    <web.config>
    ・httpRuntime executionTimeout: 9000
    <Machine.config> * 4CPU
    ・connectionManagement.maxConnection: 48
    ・processModel.maxWorkerThreads: 100
    ・processModel.maxIoThreads: 100
    ・processModel.minWorkerThreads: 50
    ・httpRuntime.minFreeThreads: 352
    ・httpRuntime.minLocalRequestFreeThreads: 304
    ・httpRuntime.executionTimeout: 1800
    ...

    It had no effect...

    Or will I have failed to configure the account for DB Login or SSRS?
    Why  partially fail ?

    How to solve  this problem?  
    Please. suggest.

    Thank you.


    ※The figure below is my server configuration diagram.

    2015年11月16日 15:54

回答

  • Hi Koba33,

    According to your description, you have created a data driven subscription, but not all report copies are sent, right?

    Based on my research about the error message “System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.”, the same issue can be caused by that the reporting services was not managing the queue threads correctly. You can open the rsreportserver.config file (default location: %Program Files%\Microsoft SQL Server\MSRS<RS Instance Name>\Reporting Services\ReportServer\), and search for the following line:

    <MaxQueueThreads>0</MaxQueueThreads>

    Change the 0 value to the number 4, save changes and restart the Reporting Services service for the change to take effect. If issue persists, please change the value to the number 5 and try again.

    Another cause can be the Max Pool size is not able to provide sufficient size to process all connections. Please refer to this article: "System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.".

    If you have any question, please feel free to ask.

    Best regards,
    Qiuyun Yu


    Qiuyun Yu
    TechNet Community Support



    2015年11月17日 3:18
    モデレータ

すべての返信

  • Hi Koba33,

    According to your description, you have created a data driven subscription, but not all report copies are sent, right?

    Based on my research about the error message “System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.”, the same issue can be caused by that the reporting services was not managing the queue threads correctly. You can open the rsreportserver.config file (default location: %Program Files%\Microsoft SQL Server\MSRS<RS Instance Name>\Reporting Services\ReportServer\), and search for the following line:

    <MaxQueueThreads>0</MaxQueueThreads>

    Change the 0 value to the number 4, save changes and restart the Reporting Services service for the change to take effect. If issue persists, please change the value to the number 5 and try again.

    Another cause can be the Max Pool size is not able to provide sufficient size to process all connections. Please refer to this article: "System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.".

    If you have any question, please feel free to ask.

    Best regards,
    Qiuyun Yu


    Qiuyun Yu
    TechNet Community Support



    2015年11月17日 3:18
    モデレータ
  • Hi Qiuyun Yu,

    Thank you for the great suggestion !!

    And i'm sorry my reply becomes late.
    I needed time to validate your proposal.

    As a result, my problem has been almost solved.
    The following, i describe the verification contents.

    • Report DataSet: 10 types (each about 10 rows)
    • Destination Address: 100 cases
    • Report Time-out: none
    • Data-Driven Subscription Command Time-out: 60 sec 
    • Include Report: True
    • Render Type: HTML

    ...
    The following table represents the transmission result and MaxQueueThreads value.

    ※Column 7: I have set the Log Backup of DB at every hour from 9 am to 9 pm.

    From these result, i think

    • Sometimes just hourly Subscription will fail. (ex. Just 7:00 am)
    • If MaxQueueThreads value is between 4 to 6, the data-driven subscriptions can send all even if SSRS error has occurred. When MaxQueueThreads value was 0 (default), SSRS error occured and some subscriptions could not be sent.(before your suggestion)


    Therefore, i decided param of Data-Driven Subscription to the following.

    • MaxQueueThreads value is 6 (between 4 to 6)
    • Avoid executing subscription at Just Hour
    I'm going to observe behavior on this condition for a while.

    In addition, I have some questions.
    Q1:
    What do the degree of MaxQueueThreads ? Also, does it rely on what ?
    (CPU Count or Memory or DataSets of Report...?)
    Although I have read the following Web Page, I could not understand it well.
    RSReportServer Configuration File

    Q2:
    What do you think that 'Just Hour' subscription will fail in my test ?

    Please let me ask these questions.

    Thank you.

    Koba33





    • 編集済み Koba33 2015年11月19日 17:29
    2015年11月19日 16:52
  • Hi Koba33,

    • Q1:
       What do the degree of MaxQueueThreads ? Also, does it rely on what ?
       (CPU Count or Memory or DataSets of Report...?)

    Regarding this question, I think Mike's explanation is very clear. See: What steps can be taken to improve the performance of Data Driven Subscriptions (MaxQueueThreads) ?

    • Q2:
       What do you think that 'Just Hour' subscription will fail in my test ?

    From the list, I see that on 7:00, there will be one report replica not be sent. To find the root cause, we need to check the Reporting Services trace log. see: Troubleshooting Subscriptions: Part II, Using the Reporting Services Trace Log File.

    If you have any question, please feel free to ask.

    Best regards,
    Qiuyun Yu


    Qiuyun Yu
    TechNet Community Support


    2015年11月23日 11:25
    モデレータ