Problem with "locked" SQL Server Express database files - Please help!

Discussion in 'ASP .Net' started by Glen Buell, Feb 4, 2007.

  1. Glen Buell

    Glen Buell Guest

    Hi all,

    I have a major problem with my ASP.NET website and it's SQL Server
    2005 Express database, and I'm wondering if anyone could help me out
    with it.

    This site is on a webhost (WebHost4Life) and was running fine and
    dandy, until I decided I needed to add some additional stored
    procedures to the database.

    I made these changes locally, and uploaded the changed files, which
    included the .MDF file for the database, overwriting the files that
    were on my host. I did not upload the .LDF file (and WebHost4Life say
    not to, since you can get errors like "database already exists for
    this user), and now my website is broken.

    I'm getting an error that my primary database file does not match my
    log file. I have attempted to delete both the .MDF and .LDF files,
    hoping to reload, but they're locked, and cannot be deleted or
    renamed.

    I believe the "lock" on these files is due them being currently
    "attached" to the SQLServer Express instance running on my webhost.
    I'm led to believe this, since I've tried all kinds of things to get
    this to work, including uploading a "copy" of the .MDF file with a
    different filename, and attempting to attach a different file (in the
    AttachDBFilename section of the connection string) but with the same
    "name" attribute. This gave me a different error saying that it could
    not attach this new database since one with the same name was already
    attached.

    My connection string within my web.config file is shown below:
    <add name="MyDB" connectionString="data source=.\SQLEXPRESS;Integrated
    Security=True;AttachDBFilename=|DataDirectory|MyDB.mdf;User
    Instance=True;Initial Catalog=MyDB"
    providerName="System.Data.SqlClient" />

    As can be seen by my connection string, I'm using "User Instance=True"
    which I thought would create a separate user instance of
    SQLServer2005Express, but that this instance exists only for the
    lifetime of my .NET web application. Thus, I thought that shutting
    down my .NET application, using the "app_offline.htm" trick, that this
    would kill my .NET app. instance, which in turn would kill the
    SQLServer user instance, thus unlocking my .MDF and .LDF files.
    However, this appears not to be the case, as the files (even after
    leaving things alone for a few hours, in case of some kind of caching
    etc.) are still locked, and cannot be deleted.

    Does anyone know of a way for me to unlock these files? I'm thinking
    I may have to "detach" the old database somehow, but I can't connect
    to it in the first place through my web application, and
    unfortunately, since this site is on a remote webhost, I don't have
    full access to IIS or the SQLServer service/instances.

    Any and all help on this matter is greatly appreciated.

    Thanks in advance.


    Glen.
    Glen Buell, Feb 4, 2007
    #1
    1. Advertising

  2. Re: Problem with "locked" SQL Server Express database files - Pleasehelp!

    Glen Buell wrote:
    > Does anyone know of a way for me to unlock these files? I'm thinking
    > I may have to "detach" the old database somehow, but I can't connect
    > to it in the first place through my web application, and
    > unfortunately, since this site is on a remote webhost, I don't have
    > full access to IIS or the SQLServer service/instances.
    >
    > Any and all help on this matter is greatly appreciated.
    >
    > Thanks in advance.
    >
    >
    > Glen.
    >


    Glen,

    I think someone who can admin their SQL Server will have to detach the
    database for you.

    Jonathan
    Jonathan Roberts, Feb 4, 2007
    #2
    1. Advertising

  3. You can close the connection held by Visual Web Developer by right-clicking the
    database in Solution Explorer and selecting the Detach option, or by right-clicking
    the database in Server Explorer and selecting Close Connection.

    Visual Web Developer will automatically close any open
    database connections when you run or debug your Web application.

    Additionally, if you need to release any open connections to a SQL Server Express Edition
    database, you can unload your Web application by using Internet Information Services Manager
    (IIS Manager).

    You can also unload a Web application by adding an HTML file named App_offline.htm to the
    root directory of your Web application. To allow your Web application to start responding to
    Web requests again, simply remove the App_offline.htm file.

    You will need to release open connections to a SQL Server Express Edition
    database when you want to copy or move the database to a new location.

    Also, you might want to consider using the SQL Server Express Utility:

    http://www.microsoft.com/downloads/...28-173f-472e-a85c-27ed01cf6b02&DisplayLang=en

    SQL Server 2005 Management Studio Express also allows you to detach databases :
    http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/MgSQLExpwSSMSE.doc






    Juan T. Llibre, asp.net MVP
    asp.net faq : http://asp.net.do/faq/
    foros de asp.net, en español : http://asp.net.do/foros/
    ===================================

    "Glen Buell" <> wrote in message
    news:...
    > Hi all,
    >
    > I have a major problem with my ASP.NET website and it's SQL Server
    > 2005 Express database, and I'm wondering if anyone could help me out
    > with it.
    >
    > This site is on a webhost (WebHost4Life) and was running fine and
    > dandy, until I decided I needed to add some additional stored
    > procedures to the database.
    >
    > I made these changes locally, and uploaded the changed files, which
    > included the .MDF file for the database, overwriting the files that
    > were on my host. I did not upload the .LDF file (and WebHost4Life say
    > not to, since you can get errors like "database already exists for
    > this user), and now my website is broken.
    >
    > I'm getting an error that my primary database file does not match my
    > log file. I have attempted to delete both the .MDF and .LDF files,
    > hoping to reload, but they're locked, and cannot be deleted or
    > renamed.
    >
    > I believe the "lock" on these files is due them being currently
    > "attached" to the SQLServer Express instance running on my webhost.
    > I'm led to believe this, since I've tried all kinds of things to get
    > this to work, including uploading a "copy" of the .MDF file with a
    > different filename, and attempting to attach a different file (in the
    > AttachDBFilename section of the connection string) but with the same
    > "name" attribute. This gave me a different error saying that it could
    > not attach this new database since one with the same name was already
    > attached.
    >
    > My connection string within my web.config file is shown below:
    > <add name="MyDB" connectionString="data source=.\SQLEXPRESS;Integrated
    > Security=True;AttachDBFilename=|DataDirectory|MyDB.mdf;User
    > Instance=True;Initial Catalog=MyDB"
    > providerName="System.Data.SqlClient" />
    >
    > As can be seen by my connection string, I'm using "User Instance=True"
    > which I thought would create a separate user instance of
    > SQLServer2005Express, but that this instance exists only for the
    > lifetime of my .NET web application. Thus, I thought that shutting
    > down my .NET application, using the "app_offline.htm" trick, that this
    > would kill my .NET app. instance, which in turn would kill the
    > SQLServer user instance, thus unlocking my .MDF and .LDF files.
    > However, this appears not to be the case, as the files (even after
    > leaving things alone for a few hours, in case of some kind of caching
    > etc.) are still locked, and cannot be deleted.
    >
    > Does anyone know of a way for me to unlock these files? I'm thinking
    > I may have to "detach" the old database somehow, but I can't connect
    > to it in the first place through my web application, and
    > unfortunately, since this site is on a remote webhost, I don't have
    > full access to IIS or the SQLServer service/instances.
    >
    > Any and all help on this matter is greatly appreciated.
    >
    > Thanks in advance.
    >
    >
    > Glen.
    >
    Juan T. Llibre, Feb 4, 2007
    #3
  4. Glen Buell

    Glen Buell Guest

    Firstly, thank you Jonathan and Juan for replying to my post, and for
    the advice you have provided.

    Next, I'd like to say that this issue is now resolved, thanks to my
    webhost being very gracious and "unlocking" the files for me. I also
    realise that this is really entirely my own fault for stupidly
    uploading a new .MDF file (overwriting the old one) whilst leaving the
    old .LDF file in place.

    My question now is one of best-practise with regard to replacing a
    remote SQLServer 2005 Express database on a remote web host. I am not
    concerned with the data inside the database, since I use the database
    purely for statistic logging, and it's not the end of the world if a
    few records don't get written whilst I'm in the process of "upgrading"
    it.

    Am I right in thinking that if I use the "app_offline.htm" trick
    (which should shut-down any and all instances of my .NET web app and
    prevent new instances from being instantiated), then my existing .MDF
    AND .LDF files should (eventually) become unlocked, thereby allowing
    me to delete the existing files (both the MDF and LDF) and re-upload
    (via FTP) the new MDF file, before removing the "app_offline.htm"
    file, allowing my application to restart ?

    Basically, I'm looking for a way to "shut-down" my application
    temporarily, ensure that the database files are entirely unused by any
    process on the host machine (ie. IIS, SQLEXPRESS etc.) therefore
    alowing me to delete/rename/overwrite these database files with
    impunity before "upgrading" my dtabase and bringing my application
    back "online".

    I have a feeling that the "app_offline.htm" method is the way to
    achieve this (since that's kinda what it's there for), but I'd just
    like confirmation from anywone that may have used this methodology for
    the same sort of reasons as myself.


    Thanks again in advance.


    Regards,
    Glen.


    On Sun, 4 Feb 2007 17:19:46 -0400, "Jonathan" wrote:
    >
    >Glen,
    >
    >I think someone who can admin their SQL Server will have to detach the
    >database for you.
    >
    >Jonathan
    >


    On Sun, 4 Feb 2007 17:19:46 -0400, "Juan T. Llibre"
    <> wrote:

    >You can close the connection held by Visual Web Developer by right-clicking the
    >database in Solution Explorer and selecting the Detach option, or by right-clicking
    >the database in Server Explorer and selecting Close Connection.
    >
    >Visual Web Developer will automatically close any open
    >database connections when you run or debug your Web application.
    >
    >Additionally, if you need to release any open connections to a SQL Server Express Edition
    >database, you can unload your Web application by using Internet Information Services Manager
    >(IIS Manager).
    >
    >You can also unload a Web application by adding an HTML file named App_offline.htm to the
    >root directory of your Web application. To allow your Web application to start responding to
    >Web requests again, simply remove the App_offline.htm file.
    >
    >You will need to release open connections to a SQL Server Express Edition
    >database when you want to copy or move the database to a new location.
    >
    >Also, you might want to consider using the SQL Server Express Utility:
    >
    >http://www.microsoft.com/downloads/...28-173f-472e-a85c-27ed01cf6b02&DisplayLang=en
    >
    >SQL Server 2005 Management Studio Express also allows you to detach databases :
    >http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/MgSQLExpwSSMSE.doc
    >
    >
    >
    >
    >
    >
    >Juan T. Llibre, asp.net MVP
    >asp.net faq : http://asp.net.do/faq/
    >foros de asp.net, en español : http://asp.net.do/foros/
    >===================================
    >
    >"Glen Buell" <> wrote in message
    >news:...
    >> Hi all,
    >>
    >> I have a major problem with my ASP.NET website and it's SQL Server
    >> 2005 Express database, and I'm wondering if anyone could help me out
    >> with it.
    >> [SNIP!]
    Glen Buell, Feb 5, 2007
    #4
  5. Re: Problem with "locked" SQL Server Express database files - Pleasehelp!

    Glen Buell wrote:
    > Basically, I'm looking for a way to "shut-down" my application
    > temporarily, ensure that the database files are entirely unused by any
    > process on the host machine (ie. IIS, SQLEXPRESS etc.) therefore
    > alowing me to delete/rename/overwrite these database files with
    > impunity before "upgrading" my dtabase and bringing my application
    > back "online".
    >
    > I have a feeling that the "app_offline.htm" method is the way to
    > achieve this (since that's kinda what it's there for), but I'd just
    > like confirmation from anywone that may have used this methodology for
    > the same sort of reasons as myself.


    Glen,

    First, I should say that I am not a developer, but a DBA. I could be
    wrong but I would be very surprised if this will detach the database for
    you. You normally do this through an admin GUI of some sort. SQL 2005
    uses SSMS; 2000 used Enterprise Manager. ** Many web hosts provide
    neither but provide their own web-based control panel application. I
    would start with your ISP. If they offer SQL, they should know the
    answer - should be a common question.

    Sorry, I could not advise more definitively.

    Jonathan
    Jonathan Roberts, Feb 6, 2007
    #5
  6. Glen Buell

    Billy Biro Guest

    On Mon, 05 Feb 2007 19:53:47 -0600, Jonathan Roberts
    <> wrote:

    >Glen Buell wrote:
    >> Basically, I'm looking for a way to "shut-down" my application
    >> temporarily, ensure that the database files are entirely unused by any
    >> process on the host machine (ie. IIS, SQLEXPRESS etc.) therefore
    >> alowing me to delete/rename/overwrite these database files with
    >> impunity before "upgrading" my dtabase and bringing my application
    >> back "online".
    >>
    >> I have a feeling that the "app_offline.htm" method is the way to
    >> achieve this (since that's kinda what it's there for), but I'd just
    >> like confirmation from anywone that may have used this methodology for
    >> the same sort of reasons as myself.

    >
    >Glen,
    >
    >First, I should say that I am not a developer, but a DBA. I could be
    >wrong but I would be very surprised if this will detach the database for
    >you. You normally do this through an admin GUI of some sort. SQL 2005
    >uses SSMS; 2000 used Enterprise Manager. ** Many web hosts provide
    >neither but provide their own web-based control panel application. I
    >would start with your ISP. If they offer SQL, they should know the
    >answer - should be a common question.
    >
    >Sorry, I could not advise more definitively.
    >
    >Jonathan


    Hi Jonathan,

    Thanks again for replying to my post.

    I think you're exactly right about having to "manually" attach/detach
    database files, but I think this only applies to the various
    "full-blown" editions of SQL Server 2005 (ie. Standard, Enterprise
    etc.).

    I'm under the impression that SQL Server 2005 EXPRESS operates
    slightly differently. Certainly, the ATTACHING of a database file is
    done dynamically when using ASP.NET, as the connectionstring that is
    used to connect to a SQL2005Express database contains a section like
    this:
    AttachDBFilename=database.mdf

    This instructs the SQL Express instance to dynamically attach the file
    specified at run-time, without any intervention from a DBA/Admin at
    all.

    Another section of the connection string has syntax like this:
    User Instance = True

    This instructs SQL2005Express to create a new "instance" of the
    SQLExpress service, running under the user account from which it is
    created.

    The following MSDN link is quite helpful:
    http://msdn2.microsoft.com/en-us/library/bb264564.aspx

    In fact, I think I may have answered my own question here, since that
    link does say that "user instances" of SQLServer2005Express are
    automatically "shut-down" after the last connection using them is
    closed. I think it says that the default time for this is 60 minutes,
    but that it is configurable. I'm assuming that when the "user
    instance" is shut-down, the database that was previously attached to
    that instance will be automatically detached, thus removing any
    OS-level file locks.

    Thanks again for your help. Seems that between us all, we've stumbled
    on the answer.

    Regards,
    Glen.
    Billy Biro, Feb 6, 2007
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Michael
    Replies:
    2
    Views:
    9,032
    magscy
    Jul 19, 2009
  2. =?Utf-8?B?dmE=?=
    Replies:
    4
    Views:
    2,831
    =?Utf-8?B?dmE=?=
    Feb 22, 2006
  3. Keith
    Replies:
    1
    Views:
    2,311
    Keith
    Jul 3, 2006
  4. Nick
    Replies:
    5
    Views:
    406
  5. AAaron123
    Replies:
    6
    Views:
    2,485
    AAaron123
    Oct 28, 2008
Loading...

Share This Page