Unable to attach the database

I had to detach the database to delete the transaction log as it took
the hole disk space.

No I am trying to attach database, however I am not able to do so.

The engine is asking me whether I want to continue and create new log
file. I agree to it, however then I am receiving an error:

Error 1813: Could not open new database 'db_name'. CREATE DATABASE
aborted. Device activation error. The physical file name 'E:\SQL
Databases\MSSQL\Data\db_name.ldf' may be incorrect.

Then after I have message than attaching database failed.

In event viewer I have:

17204 :
FCB::Open failed: Could not open device E:\SQL Databases\MSSQL\Data
\db_name.MDF for virtual device number (VDN) 0.

In the error log I have:
2008-01-23 10:28:59.00 spid52 Starting up database 'db_name'.

Do you have any ideas where the problem is?

I have never had this problem before, only now. And I didn't have this
problem with other databases for which I have deleted the log file.

Re: Unable to attach the database

(grzegorz.gazda@googlemail.com) writes:
> I had to detach the database to delete the transaction log as it took
> the hole disk space.

No, you did not have to. The correct action would have been to
backup the transaction and then shrink the log file. Or simply
switch to simple recovery.

BUT NEVER DELETE A LOG FILE! NEVER!

> No I am trying to attach database, however I am not able to do so.

Of course not. You deleted half of the database.

If you have a clean backup of the database, restore the backup.

If you don't have a backup of the database, you are in trouble. There
are possibilities to create a new log file, so that you can access
the database, but the database is likely to be in bad shape, with
corruption in internal structures as well as inconsistencies in
application data.

If this is a database that you cannot afford to lose, I recommend that
you open a case with Microsoft. The methods to access the orphan MDF
depends on the SQL Server version. In any case, the methods requires
good understanding of SQL Server and how to work from this situation.
Which, to be honest, I don't trust someone who deletes his log file to be.
Whence, I recommend you to open a case with Microsoft, so that you have
a support person who ca guide you through the process.

Also, for the future, you need to learn some basics about how the log
file work. Either you go for simple recovery, or you back up your log
file regularly. The reduces the likelyhood for a log file swamping the
disk to a minimum.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx