User Configuration - Data Factory is giving error of Database is Pause but synapse DW database is running

Imran Maqsood 1 Reputation point
2021-04-02T18:50:29.823+00:00

Hi...

I have created pipeline to load data from blob storage to Azure Synapse Workspace (Sql Pool).
I am using Copy Data Activity within ForEach Activity. For some of table data is copied successfully and for few it is failing with following error how Next Iteration completes successfully.

Everything was working fine from last 2 months. Today pipeline is failing again and again.

Activity is giving following error:
Error code 2402
Failure type User configuration issue
Details Execution fail against sql server. Sql error number: 40892. Error Message: Cannot connect to database when it is paused.

and Pipeline is giving following error:
Error code : 2200

Failure type User configuration issue
Details : ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database: 'xyz.sql.azuresynapse.net', Database: 'EDW_01', User: 'sqladminuser'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Cannot connect to database when it is paused.,Source=.Net SqlClient Data Provider,SqlErrorNumber=40892,Class=20,ErrorCode=-2146232060,State=1,Errors=[{Class=20,Number=40892,State=1,Message=Cannot connect to database when it is paused.,},],'
Source
Pipeline
MASTER_DATA_EXTRACTION

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,252 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,339 questions
{count} votes

5 answers

Sort by: Most helpful
  1. KranthiPakala-MSFT 46,412 Reputation points Microsoft Employee
    2021-04-05T22:34:43.807+00:00

    Hi @Imran Maqsood ,

    Thanks for your response and sorry for the delay.

    To start a database, use the Resume Database REST API. The following example starts a database named Database02 hosted on a server named Server01. The server is in an Azure resource group named ResourceGroup1.

    POST https://management.azure.com/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/Microsoft.Sql/servers/{server-name}/databases/{database-name}/resume?api-version=2020-08-01-preview HTTP/1.1  
    

    For more info please refer to this doc: Resume compute

    84645-image.png

    In the past I have noticed the above issue occurs when two simultaneous jobs starts immediately after resuming the DW. One of the jobs executed successfully, however the other one fails with the following error: Execution fail against sql server. Sql error number: 40892. Error Message: Cannot connect to database when it is paused.

    Please try with sequential processing in your ForEach activity and hopefully that should help avoid this issue.

    Do let us know how it goes.

    Thank you

    ----------

    Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.


  2. Imran Maqsood 1 Reputation point
    2021-04-08T05:28:59.923+00:00

    Hi @KranthiPakala-MSFT ,

    I reached out to Microsoft support and they figured out this issue was from backend and they rectified it.

    thank you for your support.


  3. Imran Maqsood 1 Reputation point
    2021-04-10T07:20:56.783+00:00

    Here is the tracking id no idea about about SR #. @KranthiPakala-MSFT
    TrackingID#2104040060000273

    BR

    0 comments No comments

  4. Syed Shahid Hussain 1 Reputation point
    2021-11-02T17:14:24.613+00:00

    @Imran Maqsood is the issue been resolved? and could you please share the solution? thanks

    0 comments No comments

  5. Mustapha ALLAOUI 0 Reputation points
    2023-04-13T13:53:33.13+00:00

    HI @KranthiPakala-MSFT , We are having the same problem today. Message error bellow. is the bug still relevant? Message=Cannot connect to database when it is paused

    0 comments No comments