Forum Discussion

DKIDV's avatar
DKIDV
Frequent Visitor
9 months ago
Solved

Fabric SQL Database SQL endpoint is not refreshing (metadata sync lag)

I dont see all my tables created in Fabric SQL Databases in the underlying SQL Analytics Endpoints (automatically created with fabric sqldb).

I have several schema's with the same tables. E.g. In schemaA (SQL Analytics Endpoint) I do see my DimDate table. In SchemaB I only see only DimTime. 

I have waited for quite some time (weeks), but the tables won't show up.

How can I refresh everything succesfully from Fabric sqldb to the sql analytics endpoint?

I want to do external data sharing, but there I saw that I am missing some tables...

 

import json
import sempy.fabric as fabric

workspace_id = "<workspace_id >"

sql_endpoint_id = "<sql_endpoint_id >" 

client = fabric.FabricRestClient()

uri = f"/v1/workspaces/{workspace_id}/sqlEndpoints/{sql_endpoint_id}/refreshMetadata"

payload = {
    "timeout": {
        "timeUnit": "Seconds",
        "value": "60"
    }
}

response = client.post(uri, json=payload, lro_wait=True)
result = json.loads(response.text)
display(result)

 

If I search on schemaB.DimDate in the output list, I dont see it.

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 months ago

    Hi DKIDV,

     

    Table count exceeds limit is the cause. It is not just a UI message. May be The issue is not caused by permissions or refresh problems the main reason your tables are missing in the SQL Analytics Endpoint is because your database has hit the internal table count limit. Once this limit is reached, the SQL Analytics Endpoint simply stops registering new tables, even though replication still shows as “Running” and the tables exist in the SQL Database. This is why older schemas appear while newer ones don’t, even when the tables are identical. The refreshMetadata API and small table changes won’t fix it because the endpoint is no longer accepting new metadata. The only real solutions are to reduce the number of tables in the database (drop unused or duplicate tables) or split your data across multiple databases using Fabric Pipelines to move data between them. This avoids the limit and ensures all tables become visible and sharable again.

     

    Thanks & Regards,

    Prasanna Kumar

