Lesson Learned #21: There is not enough space on the disk exporting BacPac using SSMS
Depending on the size of your database when you export/import the data to/from a bacpac using SQL SERVER Management Studio you could see an error like this one: There is not enough space on the disk, however, the destination/source of the data file has a lot of free space.
This issue is caused by the temporary files that SQL Server Management Studio is creating to compress/decompress the data in your %temp% folder to export/import the data to/from bacpac.
More insights of this issue:
- As you know bacpac file is, at the end, a zip file. For example, if you change the extension from bacpac to zip, you would be able to see the contents.
- For every table, SSMS needs to create in a temporal file.
- If we are exporting the data, at the final stage, SSMS needs to compress the data in a bacpac file.
- If we are importing the data, at the first stage, SSMS needs to uncompress the dataase from a bacpac file.
- Using ProcMon I saw multiple files created in the %temp% or %tmp% local folders. Some of them, are hidden, so, we need to enable to see those files. For example, I saw a file created by SSMS process and extension .model that there is a file of the bacpac contents.
- Changing the %temp% or %tmp% environment variables, pointing to a drive with enough capacity this issue was solved.