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

[Glen Buell]

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.

 

[Jonathan Roberts]

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

 

[Juan T. Llibre]

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

 

[Glen Buell]

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.

Glen,

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

Jonathan


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

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!]

 

[Jonathan Roberts]

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

 

[Billy Biro]

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.