How to prevent SQL Server LocalDB auto shutdown?

Question

I'm using SQL Server 2012 Express LocalDB. Instances seem to stop automatically after 10 minutes if there is no activity on them. Is there a clean way to keep an instance running forever?

Singlehanded

Answer 1

The timeout is configurable via T-SQL with 'user instance timeout' option:

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'user instance timeout', 5;
GO

The timeout is expressed in minutes and has a maximum value of 65535. I'm pretty sure you need to restart the instance after setting it. And don't try setting it to 0, it will just make the instance shut down immediately after starting, which will make it hard to set the value back to something useful :-).

Source: this BOL article containing other useful information on User Instances that are applicable to LocalDB instances as well.

Final Remark

If you need something that's always running and starts whenever a computer starts you might just consider using regular, service-based, instance of SQL Server Express.

Southeastwards

Note that minimum value for timeout option is 5 so it is not possible to set timeout value below 5 minutes. – Icsh

Answer 2

Here is how to do Krzysztof Kozielczyk's answer from the command line.

Start the localdb instance.

C:\> sqllocaldb start v11.0
LocalDB instance "v11.0" started.

Get the server path, which is the Instance pipe name.

C:\> sqllocaldb info v11.0
Name:               v11.0
Version:            11.0.3000.0
Shared name:        IIS_DB
Owner:              DESKTOP-AAAT5QS\bigfo
Auto-create:        Yes
State:              Running
Last start time:    2/17/2016 12:06:43 PM
Instance pipe name: np:\\.\pipe\LOCALDB#SH9D87FB\tsql\query

Run an SQL command on that server.

C:\> sqlcmd -S np:\\.\pipe\LOCALDB#SH9D87FB\tsql\query

1> sp_configure 'show advanced options', 1;
2> GO

Configuration option 'show advanced options' changed from 1 to 1. 
Run the RECONFIGURE statement to install.

1> RECONFIGURE;
2> GO

1> sp_configure 'user instance timeout', 5;
2> GO

Configuration option 'user instance timeout' changed from 5 to 5. 
Run the RECONFIGURE statement to install.

1> RECONFIGURE;
2> GO

> exit

Mailman

i didnt have name pipe so i used this sqlcmd -S "(localdb)\MSSQLLocalDB" – Anette

To see the current setting, it is sp_configure 'user instance timeout' – Marlenamarlene