We are runnig a V12 Azure Database instance on the S3 tier. There is still about 100GB of a free space on the database. When loading 85MB XML file with SSIS running on a different non azure SQL server, and directly inserting it into the azure database, the insert crashes on the target database with the following error.
The database 'tempdb' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible
resolutions.
Is there any limit on the tempdb or any idea why this might be crashing? There is no way how the 85MB file can fill the remaining space on the database. The tempdb seems somehow hidden, how can I monitor its usage?
Best Answer
Limits depend from your service trier, in this sitiation it is no less than 250 GB for database, but it is only 32 MB for tempdb attached to this database. You can check other available resources here Azure SQL Database resource limits and information about Azure tempdb here Tempdb database in SQL Database.
There are several options which allow you to investigate what is going on with your tempdb. It is hidden, but you can still query sys objects to retrive information about this kind of databases. Try to check following resources:
When you find out what is consuming tempdb resources, you should be able to fix it or extend your question with additional information.
As you said, your file is 85 MB and we can see that it exceed max tempdb data size. Try to work with smaller chunks of data at a time. You should also consider BULK insert in this scenario.