Tag Archives: Localdb

Profiling LocalDb

I have become quite enamoured with LocalDb and find myself preferring to use it over SQL Server or SQL Server Express. It was created for developers, so it kinda makes sense that I’d adopt it.

My first hitch came when I tried to use SQL Server Profiler to profile LocalDb. The tricky bit was connecting. A bit of Googling sorted me out and here are the steps:

  1. open the Visual Studio Command Prompt and enter Sqllocaldb info MSSQLLocalDB
  2. one of the details returned will be Instance pipe name. Copy that value to clipboard. For me it was np:\\.\pipe\LOCALDB#3B4A343D\tsql\query
  3. Open SQL Server Profiler and start a new trace. In the Server Name textbox of the Connect To Server dialog which launches, paste the pipe name.

And that’s it!

Note: if you have version 11.0 on your machine, the command to enter is Sqllocaldb info v11.0

LocalDb – Where the Dbs Live and Some Simple Facts

I always forget where to find the mdf files which are created by localdb (hence this post).
If we take a look at the connection string, it’s not too instructional as to the location:

connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=ContosoUniversity2;Integrated Security=SSPI;"

In this post, I’ll identify the locations where you are likely to find that elusive localdb database.

Local AppData
Where you haven’t created the database yourself (perhaps you downloaded a project which creates and seeds the database via EF Code First), it can usually be found in this directory:
%AppData%\..\Local\Microsoft\Microsoft SQL Server Local DB\Instances\v11.0. Note the two dots before the string, Local, in that path. Without that, you’ll be dumped into the Roaming directory, which is not the right place.

User Profile
If you don’t find it there, another place it is often found is in the User Profile – %USERPROFILE%. When you create a new database in Sql Server, either through the GUI or by running the following Sql, the default path is in the %USERPROFILE% location:

create database SpiderMonkey

SQL Server Management Studio
Ultimately, you can dictate where the localdb mdf/ldf files for a database are created. Either through the GUI in SQL Server Management Studio, or by running the following into SQL Server Management Studio’s query window:

create database SpiderMonkey on (name='SpiderMonkey', filename='c:\DBs\SpiderMonkey.mdf')

Visual Studio
If you create a localdb database using Visual Studio, the default location is: %USERPROFILE%\AppData\Local\Microsoft\VisualStudio\SSDT
The following 2 pictures outline the workflow of using Visual Studio to create a localdb database. As you can see from the 2nd picture, you can choose where the mdf files are created:


As a final comment, the whole idea of localdb is that we developers should not need to be concerning ourselves with issues about the database. The team who created localdb did so with the intention of abstracting away the database so that we could focus on development. But it is still nice to know where to find these things, even if just to clean up your hard drive!