Cannot drop database

[Michael C]

Hi All,

I'm trying to drop an sqlserver database from c# but can't because it is
claiming it is in use. As I don't have a connection to it it must be
connection pooling that is causing the problem. I can test this by disabling
connection pooling and the database can be dropped. Is there any way to
clear out the connection pool? I can't disable connection pooling as my app
uses it. Some sample code is below

Thanks
Michael


private void DoIt()
{
Execute("CREATE DATABASE ABC");
//remove this next line and it works
Execute("SELECT * FROM sysobjects", "ABC", false);
Execute("SELECT * FROM sysobjects", "ABC", true);
Execute("DROP DATABASE ABC");
}

private void Execute(string SQL)
{
Execute(SQL, "", false);
}

private void Execute(string SQL, string Database, bool NoPooling)
{
string connect = "Data Source=(local);Integrated
Security=SSPI;";
if (Database.Length > 0) connect += "Initial Catalog=" +
Database + ";";
if (NoPooling) connect += "Pooling=False;";
SqlConnection connection = new SqlConnection(connect);
connection.Open();
SqlCommand command = new SqlCommand(SQL, connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

I'm trying to drop an sqlserver database from c# but can't because it is
claiming it is in use. As I don't have a connection to it it must be
connection pooling that is causing the problem. I can test this by disabling
connection pooling and the database can be dropped. Is there any way to
clear out the connection pool? I can't disable connection pooling as my app
uses it.

A dirty workaround is SP_WHO and KILL !

Arne

 

[wisccal]

Have you tried SqlConnection.ClearPool(connection) ?

 

[Erland Sommarskog]

I'm trying to drop an sqlserver database from c# but can't because it is
claiming it is in use. As I don't have a connection to it it must be
connection pooling that is causing the problem. I can test this by
disabling connection pooling and the database can be dropped. Is there
any way to clear out the connection pool? I can't disable connection
pooling as my app uses it. Some sample code is below

Before you drop the database, run

USE db
ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE master


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Michael C]

Have you tried SqlConnection.ClearPool(connection) ?

Yikes! Sorry for the dumb question, I was assuming it wasn't going to be so
straight forward! I went straight to google but couldn't find the right
terms. I probably wouldn't have thought to look for static methods tho

Thanks Arne and Erland also useful information.

Michael