Azure SQL MI Question (tempdb issue)

  • imani_technology

    SSCertifiable

    Points: 7991

    We are getting following error while dumping data from Databricks into SQL MI.

    "Error : Could not allocate space for object 'dbo.SORT temporary run storage: 140799153471488' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."

     

    Please bear in mind, we cannot set how many files or maximum size for TempDB in Azure SQL Managed Instance, it just isn't supported.

     

    We did run the truncate command, but we got this error afterward:

    "The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'."

    So, how might we fix the problem, given the peculiarities of Azure SQL Managed Instance?

  • Site Owners

    SSC Guru

    Points: 80380

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Andrey

    SSChasing Mays

    Points: 650

    hi

    in case of Managed Instance (Azure SQL DB) you have no control over the underlying instance.

    All you can do is to get understanding how big tempdb can be according to your service tier (either DTU, vCore, others).

    To prevent filling up the tempdb, you can control its size during data upload  and/or setup alerts based on current tempdb size.

     

    Offtopic:

    when I started working with Azure SQL DB 8 months ago, it became clear that a lot of things are hidden and not available in portal.azure.com interface.

    So, I started creating SSMS dashboards (custom reports) which helped me and my colleagues to get answers on the frequent practical questions  like "what is running now, how much ram is available, why my query is not progressing, what was running today early in the morning", etc.

    Dashboards are available here:

    https://github.com/ASamykin/saReports_Azure/

     

     

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply