Disk Space Side Effects of SQL Server Data Tools

SQL Server Data Tools (SSDT) installs LocalDB, a new version of SQL Express. For details on LocalDB, please refer to Introducing LocalDB, an improved SQL Express.

As Kevin Cunnane explains in the SQL Server forum thread Importing database always defaults to localdb, creating a SQL Server database project by SSDT always creates a database on LocalDB, (localdb)\Projects since September 2012 update of SSDT. And of course, one cannot change this behavior – well, why should we, just because we do have a SQL Server installed on our dev box?

Kevin points out that the database is empty and only populated on debug/deploy. So another 4 MB are wasted on my profile drive. Why on my profile drive? Because the database files are located on %user%/AppData/Local/Microsoft/VisualStudio/SSDT. Of course, you can delete the database, also from within SQL Server Management Studio, as soon as you close the database project. But when you re-open the project, the database is re-created.

The system databases, error logs and event files are located at %user%/AppData/Local/Microsoft/Microsoft SQL Server Local DB/Instances/Projects (or V11.0, if you do not have September 2012 update or later installed). Here you can remove the event log and system health files from time to time, if LocalDB won’t do so. If you like to test it (I didn’t): the directory is set in the registry under HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\UserInstances. In case there is more than one entry below this key, have fun 😉 This seems to happen when you have SSDT installed before the September 2012 update, and then updated it.

Trying to change some settings of LocalDB using SQL Server Management Studio, like database default locations or error log recycling, led to an ‘Access denied’ exception on my machine.

But that’s not all. There is also a .dbmdl file created, having about 8 MB on my really small sample database project. This file can be deleted too, but will be re-created. Some guys on the web say that .dbmdl files are only used as a cache per user. This is why they should not be put under source control.

So in case you run out of disk space, check your profile for LocalDB database, log or health files and your projects for .dbmdl files.