Release database lock

Actually I'd like to know how I got the lock and then you can tell me how to release it. Here's the problem (this is sql server 2000 with visual studio 2005)

I have a windows service that wakes up every 5 minutes and does some database work (reads rows from a table, process them, inserts them into another table). When the inserts are complete, the process should (I would think) release the database lock, but it doesn't. The database is accessed via a dataset object and that object is disposed of via the using/end using statement. Is there something I have to put in the connection string to avoid this behavior?

Thanks,

Bob

If a connection is open from the client it will maintain an S lock on the database. This is true even if the client is doing no work. The purpose of the lock is to prevent certain operations that might change the state of the database (like say dropping it) while there are connections relying on the current state.

Is this causing you some problem?

|||I just don't understand how the connection could possibly still be open. I would assume that by using VS 2005's generated dataset code, that disposing of a dataset object would close the connection. I certainly don't want the connection open while the process sleeps for 5 minutes (or more) at a time.|||

If you are using SqlClient (and it sounds like you are) it could very well be taking advantage of connection pooling.

You can tell for certain by using profiler to monitor the Audit Login/logout events.

|||

Well this statement is no doubt in the code:

imports System.Data.SqlClient

Is that what you mean? If so, can I close the connection manually?

|||

I figured it out shortly after my last post. I added this to my connection string:

pooling=false;timeout=45;

seems to work just fine.