SQL Express connections

pachjo

Well-known member
Joined
Dec 12, 2006
Messages
370
Programming Experience
10+
If an instance of SQL Server Express is created like this:

VB.NET:
[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cnSQLConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection([/SIZE][SIZE=2][COLOR=#0000ff]My[/COLOR][/SIZE][SIZE=2].Settings.MB2007ConnectionString)
 
cnSQLConnection.Open()
 
[SIZE=2]scnServerConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Microsoft.SqlServer.Management.Common.ServerConnection(cnSQLConnection)
[/SIZE][SIZE=2][COLOR=#008000] 
[/COLOR][/SIZE][SIZE=2]srvAdminServer = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Server(scnServerConnection)
[/SIZE][/SIZE]

Then isn't this classed as an open connection to the database in the connection string?

I am trying to restore a database and before I call the restore code I close the main application connection which allows the user to interact with the database.

But I get an error saying it cannot obtain exclusinve lock on database to restore?

I'm confused then as to how to perform a restore?

Thansk
 
This appears to be one of the offending blocks

VB.NET:
glb_taTransactions = New SqlClient.SqlDataAdapter(strSQLString, glb_cnMB2007) 

glb_taTransactions.Fill(glb_dtTransactions)

I have three similiar blocks as above.

Why do all 3 register as a single connection and not 3 connections?

Why does glb_cnMB2007.open() not register as a connection?

I thought tableadaptors were disconnected so why a connection?

Any help please?

Thanks
 
Uhm.. .NET does connection pooling, which means at any one time, X number of conenctions exist to the database to service Y number of requests. A request that is completed Close()s the connection, but the tcp connection to the database is not terminated, merely returned to a pool, waiting to service another request.

Im sure you can see the advantages to this.. Open() leases a connection.. CLose() returns it. Whether or not connections to the database are setup or torn down isnt really our concern.. However, if you want to establish an exclusive connection to the database, you will need to create a connection with a new connection string that turns off the pooling temporarily.. See the Advanced section of connection string setup where you can set Pooling=false

Dont run permananetly without pooling;; it will kill the performance of your app..
 
HI thanks for this it kind of makes sense.

So if I have the user connected to use the application with one connection and then when they click the restore button a new connection is made as you say exclusively?

But surely the problem still remains? Isn't the user still connected to the same database albeiti with another connection string?

Won't the exclusive attempt fail as the other connection is there?

Thanks
 
heh.. yeah.. the inference is to dispose of everything.. TableAdapters, DataAdapters, Connections.. anything that has possibilty to make a connection..
How about creating one pooled=false connection, using it to kill all other sessions, then do your backup?
 
HI, ok get your drift thanks.

Think I will create a proc called InitSingleUser which dumps all other stuff as you say and then recalls the original init proc that set them all up at app startup.

Thanks for you advice, at leaset I have learned some more useful stuff;)
 
Just thinking aloud.....hoping for an answer;)

Why does the call con.open() not create/register a connection yet tableadapter.fill(table) does?

Thanks
 
heh.. yeah.. the inference is to dispose of everything.. TableAdapters, DataAdapters, Connections.. anything that has possibilty to make a connection..
How about creating one pooled=false connection, using it to kill all other sessions, then do your backup?

OK my eyes are out on stalks :(:confused:

I spent all day tracking every line to identify it was the .fill that was making the connection.

Now even though I don't understand why if I call .fill from 3 different tableadapters I only get a single increase in connection count rather than 3?

But I have now created a proc to dispose of everything that is created in the intialisation proc at app startup and the connection count remains the same?

How the heck does one drop/kill/anialate a connection other than calling .dispose?

I an knacked with a capital brain ache:confused::confused:
 
OK here is the crack....

VB.NET:
'start of run shows 1 connection
[SIZE=2]ShowConnections()
 
' fill a table[/SIZE][SIZE=2]
glb_taTransactionDesc.Fill(glb_dtTransactionDesc)
[/SIZE][SIZE=2] 
'now shows 2 connections after .fll above
[SIZE=2]ShowConnections()
 
'dump the datatable and tableadaptor
[/SIZE][/SIZE][SIZE=2][/SIZE][SIZE=2][/SIZE][SIZE=2]glb_dtTransactionDesc.Dispose()
glb_taTransactionDesc.Dispose()
[/SIZE][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE] 
[SIZE=2]'still shows 2 connections after .fll above
[SIZE=2]ShowConnections()
[/SIZE][/SIZE][SIZE=2][/SIZE]

Have I missed something that I need to do to ensure the connection is dropped? Whilst trying to perform a restore I need exclusive lock which I cannot obtain as this extra connection wont drop??

Is this some hiccup caused whilst running this throught the developemnt environment?
 
OK my eyes are out on stalks :(:confused:

I spent all day tracking every line to identify it was the .fill that was making the connection.

Now even though I don't understand why if I call .fill from 3 different tableadapters I only get a single increase in connection count rather than 3?

Given that you dont perform all 3 fills simultaneously, you dont need 3 connections! One connection can be re-used. This is the essence of pooling!

Open()
'connection comes from pool. connection linked to db. use it
Close()
'connection goes back to pool. still conencted to DB
Open()
'same conenction got from pool, still conencted to DB
Close()
'connection goes back to pool
Open()
'connection got from pool yet again

...
 
OK here is the crack....

Log into the database with another connection, directly. Query the active users and derive the commands needed to force kill all other users than this current conenction out of the database. Get the server to kill all sessions. Log out and back in with excluisve. If another user has logged in meantime and exclusive lock cannot be obtained, log in normally, kill them, repeat..


Or, you could just use a real database, like Oracle, that can backup even when users are connected and active, doing things...
 
OK I still think I am missing the plot here, please be patient with me:confused:

I am the ONLY user.
I connect as a user
I connect a server object to try to restore
I close and dispose of my original connection
The server object complains that 2 connections present.

I understand the pooling etc.

What I don't understand is if I use .Close and .Dispose why does the connection still stand thus stopping me from exlusive.

Has the penny dropped here...?

Regardless of my operation above to get exclusive I have to 'thump' the other connection out of existence?

Sorry for being so painful....:eek:
 
Right, walked away from the PC last night and rest my patently frazzled brain!

So from what I can see I obviusly did not absorb what you clearly state about pooling as it all makes sense to me now.

Of course I now understand the poolng process and that in order to obtain exclusiv lock I need to force/kill the connection from the pool......

Which is what I shall work on today.;)
 
Look at the server object..... I vaguely remember comming across a method ClearPool or it might have been PoolingClear.... something to that effect. I thought of this thread when I found it, but wasn't in a position to reply.

-tg
 
Back
Top