Problems after detaching database

Hi there,
I have an urgent problem, after detaching a database, and removing a
log-file (it went crazy size-wise, the db is about 80 MB, and the
log-file was about 1.5GB) I now cant seem to reattach the DB, the
messege I get when trying to do it, either in Enterprise manager or
Query Analyzer is:
----
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'mindb'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'd:\Program
Files\Microsoft SQL Server\MSSQL\Data\mindb_Log.LDF' may be incorrect.
----
Help very much appritiated.
+=======================================
===============================+
| Hobbelito
+=======================================
===============================+
| e-mail: hobbelito[NOSPAM]yahoo.com |
+=======================================
===============================+
To send me an email, replace the [NOSPAM] tag with @. sign.Assuming the database only had one mdf and one ldf you can try to attach it
using
sp_attach_single_file_db.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SparvHok" <"sparvhok[NOSPAM]"@.hotmail.com> wrote in message
news:%23XXZI$WSFHA.2680@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I have an urgent problem, after detaching a database, and removing a log-f
ile (it went crazy
> size-wise, the db is about 80 MB, and the log-file was about 1.5GB) I now
cant seem to reattach
> the DB, the messege I get when trying to do it, either in Enterprise manag
er or Query Analyzer is:
> ----
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'mindb'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'd:\Program Files\Microsof
t SQL
> Server\MSSQL\Data\mindb_Log.LDF' may be incorrect.
> ----
> Help very much appritiated.
>
> --
> +=======================================
===============================+
> | Hobbelito
> +=======================================
===============================+
> | e-mail: hobbelito[NOSPAM]yahoo.com
|
> +=======================================
===============================+
> To send me an email, replace the [NOSPAM] tag with @. sign.|||Make sure that te (Server) path points to the right file, because the file
you mentioned in not found by SQL Server. DId you try to use the attach
syntax in QA, defning both the data and the logfiles ?
Jens Suessmeyer.
"SparvHok" <"sparvhok[NOSPAM]"@.hotmail.com> schrieb im Newsbeitrag
news:%23XXZI$WSFHA.2680@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I have an urgent problem, after detaching a database, and removing a
> log-file (it went crazy size-wise, the db is about 80 MB, and the log-file
> was about 1.5GB) I now cant seem to reattach the DB, the messege I get
> when trying to do it, either in Enterprise manager or Query Analyzer is:
> ----
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'mindb'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'd:\Program
> Files\Microsoft SQL Server\MSSQL\Data\mindb_Log.LDF' may be incorrect.
> ----
> Help very much appritiated.
>
> --
> +=======================================
===============================+
> | Hobbelito
> +=======================================
===============================+
> | e-mail: hobbelito[NOSPAM]yahoo.com
|
> +=======================================
===============================+
> To send me an email, replace the [NOSPAM] tag with @. sign.|||Hi
You need to Log to re-attach, else sp_attach_single_file_db might work if
you don't have it.
If you have a log that grows that big, it means that you are running in Full
Recovery mode and are not dumping the log. Have a look at Backuping up
databases in BOL.
Regards
Mike
"SparvHok" <"sparvhok[NOSPAM]" wrote:

> Hi there,
> I have an urgent problem, after detaching a database, and removing a
> log-file (it went crazy size-wise, the db is about 80 MB, and the
> log-file was about 1.5GB) I now cant seem to reattach the DB, the
> messege I get when trying to do it, either in Enterprise manager or
> Query Analyzer is:
> ----
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'mindb'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'd:\Program
> Files\Microsoft SQL Server\MSSQL\Data\mindb_Log.LDF' may be incorrect.
> ----
> Help very much appritiated.
>
> --
> +=======================================
===============================+
> | Hobbelito
> +=======================================
===============================+
> | e-mail: hobbelito[NOSPAM]yahoo.com
|
> +=======================================
===============================+
> To send me an email, replace the [NOSPAM] tag with @. sign.
>|||Yes, I have only one db-file.
Either way, it doesnt help me, since something must have gone bad, when
the detaching took place, I dont know what!?
Isnt there anyway of making the attachment of the db-file reset the
log-file info, altogether!?
Ive also tried to create a new file (text-file) with the same name as
the old log-file, didnt work.
I also tried create a whole new database (with the same name as the old)
and use the newly created log-file with the old database file, didnt
work either.
Anymore ideas, please share them
Tibor Karaszi wrote:
> Assuming the database only had one mdf and one ldf you can try to attach i
t using
> sp_attach_single_file_db.
>|||Yes,
I tried with both sp_attach_db and sp_single_file_attach_db, nothing works.
Jens Smeyer wrote:
> Make sure that te (Server) path points to the right file, because the file
> you mentioned in not found by SQL Server. DId you try to use the attach
> syntax in QA, defning both the data and the logfiles ?
> Jens Suessmeyer.
>
>|||I think this might help:
http://www.windowsitpro.com/SQLServ...6044/26044.html
HTH, JEns SUessmeyer.
"Hobbelito" <"hobbelito[NOSPAM]"@.yahoo.com> schrieb im Newsbeitrag
news:%23iszFPXSFHA.3424@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Yes,
> I tried with both sp_attach_db and sp_single_file_attach_db, nothing
> works.
>
> Jens Smeyer wrote:|||hi there Tibor,
Just as you say, I myself have thought of that, something went wrong
when detaching the database.
And yes it is a standard/default (so to speak), two-file database, one
data and one log file, thats all.
Open a support case at MS, never done that before, how does one go about
it, and how long time before help is provided?
A followup question, is it at all possible to retrieve information from
my gone-bad database file!? Import, backup, restore, something like
that? Anybody know of any tools providing that?
Thanx for all your input guys
Tibor Karaszi wrote:
>
> Impossible, at least if that would be the answer to my question: "What dat
abase files *did* the
> database have? :-). Sorry for nit-picking, but a database has at least two
files. One for data and
> one for log. The reason I'm picky on this is that sp_attach_single_file_db
is only documented to
> create a new log file for you if you only had one mdf and one ldf file.
> In any case, I would open a support case with MS on this. Seems you either
had more than two files
> for the database or that detach went wrong. So SQL Server need to perform
recovery work for which
> the transaction log is needed. But the log isn't there, and SQL Server wil
l not leave you with an
> inconsistent database!|||I wonder how many of those who has used this really has been going through t
he data looking for
inconsistencies in the data? ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message
news:Ox7hIXXSFHA.996@.TK2MSFTNGP09.phx.gbl...
>I think this might help:
> http://www.windowsitpro.com/SQLServ...6044/26044.html
> HTH, JEns SUessmeyer.
>
> "Hobbelito" <"hobbelito[NOSPAM]"@.yahoo.com> schrieb im Newsbeitrag
> news:%23iszFPXSFHA.3424@.TK2MSFTNGP10.phx.gbl...
>|||Actually, it sounds just right for my problem, but seems to require the
existence of the database still online for some reason... I dont have it
anymore, becase of the detaching process. I seem to succeeded in
detaching the database, but somewhere in the processes something else
went wrong, therefore I cant rebuild logs for a database that isnt
there, or have I misunderstood the tip?
rgds hobil
Jens Smeyer wrote:
> I think this might help:
> http://www.windowsitpro.com/SQLServ...6044/26044.html
> HTH, JEns SUessmeyer.
>
>