When I try to connect to a database that was previously opening with a SQL connection I get a "File in Use" error. Process Explorer tells me that sqlservr is still holding the file. Any ideas on how to get sql to release the file from within the vb.net 2010 code?
A:
Which file is this? Is it a file that's normally internally managed by the SQL server? If so, then you'll probably have to ask the SQL server to shut itself down before you can open the file.
You may also want to consider your reasons for wanting to access such a file. If it's for backup purposes, you are almost always better off using the services of your database to perform backups, instead of trying to do it yourself.
Greg Hewgill
2010-08-31 04:38:14
Thanks Greg, I have an application that opens a database relating to a particular set of data, then when finished with the use may want to open another database and work on that, then may want to go back and work on the first database and this is where it comes unstuck as the first database is still held by SQL. Much like Word or Excel. What is also strange is that when I compile and run this program on my XP laptop it opens and closes files all day long without a problem??
Brad Henderson
2010-08-31 04:59:22
It's still not clear to me whether you are asking your SQL server to open the file, or whether you're trying to open the actual disk file yourself.
Greg Hewgill
2010-08-31 05:19:13
I could be doing this wrong, but I use a SqlDataAdapter to fill the DataTable with the connection string to the database. After I have filled the DataTable I close the connection with close() but still the SQL Server holds the database in use.
Brad Henderson
2010-08-31 05:32:02
A:
After you closed the connection you must call
SqlConnection.ClearPool
or
SqlConnection.ClearAllPools
in order to release the files from the SqlServer.
BitKFu
2010-08-31 05:24:11
BitKFu, That was it. Thanks, I didn't know ClearPool existed, 26 hours of searching is now over. Thanks Again.
Brad Henderson
2010-08-31 05:39:23