Initial Connection to SQL Express database is...slow.

Just set up a SQL Express database using "Microsoft SQL Server Management Studio Express" on a dedicated machine on our network.

In my program, I am basically using a SqlConnection to connect to this database via...



SqlConnectionStringBuilder sqlconnbuild = new SqlConnectionStringBuilder();

            sqlconnbuild.DataSource = "xxx.xxx.xxx.xxx\\xxxxxxxx";  //E.G. 192.168.25.24\\DBServer
            sqlconnbuild.InitialCatalog = "DatabaseNameHere";              //For example
            sqlconnbuild.ConnectTimeout = 120;                                    //I needed to set this to prevent T/O

SqlConnection conn = new SqlConnection(sqlconnbuild.ToString());
SqlDataReader reader;

SqlCommand comm = new SqlCommand("Insert SQL SELECT statement here", conn);

try
{
     conn.Open();
     reader = comm.ExecuteReader();
     //Insert general code here
}
catch (SqlException e1)
{
     MessageBox.Show(e1.Message);
}
finally
{
conn.Close();
}


But here is what happens.

I run the program (F5), I type in my login details, and then wait approximately 25 seconds and then it finally finishes and allows me access into the program.

However

If I run the program (F5), and I type the wrong login details for example, wait again for approximately 25 seconds, and obvisouly I get a message saying that I typed my login incorrectly, but if immediately after I type my correct details, the connection opens and finishes like lightning probably about 0.1 of a second or lower.

Unfortionetly every the program restarts I get the same problem on the initial login, which obviously people are only ever going to log in once so its a big problem.

Any insight would be great, thanks!

(Also the database in SQLExpress has auto-close to false, I checked that one ;o))
recruititAsked:
Who is Participating?
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

SQL Express set autoclose true for user databases.

Change this to false.

Cheers
  David

PS Running the script against model will make sure that all future databases don't auto_close. Otherwise change model to the name of your database.
alter database model
set auto_close off

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.