Cannot drop SQL Server database

[wey97]

I have an ASP.NET page that connects to a SQL Server 2000 database. I want to delete the database and I get this error:

Error 3702: Cannot drop the database 'db' because it is currently in use.

I have made sure to call .Close() on all my connections and .Dispose() on all the database objects. The only way I can delete the database is to go to Task Manager and End Task on aspnet_wp.exe

It worries me the database is locked even after I close the page and the development environment.

[nemaroller]

Well, we are able to drop our database without ending the aspnet_wp process... so perhaps you are leaking a connection somewhere.

[wey97]

I've called

con.Close(); con.Dispose(); con = null;

on every connection. Am I missing something?

This is weird.

[Fishcake]

Do you have access to 'Sql Query analyser'? If so you could try running 'sp_who' procedure to check if any users/processes are connected to the database at the time.

Don't know if that helps.......

[wey97]

Good idea.
I ran EXEC sp_who from query analyzer after I open and close the page and get this:

spid	ecid	status		login		hostname	blk	dbname		cmd

1	0	background	sa				0	NULL		LAZY WRITER
2	0	sleeping	sa				0	NULL		LOG WRITER
3	0	background	sa				0	master		SIGNAL HANDLER
4	0	background	sa				0	NULL		LOCK MONITOR
5	0	background	sa				0	master		TASK MANAGER
6	0	background	sa				0	master		TASK MANAGER
7	0	sleeping	sa				0	NULL		CHECKPOINT SLEEP
8	0	background	sa				0	master		TASK MANAGER
9	0	background	sa				0	master		TASK MANAGER
10	0	background	sa				0	master		TASK MANAGER
11	0	background	sa				0	master		TASK MANAGER
51	0	runnable	domain\userName	worstationID	0	master		SELECT
52	0	sleeping	domain\userName	worstationID	0	master		AWAITING COMMAND
53	0	sleeping	domain\userName	worstationID	0	dbToDelete	AWAITING COMMAND

I can wait like 5 or so minutes, run the same proc. from Q.A. and the last line with my database name is gone.

[wey97]

If I run this code and check sp_who, the database doesn't stay open:

string strCon = "server=;database=;trusted_connection=true;";

SqlConnection sqlCon = new SqlConnection(strCon);
SqlDataAdapter sqlDat = new SqlDataAdapter("SELECT * FROM Table1;", sqlCon);

sqlCon.Close();

but as soon as I fill a DataTable, that's what does it.

string strCon = "server=;database=;trusted_connection=true;";

SqlConnection sqlCon = new SqlConnection(strCon);
SqlDataAdapter sqlDat = new SqlDataAdapter("SELECT * FROM Table1;", sqlCon);

DataTable dt = new DataTable();
sqlDat.Fill(dt);

dt.Clear();
sqlCon.Close();

crazy why filling a DataTable would keep the DB open.