10 Replies

  • DKIDV's avatar
    DKIDV
    Frequent Visitor
    8 months ago

    Hi MFelix ,

    Thanks for the reply!
    I just double check it and my account do have the right permissions. 

    Minor table changes also doenst trigger sql analytics endpoint. 

    • MFelix's avatar
      MFelix
      Super User
      8 months ago

      Hi DKIDV ,

       

      Let me check on my side and i will get back to you ASAP.

  • Nasif_Azam's avatar
    Nasif_Azam
    Super User
    8 months ago

    Hey DKIDV ,

     

    1) Check replication status for the missing tables: From the SQL database item (not the endpoint):

    1. Open the database in the Fabric SQL query editor.
    2. Use the menu at the top: Replication → Monitor replication.
    3. On that page you should see all tables and their status (Completed / Replicating / Error) and you can use the Refresh button to recheck.

    If the table failed to replicate originally (for example due to capacity/resource pressure or schema issues), the endpoint can’t surface it until replication succeeds. A very similar “tables visible in SQL DB but missing in endpoint” case is documented as a replication/sync issue, with MS support recommended as the final step.

    Query the SQL analytics endpoint of your SQL database in Fabric

    Discrepancy in Table Visibility

     

    2) Using the refreshMetadata API: You’re calling the right REST API:

    uri = f"/v1/workspaces/{workspace_id}/sqlEndpoints/{sql_endpoint_id}/refreshMetadata"
    payload = {
        "timeout": {"timeUnit": "Seconds", "value": "60"}
    }
    response = client.post(uri, json=payload, lro_wait=True)

     

    This is the documented way to force metadata sync on a SQL analytics endpoint for lakehouse, warehouse, mirrored and native SQL database items.

    SQL analytics endpoint in Fabric

    Items - Refresh Sql Endpoint Metadata

     

    3) Check for table / feature limitations: If replication is failing on specific tables/schemas only, it’s worth checking whether those tables use any features that are constrained in Fabric SQL DB or its SQL analytics endpoint. For example:

    • Certain table types (like in-memory / ledger tables) aren’t supported in Fabric SQL Database. 
    • The analytics endpoint itself has limitations around schema auto-generation and supported types (inherited from the warehouse / lakehouse endpoint behavior).

    Sometimes doing a small schema change (e.g. add a dummy column, then drop it) can retrigger replication for a table but if it still doesn’t show up in Monitor Replication or in the API result afterwards, it’s very likely a product issue rather than something you can fix from the UI.

    Limitations in SQL database in Microsoft Fabric

    Limitations of Fabric Data Warehouse

     

    4) External data sharing vs. workaround: For External data sharing, you’re correct: it relies on the SQL analytics endpoint, so missing tables there will also be missing from the share.

    As a temporary workaround for reporting only (not for External data sharing), you can:

    1. Go to your SQL Database item in Fabric.
    2. Use … → Open in VS Code / SSMS and copy the Server Name and Database Name.
    3. In Power BI Desktop, create a new SQL Server connection with those values and build/refresh your model directly against the database (or against the endpoint connection string if you prefer).
    4. Publish and, in the Service, point the semantic model to that connection.

    That gets you unblocked for reporting, but it doesn’t “fix” the missing table in the SQL analytics endpoint for External data sharing.

    Connect to your SQL database in Microsoft Fabric

     

    Best Regards,
    Nasif Azam

     

    • DKIDV's avatar
      DKIDV
      Frequent Visitor
      8 months ago

      Hi Nasif_Azam , Anonymous, MFelix 
      thanks for the reply.

      I now understand the limitations of fabric sql db why some tables are not visible in sql analytics endpoint. e.g. I have some tables with PK's with unsupported data types. Fixing this will not solve 100% my initial question.


      Since I want to share different schema's with different tenants e.g. schema A external data share with tenant A, schema B external data share with tenant B. We now have all data in one database, because I think it is not possible to use cross-database with several fabric sql database to operate ETL from database A to database B.

      I use pipelines in fabric and not data flow gen 2. If you know a solution for this, I can operate with multiple databases in stead of 1. There are more tenants, because of client data that we want to seperate. 

       

      As you can see in the screenshot below: in the 'IM' schema you can see the DimDate table, in sql analytics endpoint you can't see the DimDate table. For some reason the other schema's I can see the IM_xxxxxx.DimDate table in sql analytics endpoint. These tables are 100% the same.
      In this example it is just a simple table, no PK's whatsoever.

      So unfortunately I have this problem for quite a lot of tables in different schema's.



      Other tables that I do not see at all in the sql analyitcs endpoint doesn't have a 'Failed', 'Stopped', 'RunningWithWarnings' status.

      Minor table changes also doens't trigger the visibility in sql analytics endpoint.

      The error that you see in the screenshot: 'Table count exceeds limit', is that a cause? or just a UI thing?

      Thank you in advance!

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 months ago

        Hi DKIDV,

         

        Table count exceeds limit is the cause. It is not just a UI message. May be The issue is not caused by permissions or refresh problems the main reason your tables are missing in the SQL Analytics Endpoint is because your database has hit the internal table count limit. Once this limit is reached, the SQL Analytics Endpoint simply stops registering new tables, even though replication still shows as “Running” and the tables exist in the SQL Database. This is why older schemas appear while newer ones don’t, even when the tables are identical. The refreshMetadata API and small table changes won’t fix it because the endpoint is no longer accepting new metadata. The only real solutions are to reduce the number of tables in the database (drop unused or duplicate tables) or split your data across multiple databases using Fabric Pipelines to move data between them. This avoids the limit and ensures all tables become visible and sharable again.

         

        Thanks & Regards,

        Prasanna Kumar

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 months ago

    HI DKIDV,

     

    Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to MFelix and Nasif_Azam  for prompt and helpful responses.

    Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

     

    Best regards,
    Prasanna Kumar

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 months ago

    Hi DKIDV,

     

    Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

     

    Best regards,
    Prasanna Kumar