Gary Heath Gary Heath - 1 month ago 13
C# Question

Database is being used by another process ... but what process?

I have written a very small C# program, that uses a very small SQL Server database, purely for some learning & testing purposes. The database is used in this one new project and nowhere else. However, I am getting problems whilst running Debugs where the program will not run, because the database "is being used by another process".

If I reboot my machine, it will work again, and then after a few test runs I will get the same problem again.

I have found many, many similar problems reported all over the Internet, but can find no definitive answer as to how to resolve this problem. Firstly, how do I find out what "other process" is using my .mdf & .ldf files ? Then, how do I get these files released & not held in order to stop this happening time after time after time ?!?

I am new to VS2010, SQL Server & C#, so please be quite descriptive in any replies you give me !!!

This is my code, as you can see, you couldn't get anything much more basic, I certainly shouldn't be running into so many problems !!!

namespace MySqlTest
{
public partial class Form1 : Form
{
SqlConnection myDB = new SqlConnection(@"Data Source=MEDESKTOP;AttachDbFilename=|DataDirectory|\SqlTestDB.mdf;Initial Catalog=MySqlDB;Integrated Security=True");
SqlDataAdapter myDA = new SqlDataAdapter();
SqlCommand mySqlCmd = new SqlCommand();

string mySQLcmd;
int myCount;

public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
MessageBox.Show("myDB state = " + myDB.State.ToString());
//Open SQL File
myDB.Open();
MessageBox.Show("myDB state = " + myDB.State.ToString());
}

private void button2_Click(object sender, EventArgs e)
{
myCount++;
MessageBox.Show("myCount = " + myCount.ToString());
//Insert Record Into SQL File
mySqlCmd.Connection = myDB;
mySqlCmd.CommandText = "INSERT INTO Parent(ParentName) Values(myCount)";
myDA = new SqlDataAdapter(mySqlCmd);
mySqlCmd.ExecuteNonQuery();
}

private void button3_Click(object sender, EventArgs e)
{
//Read Record From SQL File
}

private void button4_Click(object sender, EventArgs e)
{
//Read All Records From SQL File
}

private void button5_Click(object sender, EventArgs e)
{
//Delete Record From DQL File
}

private void button6_Click(object sender, EventArgs e)
{
MessageBox.Show("myDB state = " + myDB.State.ToString());
//Close SQL File
myDB.Close();
MessageBox.Show("myDB state = " + myDB.State.ToString());
}

private void button7_Click(object sender, EventArgs e)
{
//Quit
this.Close();
}
}
}

Answer

The most likely options:

  1. A previous (crashed) instance of your program
  2. Visual Studio (with a Table designer open or something similar)

You can check 1) with TaskManager and 2) by looking in Server Explorer. Your db should show a small red cross meaning 'closed'.

And you should rewrite your code to close connections ASAP. Use try/finally or using(){ } blocks.

Comments