open connections to the database

[rock_finn]

is there a way to know if i have open connections to my database (i'm using sql server 2000)? or is there a way to close all those connections? coz i'm trying to backup and restore my database. i'm having problems with my restore, saying that "Exclusive access could not be obtained because the database is in use."

one more thing, what command can i use to close all my open forms at once? for example, i have my main form, then i open my Restore form. after my restore is done, i want my application to close automatically, even if my Restore form and my main form is still open.

thanks for the help.

 

[cjard]

the database connection pooling mechanism may be keeping spare connections to the database open.. is it your app's responsibility to backup the database?

one more thing, what command can i use to close all my open forms at once? .

What happens when you call Application.Exit() ?

 

[rock_finn]

yes, my app has a module for backup and restore of the database.

thanks, Application.Exit works fine.

 

[cjard]

ok.. well be aware if there are multi users, you might hit the same thing again.. and another thing worthy of note with Application.Exit() - it asks the app to close, but the app doesnt close at the point of calling it.. I'm having some small issue with this in that if your app has any minimized MDI chiildren forms, they seem to close and nothing else when you first call APplication.Exit().. calling it a second time closes the app, but im trying to find out why it doesnt work first off..

 

[rock_finn]

ok, thanks. i'll try to look into that small issue with ApplicationExit().

 

[dellman]

I am not sure if you have your full answer yet, but in order to close off any active connections to your database from a single instance add a Pooling=False to your connection string. This will cause both SQL 2000 and SQL 2005 to flush their cache of connections that are not currently in use. As cjard put it, if you have other users connecting after you call this and they do not have the pooling=false on their connection string then you will run into the same issue you are now. please note that pooling=false will not work on machines that are serving from Media Center Edition. I dunno who uses MCE to serve from, but that is a bug to the best of my knowledge that MS has stated they will not be fixing.

If you do not want to worry about the bug in Application.Exit you can still use END in your software and that will Kill everything. I am not 100% certain how End reacts to multi-threaded apps tho so you might want to test that or make darn sure that you exit all your threads before you call END since if its not handled correctly you will get orphan threads in the thread pool.

 

[cjard]

End (and its .NET corolaray Environment.Exit()) are generally to be avoided as dellman says, they dont shut things down that nicely. Where reasonably possible you should devolve db backup responsibilities to a separate process entirely, maybe a scheduled task that runs once a week or the first user to connect on monday.. if its atrue multi user system where you dont have that much control over the peons.

That said, we use oracle at work and the live system is backed up every night without disturbing any users who are logged on. investigate whether your db tech can do the same

 

[rock_finn]

i'm using this connection string for my vb.net application, "server=pating;Trusted_Connection=false;user id=sa;password=;database=ANTAT". should i just add "pooling=false" to my connection string?
i guess if all else fails. the best option is what cjard said, like having a separate process just for my backup and restore. right now, the users of the application use the Enterprise Manager manually for that. but they want to have a backup and restore utility thats why i'm trying to have that in my application.
thanks, dellman and cjard for the inputs!!!

as for the Application.exit(), its working right now. i'm not sure if i'll encounter some problems with it in the future. i hope not!
well, thanks again.

 

[cjard]

i personally dont recommend switching pooling off, but it depends how your app is written. Microsoft advocate that you open a connection, use it and close it. with pooling on, opening and closing merely acquire and return connections from/to the pool. the pool maintains the connections.

if you have written your app in good microsoft practice, then it will suffer a huge performance drop if you switch off pooling because every open and close really will connect and disconnect from the database. this applies too if you are using tableadapters and other MS niceties to connect your db (microsoft obey their own recommends )

if however, you open a connection at the start of the app, store it and use it throughout your app (not recommended) and close it at the end, never using any microsoft DB helpers like tableadapters, then your app is OK to have pooling switched off. i dont recommend you interact with a DB in this way but it's your choice to do so

eeek.. you give your users EM? wow.. ok.. i'm SURE there are command line utilities that can be used to backup and restore the db, no need for EM..

 

[dellman]

what cjard says is 100% and I align my recomendations with his. I use Pooling=false in very specific circumstances in my apps and only when I am designing for local servers such as MSDE or SQLEXPRESS. To reinforce cjard's point, I did some testing with Pooling=false on a sample app writing 10,000 records individually. Your results may vary, but I got no less than approx 45% drop in overal performance! I did not shut down other services running on my machine or try to ensure that the test was 100% fair, but it gives you an idea of the reasons most developers have very little use for it.

As a general rule, however, I do avoid the use of END and stick to Application.Exit() and I have not run into the problems as specified by cjard, but I will keep an eye out for them of course.

 

[rock_finn]

i didn't mean all users have access to EM. only the the db administrator of our client has access to that. but the same db admin i think is a user of the apps. thats why he wants that backup/restore facility.
i only came to the project team in the latter part of the development, so i'm not sure how they come up with the design of the application. but anyway...
thanks again.