How can I specify that my app should start SQL Server LocalDb with a trace flag parameter?

Question

My application uses a LocalDb instance to store it's data. I've noticed a quirk in the way that LocalDb has allocated identifies (explained more here) which I'd like to avoid. The linked answer explains that this can be done by setting an SQL Server trace flag, and explains how to do this for "full fat" SQL Server.

Is it possible to set a trace flag on LocalDb? As my application starts it on demand, could this be done through the connection string?

Answer 1

I have a similar problem and believe I have a workaround...

Launch Regedit and find your localdb registry key. For me this was

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12E.LOCALDB\MSSQLServer\

Within this key create another key named Parameters.

Within the Parameters key create a string (REG_SZ) named SQLArg0 and set its value to -T272.

Stop the LocalDB process by running SQLLOCALDB.EXE in a command prompt, passing in stop as a parameter and the name of the instance. For example:

C:\> sqllocaldb stop mssqllocaldb

And then cause LocalDB to auto start by connecting to a database on it.

Run the query DBCC TRACESTATUS() and you should see that the trace flag 272 is present. For example:

C:\>sqlcmd -S (LocalDB)\MSSQLLocalDB -Q "DBCC TRACESTATUS();"

To test this I've been using VS2015 server explorer to explore the database. Killing the LocalDB process in Task Manager (sqlservr.exe), connecting to the database in VS2015 and adding a new row manually causes my auto-increment value to jump by 1000. After adding the registry hack described above, manually terminating the LocalDB process doesn't cause the next generated identity value to skip.

HTH