deleted my database log file, can't reattach

[Jose]

Hello,

A utility I was running caused our database's log file to run out of
hard drive.

So, I detached the database, deleted the log file, and tried to
reattach.

SQL server fails to reattach because it cant find the log file. (Why
it cant just create a new default one is beyond me...but...)

EXEC sp_attach_single_file_db @dbname = 'sgvault',
@physname = 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\sgvault.mdf''

Could not open new database 'sgvault'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program
Files\Microsoft SQL Server\MSSQL\data\sgvault_log.LDF' may be
incorrect.

Do you know how I can fix this dilemma? Thanks!

[Simon Hayes]

jo**********@yahoo.com (Jose) wrote in message news:<b9**************************@posting.google. com>...

Hello,

A utility I was running caused our database's log file to run out of
hard drive.

So, I detached the database, deleted the log file, and tried to
reattach.

SQL server fails to reattach because it cant find the log file. (Why
it cant just create a new default one is beyond me...but...)

EXEC sp_attach_single_file_db @dbname = 'sgvault',
@physname = 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\sgvault.mdf''

Could not open new database 'sgvault'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program
Files\Microsoft SQL Server\MSSQL\data\sgvault_log.LDF' may be
incorrect.

Do you know how I can fix this dilemma? Thanks!

Assuming that you detached the database with sp_detach_db, then this
should work. If not, and assuming that you have the correct path to
the .mdf in your command, as well as full permissions in that folder,
then you will probably have to restore from backup. You can then
shrink the log file as described here:

http://support.microsoft.com/default...&Product=sql2k

Simon

[Erland Sommarskog]

[posted and mailed]

Jose (jo**********@yahoo.com) writes:

A utility I was running caused our database's log file to run out of
hard drive.

So, I detached the database, deleted the log file, and tried to
reattach.
That was a very, very bad thing to do. Never, never delete log fils
(unless you really want to get rid of that database). And if you really
must do, first make sure that copied it somewhere else first.

I leave this is as a memento not only for you, but for anyone who might
see these thread on Google in the future.
SQL server fails to reattach because it cant find the log file. (Why
it cant just create a new default one is beyond me...but...)

EXEC sp_attach_single_file_db @dbname = 'sgvault',
@physname = 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\sgvault.mdf''

Could not open new database 'sgvault'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program
Files\Microsoft SQL Server\MSSQL\data\sgvault_log.LDF' may be
incorrect.
I tried this exercise (with a database I could afford to waste), and I
got this message too, but the next message said that it was creating
an LDF for me.

It may be that your database was not shut down cleanly, for instance
because you panicked and killed SQL Server before you detached.
Do you know how I can fix this dilemma? Thanks!

If you have a good backup, restore it. If not, well, you may be able
to get to that data - or you may not.

I know of a way to make the database accessible from SQL Server, in so
far that you can say "use db". But depending on what that application
was doing, your database may be more or less corrupt. This is because
you may get the database in the middle of a transaction, so that page
linkings may be bad. And if even DBCC comes out clean, your own data
may be inconsistent.

I am not going to post the steps bring the database back online, because
they are far too dangerous. I would strongly recommend you to open a
case with Microsoft Support. No that is not going to be cheap, but you
need to weigh that against of losing the data altogether.

If you absolutely want to fight this alone, I can send the instructions
by mail.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp