Database Corruption issue with Sql Express localdb

[Whizzo]

There are even bigger issues using Sql Express SqlLocalDB. If you connect to the Sql Express database .mdf file using the AttachDbFilename= method, it locks the database up. The next time you try to access it with an SQL tool or the Sql Server Management Studio, it shows the database as (Pending Recovery). Had to drop the database and recreate it.

Happened twice. Recreated database, loaded sample tables. Opened database.mdf using attachfile method, ran a simple query against a 9 row table, closed recordset and the database, then closed the application. Tried to view table with SSMS, showed database as (Pending Recovery).

I guess I will have to go back to the Access 2007 database if I can solve the issue with not being able to run the compact process from within Visual Basic.

[smendoza]

You're only attaching the database one time correct? or are you using the attachDbFileName each time you run your program?

[Whizzo]

This error occurs the first and only time the file is touched. Currently, I have only one test function that touches the Sql Express .mdf file. The rest of the app still uses the Access 2007 database.

[Shaggy Hiker]

So, why are you using that method to attach the database? Essentially, if that is causing you trouble, then the best solution is to not use it. What problem are you trying to solve that you need that method for?

After all, if you are worried about database corruption, going back to Access is not exactly a solution. Database corruption in Access is more interesting than in SQL Server, since it is so nearly unpredictable, but it still happens.

[smendoza]

Are you attaching the database through your app or SSMS?

[Whizzo]

Attaching via the applications. This an application for distribution to unknown users. I need to create the database and load some tables with default values then distribute with the application. It is likely that none of the ultimate users would have SqlServer installed on their computers. That is the reason for wanting to use SqlLocalDB and attach via a filename.

Perhaps the issues is because I created the database with an SQL tool connected to SQLServer via a DSN name. Then connected to the resultant .mdf file using DBattachFileName via the application.

I will see if I can run localdb and create an instance on the same computer that has SQLServer Express running on it.

I know what you mean about Access. I hoped to switch to localdb to avoid the self-bloating, self-corrupting issues associated with Access.

[Shaggy Hiker]

Are you using SQL Server Express 2012? I see that one has a new option for local DBs. Someday I may have to move there. For now, I'm in an organization that is using 2008 R2, though, so not just yet.

[Whizzo]

Yes. I am using SQL Server Express 2012.

[formlesstree4]

May I ask why you're using:

AttachDbFilename=

I've been around the block and back a few times with SQL Server and I've never had a need to do this.

[Whizzo]

Because this will be part of a distributed app. The .mdf and .ldf will be included in the install pack. Users will not have SQLserver installed. I will do a silent install with SQLLocalDB.msi and SQLNCLI.msi. Many posts on various forums and some on MSDN say that is the way to go.

[Whizzo]

Problem Solved: After the silent install of SQLExpress localdb and SQLServer client tools, I create the instance with the following:
SqlLocalDB create RCMDS
SqlLocalDB start RCMDS

Then I create the database with the function below (requires - Imports System.Data.SqlClient). After that I create necessary tables and insert any required records.

  Private Function CreateDatabase() As Boolean
    ' Declare Variables
    Dim sqlString As String
    Dim myConn As SqlConnection = New SqlConnection("Server=(localdb)\RCMDS;database=master")

    sqlString = ""
    sqlString += "Create Database RCMDataStore ON PRIMARY" + vbCrLf
    sqlString += " ( NAME = RCMDataStore," + vbCrLf
    sqlString += "   FILENAME = 'C:\RecipeMinder\DataStore\RCMDSData.mdf'," + vbCrLf
    sqlString += "   SIZE = 20MB," + vbCrLf
    sqlString += "   MAXSIZE = 500MB," + vbCrLf
    sqlString += "   FILEGROWTH = 5MB )" + vbCrLf
    sqlString += "LOG ON" + vbCrLf
    sqlString += " ( NAME = RCMDSLog," + vbCrLf
    sqlString += "   FILENAME = 'C:\RecipeMinder\DataStore\RCMDSLog.ldf'," + vbCrLf
    sqlString += "   SIZE = 10MB," + vbCrLf
    sqlString += "   MAXSIZE = 500MB," + vbCrLf
    sqlString += "   FILEGROWTH = 5MB )" + vbCrLf
    sqlString += " COLLATE Latin1_General_CI_AI" + vbCrLf

    Dim myCommand As SqlCommand = New SqlCommand(sqlString, myConn)

    Try
      myConn.Open()
      myCommand.ExecuteNonQuery()
      MessageBox.Show("Database created successfully", _
                      "MyProgram", MessageBoxButtons.OK, _
                       MessageBoxIcon.Information)
    Catch ex As Exception
      MessageBox.Show(ex.ToString())
    Finally
      If (myConn.State = ConnectionState.Open) Then
        myConn.Close()
      End If
    End Try

    Return True

  End Function   '   END  CreateDatabase()

[Shaggy Hiker]

In other words: Problem solved in the most painful way imaginable.

This is an issue that I will be dealing with in less than a year, hopefully, but am not dealing with right now. I've used the kind of solution you showed there in a previous application, but would really prefer to have an easier way to do the same work in the future.

[Whizzo]

I guess I am "old school". Coding it that way might take more lines of code, but at least it works AND I have control. I tried at least 15 or more supposed "working" examples from various postings. Didn't have any more time to expend. It works - so I moved on to the next item on the To-Do list.

Been in this business for 30+ years supporting SQLServer, Oracle, DB2, and teradata and VERY seldom does the hoped for "easier" way work. Most often searching for the "easier" way turns into hours and hours of frustration - trying this and trying that until you finally "happen" across something that works but then it breaks something else. Hope you are fortunate and find the easier way.

[smendoza]

What about doing the silent install then restoring a backup of the blank database?

[techgnome]

Attaching via the applications. This an application for distribution to unknown users. I need to create the database and load some tables with default values then distribute with the application. It is likely that none of the ultimate users would have SqlServer installed on their computers. That is the reason for wanting to use SqlLocalDB and attach via a filename.

Perhaps the issues is because I created the database with an SQL tool connected to SQLServer via a DSN name. Then connected to the resultant .mdf file using DBattachFileName via the application.

I will see if I can run localdb and create an instance on the same computer that has SQLServer Express running on it.

I know what you mean about Access. I hoped to switch to localdb to avoid the self-bloating, self-corrupting issues associated with Access.

Yup. that's where you went wrong. With a LocalDBFile, you'll notice the lack of an ldf that goes with it during distribution... it'll be generated later when it's attached. What you had though was a full db, including the ldf, which the mdf was aware of... when you told it to attach the mdf, it went looking for the ldf... and that's what hung it up. Best I've found is to create it as a localdbfile from VS right from the start.

May I ask why you're using:

AttachDbFilename=

I've been around the block and back a few times with SQL Server and I've never had a need to do this.

It's a newish option... a way to distribute SQL databases in a fairly painless way... w/o having to also distribute scripts or stuff like that. Of all the times I've seen it used... this is the first one I've heard of failing.

Because this will be part of a distributed app. The .mdf and .ldf will be included in the install pack. Users will not have SQLserver installed. I will do a silent install with SQLLocalDB.msi and SQLNCLI.msi. Many posts on various forums and some on MSDN say that is the way to go.

Generally speaking it is... as I jsut mentioned about... this is the first I've heard about it failing like this... but I think it's because of how it began life originally...

What about doing the silent install then restoring a backup of the blank database?

Oddly, that's even more work... you still have to first create the database, then restore to it.


Probably too late to mention this, but given the limited visiblity the database needs to be... you could have gone with SQL Server Company Edition (SQLCE) ... No need to isntall SQL Server... just the SQLCE assemblies (which isn't much) and then the sdf (the database file).

-tg

[szlamany]

Isn't AttachDBFilename all about working with a DB in single user mode?? Basically it's a USER INSTANCE of the DB.

Why would you expect to be able to do that and also work the DB through SSMS?

http://msdn.microsoft.com/en-us/libr...v=vs.110).aspx

The OP attacks the feature for what it's not designed to do - what's up with that?

[Shaggy Hiker]

Probably too late to mention this, but given the limited visiblity the database needs to be... you could have gone with SQL Server Company Edition (SQLCE) ...

-tg

Company???

I have always heard that as Compact.

[techgnome]

Maybe that's why I've had problems with it before. :P

-tg