sql server express loses ability to write to database

sql server express loses ability to write to database

• Elkoda

  There is a paper about Understanding and Avoiding Blocking on MSDN. This paper was written for SQL Server 2000, but the concepts are the same. This will give you some background on what may be the problem and a place to start your investigation.

  Regards,

  Mike

  
  
  

• Ed M

  Agreed.

  Like I said, I'm sure you can track down this problem with enough troubleshooting, for example using sp_who2 when the problem happens and looking at other things such as the Activity Monitor. You need to make a reasoned decision about how much time you're willing to put into the activity. There doesn't seem to be an obvious cause, so the rest is brute force. That's the nature of troubleshooting.

  Mike

  
  
  

• JP.

  My guess is that this is the result of Connection Pooling, which I'm sure you can find a host of information about on the web. The short answer is that your application automatically has a pool of connections available to use. When you close a connection, you are actually releasing it back to the pool so that it can be used elsewhere.

  A connection in the pool is "closed" but it's still hanging around in an idle state. The mechanics of how connection pooling works and why it's a good thing are beyond me. If you are really interested, you can probably find more information in BOL or you might want to start up a conversation in the Database Engine forum with the guys who know more about this.

  For small applications such as those build on Express, it's typically enough that it works.

  Mike

  
  
  

• Tibor den Ouden

  It is going to take some effort to examine the situation in the way you describe since the problem is infrequent and it is a matter of being there in order to deal with it.

  In the meantime, I have given the users a menu item which simply closes and reopens the database connection to see if this restores the ability to write, as well as read.

  I am unclear how to look at this as a blocking issue in that there is nothing in the application that relates to this term. Can you point me to somewhere where it is more fully explained

  
  

• Tom D

  Hi Mike,

  I've discussed this a few other folks in the group and it's been suggesting that you could be seeing a blocking issue. You should be able to use sp_who2 as part of your troubleshooting to help determine what is happening when the problem occurs. You should also check out the Activity Monitor in Management Studio Express as another place to examine server state.

  Let us know if this yeilds any results.

  Regards,

  Mike Wachal
  SQL Express team

  ----
  Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=307712&SiteID=1
  

  
  
  

• Andy!

  Hi Mike,

  I've not heard of this problem before. I don't think there are any easy answers for this one, it's just a matter of troubleshooting the problem as best you can. These things are never random, there is something that is causing it.

  There are a number of things you can do:

  • Check the SQL error log, there is often more information available there.
  • Log user actions and successful data write so you can determine what the user was doing right before the problem occurs.
  • Log the code location for each data write so you know where the last write is coming from before the problem.
  • Verify that you are not having network problems.
  • Check to make sure your error handling routines are not hiding the errors. (It would be pretty odd that the write is failing without some kind of message.)
  • Verify that you're using the right connection string everywhere you connect. If you have connections strings in more than one location in your program, you may have forgotten to change something and it just happens that people aren't using that section of code in the first 1 to 2 hours of use.

  Think about what is going on in the application when the problem occurs. Is there something specific that people do after 1 - 2 hours that is different Does this happen at the same time every day or on the same days Try to narrow down the conditions that cause the problem and you'll start zeroing in on the cause.

  Regards,

  Mike Wachal

  
  
  

• Tim Daniels

  Thanks for a prompt repsonse, however:

  1. There is nothing indiciative in the error log

  2. Once the application is in this condition it fails to implement all database writes, whilst processing all reads correctly

  3. All dB accesses are processed by two routines, one which wraps up an ExecuteNonQuery and the other a fillfields. Hence all possible error conditions are caught at the one place.

  4. The application establishes a db connection when it initialises and keeps that open until the application closes. All calls to the above routines use the same connection.

  5. The connection is created using the following:

  Dim strConn As String = _

  "Server=" & Server & ";" & _

  "DataBase=admin2005;" & _

  "Integrated Security=SSPI"

  oSqlConn = New SqlConnection(strConn)

  6. The application is run on about 6 machines. To date (about 6 months) the problem has only manifested on the two which are laptops. All are connected to the server through a wired local network.

  7. I have wondered if there is a possible timeout issue, but am unable to find any configurable value that might be responsible, bearing in mind this is Sql Server Express.

  
  

• maheshchinni

  Hi Mike,

  Connections themselves are not read only or read/write as far as I know, the capability of a connection in that regard is specified by the Login and User that is being supplied as part of the connection string. If the user has write permissions, then so does the connection.

  To be clear, Blocking <> Read Only. Blocking is a state the server gets in where the lock from one process prevents another process from performing a task. For example, if I have a row locked for three hours, no one else can write to that row regardless of their permissions. This is a simplistic example. You issue may or may not be blocking. You need to do some troubleshooting to figure that out.

  To you're point about maintaining an open connection, I beleive that this is not the recomended practice. Open connections are an expensive and limited, depending on your application, resource. You might be interested in the Patterns & Practices .NET Data Architecture Guide that discusses data access best practices. Specific to connection management, the guide states this:

  Managing Database Connections

  Database connections represent a critical, expensive, and limited resource, particularly in a multitier Web application. It is imperative that you manage your connections correctly because your approach can significantly affect the overall scalability of your application. Also, think carefully about where to store connection strings. You need a configurable and secure location.

  When managing database connections and connection strings, you should strive to:

  • Help realize application scalability by multiplexing a pool of database connections across multiple clients.
  • Adopt a configurable and high performance connection pooling strategy.
  • Use Windows authentication when accessing SQL Server.
  • Avoid impersonation in the middle tier.
  • Store connection strings securely.
  • Open database connections late and close them early.

  Taking the approach of opening connections when you need them and closing them quickly may impact the problem you are seeing. It also may not, it's hard to say without knowing why the problem occurs.

  Regards,

  Mike

  
  
  

• Bogdan M.

  I note that a possible cause relates to rertieval of all rows from a query.

  All of the retrieval operations in this application use the following routine:

  Function THfillds(ByVal ds As DataSet, ByVal SQLQ As String) As Integer

  If oSqlConn.State And System.Data.ConnectionState.Open = 0 Then

  MsgBox("Connection broken")

  Exit Function

  End If

  Dim NumRows As Integer

  Try

  Dim da As New SqlDataAdapter

  da.SelectCommand = New SqlCommand("USE admin2005;" & SQLQ, oSqlConn)

  NumRows = da.Fill(ds, "xxx")

  Catch expSql As SqlException

  MessageBox.Show(expSql.ToString, "xxx", _

  MessageBoxButtons.OK, MessageBoxIcon.Error)

  MsgBox("SQLQ: " & SQLQ)

  Return False

  End Try

  Return NumRows

  End Function

  The Datset which is retrieved is passed back to the calling routine to use. Does this count as 'retrieving all of the rows from the query'

  As stated earlier, the oSqlConn is created at the start of the application and kept open throughout. This is in the belief that there is a non-trivial overhead in connecting for each operation, but am I wrong in that assumption

  
  

• hosrow

  Sure but in my experience solving a problem by changing things so that 'it goes away like magic' end up with the problem coming back and hitting you when and where you least want it to!
  

• Anon56789

  This seems like one of those times where it will be good enough that the problem goes away. Let's hope it does.

  I'm sure you could work out the actual cause, but as I mentioned, it would likely take a bunch of troubleshooting and a fair amount of time if you're unable to reproduce the problem at will. As I said, there is not obvious answer to why this is happening, so it would have to be dug out through time and persistence.

  As developers, some times we just have to move on without understanding why the problem happened or why it went away. (The stories I could tell you about my cell phone "magically" fixing itself!)

  Mike

  
  
  

• Jack-Jack

  Thanks - I had wondered if that was the case and I guess it is that feature which means that re-opening the connection seems to be a fast operation.

  However, that means I am still no wiser about what was actually going wrong with our application in the first place! Or why closing and re-opening should make a difference. As yet there has been insufficient time to tell whether it is now any better or not. The next few days will give some idea.

  
  

• jinxed

  I'll need to study that in some depth but can you see what circumstances might lead to the connection being set to be read-only (I can't even see a way of defining a connection to be such, even if I wanted it!)
  

• lavan

  I guess I am still a little confused about what is actually going on!

  I have changed the sql access routines so that the connection is opened and closed for each operation (read or write). This appears to have had only minor impact on performance. (Whether or not it removes the original problem only time will tell)

  However, I have also, as recommended, started looking at the Activity Monitor. What puzzles me is that the application appears in the activity list under ProcessInfo immediately after the first Open. But, it does not disappear from the list after the Close, only when the application is terminated. Why

  
  
• sql server express loses ability to write to database