网上找了一些资料,发现都有各自缺点和不兼容性,综合了一下,在SQL2005和2008下测试通过:
///
<summary>
/// 终止正在被占用的数据库进程
/// </summary>
/// <param name="connectionStr"> 连接字符串 </param>
/// <param name="dataBaseName"> 数据库名称 </param>
private static void KillProcessInUse( string connectionStr, string dataBaseName)
{
SqlConnection conn;
conn = new SqlConnection(connectionStr);
conn.Open();
string sql1 = " select spid from master..sysprocesses where dbid=db_id(' " + dataBaseName + " ') " ;
SqlDataAdapter Da = new SqlDataAdapter(sql1, conn);
DataTable spidTable = new DataTable();
Da.Fill(spidTable); // 把进程名称放到DataTable中
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
cmd.CommandText = " use master " ; // 关闭用户进程语句
cmd.ExecuteNonQuery();
// 强制结束掉所有链接AnalysisSystem数据库的进程
for ( int irow = 0 ; irow < spidTable.Rows.Count; irow ++ ) // 这里Count-1也可以
{
try
{
int spid = Convert.ToInt32(spidTable.Rows[irow][ 0 ]);
if (spid < 50 )
{
continue ;
}
cmd.CommandText = " kill " + spid; // 关闭用户进程语句
cmd.ExecuteNonQuery();
}
catch
{
// TODO:Log
}
}
conn.Close();
conn.Dispose();
/// 终止正在被占用的数据库进程
/// </summary>
/// <param name="connectionStr"> 连接字符串 </param>
/// <param name="dataBaseName"> 数据库名称 </param>
private static void KillProcessInUse( string connectionStr, string dataBaseName)
{
SqlConnection conn;
conn = new SqlConnection(connectionStr);
conn.Open();
string sql1 = " select spid from master..sysprocesses where dbid=db_id(' " + dataBaseName + " ') " ;
SqlDataAdapter Da = new SqlDataAdapter(sql1, conn);
DataTable spidTable = new DataTable();
Da.Fill(spidTable); // 把进程名称放到DataTable中
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
cmd.CommandText = " use master " ; // 关闭用户进程语句
cmd.ExecuteNonQuery();
// 强制结束掉所有链接AnalysisSystem数据库的进程
for ( int irow = 0 ; irow < spidTable.Rows.Count; irow ++ ) // 这里Count-1也可以
{
try
{
int spid = Convert.ToInt32(spidTable.Rows[irow][ 0 ]);
if (spid < 50 )
{
continue ;
}
cmd.CommandText = " kill " + spid; // 关闭用户进程语句
cmd.ExecuteNonQuery();
}
catch
{
// TODO:Log
}
}
conn.Close();
conn.Dispose();
}