Lesson Learned #21: There is not enough space on the disk exporting BacPac using SSMS

Published 03-13-2019 06:38 PM 2,159 Views
First published on MSDN on Feb 02, 2017
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 our Windows Explorer 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.

%3CLINGO-SUB%20id%3D%22lingo-sub-368863%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2321%3A%20There%20is%20not%20enough%20space%20on%20the%20disk%20exporting%20BacPac%20using%20SSMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-368863%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Feb%2002%2C%202017%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20Depending%20on%20the%20size%20of%20your%20database%20when%20you%20export%2Fimport%20the%20data%20to%2Ffrom%20a%20bacpac%20using%20SQL%20SERVER%20Management%20Studio%20you%20could%20see%20an%20error%20like%20this%20one%3A%20%3CSTRONG%3E%20There%20is%20not%20enough%20space%20on%20the%20disk%20%3C%2FSTRONG%3E%20%2C%20however%2C%20the%20destination%2Fsource%20of%20the%20data%20file%20has%20a%20lot%20of%20free%20space.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20This%20issue%20is%20caused%20by%20the%20temporary%20files%20that%20SQL%20Server%20Management%20Studio%20is%20creating%20to%20compress%2Fdecompress%20the%20data%20in%20your%20%25temp%25%20folder%20to%20export%2Fimport%20the%20data%20to%2Ffrom%20bacpac.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20More%20insights%20of%20this%20issue%3A%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EAs%20you%20know%20bacpac%20file%20is%2C%20at%20the%20end%2C%20a%20zip%20file.%20For%20example%2C%20if%20you%20change%20the%20extension%20from%20bacpac%20to%20zip%2C%20you%20would%20be%20able%20to%20see%20the%20contents.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EFor%20every%20table%2C%20SSMS%26nbsp%3Bneeds%20to%26nbsp%3Bcreate%20in%20a%20temporal%20file.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EIf%20we%20are%20exporting%20the%20data%2C%20at%20the%20final%20stage%2C%20SSMS%20needs%26nbsp%3Bto%26nbsp%3Bcompress%26nbsp%3Bthe%20data%20in%20a%20bacpac%20file.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EIf%20we%20are%20importing%20the%20data%2C%20at%20the%20first%20stage%2C%20SSMS%20needs%20to%26nbsp%3Buncompress%20the%20dataase%20from%20a%20bacpac%20file.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EUsing%20%3CA%20href%3D%22https%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Fsysinternals%2Fprocessmonitor.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20ProcMon%20%3C%2FA%3E%20I%20saw%20multiple%20files%20created%20in%20the%20%25temp%25%20or%20%25tmp%25%20local%20folders.%20Some%20of%20them%2C%20are%20hidden%2C%20so%2C%26nbsp%3Bwe%20need%20to%20enable%20our%20Windows%20Explorer%26nbsp%3Bto%20see%20those%20files.%20For%20example%2C%20I%20saw%20a%20file%20created%20by%20SSMS%20process%20and%20extension%20.model%20that%20there%20is%20a%20file%20of%20the%20bacpac%20contents.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F88916iD431B30A15A9DF0F%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EChanging%20the%20%25temp%25%20or%20%25tmp%25%20environment%20variables%2C%26nbsp%3Bpointing%20to%26nbsp%3Ba%20drive%20with%20enough%20capacity%20this%20issue%20was%20solved.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-368863%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Feb%2002%2C%202017%20Depending%20on%20the%20size%20of%20your%20database%20when%20you%20export%2Fimport%20the%20data%20to%2Ffrom%20a%20bacpac%20using%20SQL%20SERVER%20Management%20Studio%20you%20could%20see%20an%20error%20like%20this%20one%3A%20There%20is%20not%20enough%20space%20on%20the%20disk%2C%20however%2C%20the%20destination%2Fsource%20of%20the%20data%20file%20has%20a%20lot%20of%20free%20space.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-368863%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ebacpac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eprocmon%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Essms%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Ethere%20is%20not%20enough%20space%20on%20the%20disk%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 13 2019 06:38 PM
Updated by: