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-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.|||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 it using
> sp_attach_single_file_db.
>|||Yes,
I tried with both sp_attach_db and sp_single_file_attach_db, nothing works.
Jens Süßmeyer 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.
>
>|||> Yes, I have only one db-file.
Impossible, at least if that would be the answer to my question: "What database 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 will not leave you with an
inconsistent database!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hobbelito" <"hobbelito[NOSPAM]"@.yahoo.com> wrote in message
news:elERhMXSFHA.3424@.TK2MSFTNGP10.phx.gbl...
> 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 it using
>> sp_attach_single_file_db.|||I think this might help:
http://www.windowsitpro.com/SQLServer/Article/ArticleID/26044/26044.html
HTH, JEns SUessmeyer.
"Hobbelito" <"hobbelito[NOSPAM]"@.yahoo.com> schrieb im Newsbeitrag
news:%23iszFPXSFHA.3424@.TK2MSFTNGP10.phx.gbl...
> Yes,
> I tried with both sp_attach_db and sp_single_file_attach_db, nothing
> works.
>
> Jens Süßmeyer 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.
>>|||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:
>>Yes, I have only one db-file.
>
> Impossible, at least if that would be the answer to my question: "What database 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 will not leave you with an
> inconsistent database!|||I wonder how many of those who has used this really has been going through the data looking for
inconsistencies in the data? ;-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jens Süßmeyer" <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/SQLServer/Article/ArticleID/26044/26044.html
> HTH, JEns SUessmeyer.
>
> "Hobbelito" <"hobbelito[NOSPAM]"@.yahoo.com> schrieb im Newsbeitrag
> news:%23iszFPXSFHA.3424@.TK2MSFTNGP10.phx.gbl...
>> Yes,
>> I tried with both sp_attach_db and sp_single_file_attach_db, nothing works.
>>
>> Jens Süßmeyer 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.
>>
>|||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 Süßmeyer wrote:
> I think this might help:
> http://www.windowsitpro.com/SQLServer/Article/ArticleID/26044/26044.html
> HTH, JEns SUessmeyer.
>
>|||In your situation, I'd just restore the most recent backup. Even if you lose a few hours work, it is
probably better then a possibly inconsistent database!
As for opening a case with MS, you find some information here:
http://www.microsoft.com/services/microsoftservices/supp.mspx
Note that talking to Ms will not change the fact that SQL Server probably need to perform recovery
for the database. Probably (possibly) MS will assist you in getting the database running, but it
will probably still be inconsistent.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hobbelito" <"hobbelito[NOSPAM]"@.yahoo.com> wrote in message
news:OGv4LdXSFHA.3788@.tk2msftngp13.phx.gbl...
> 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:
>>Yes, I have only one db-file.
>>
>> Impossible, at least if that would be the answer to my question: "What database 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 will not leave you
>> with an inconsistent database!|||Hi,
I got this tip, about a utility that forces a single file database
attachment, has anyone heard of it, and maybe tried it?
http://www.examnotes.net/archive79-2002-10-76299.html
The problem is that the website that used to host the utility is
oudated. Wondering if anyone has the utility still and maybe share it.
rgds Hobil
Tibor Karaszi wrote:
> In your situation, I'd just restore the most recent backup. Even if you lose a few hours work, it is
> probably better then a possibly inconsistent database!
> As for opening a case with MS, you find some information here:
> http://www.microsoft.com/services/microsoftservices/supp.mspx
> Note that talking to Ms will not change the fact that SQL Server probably need to perform recovery
> for the database. Probably (possibly) MS will assist you in getting the database running, but it
> will probably still be inconsistent.
>|||Hi guys,
Again, thanks very much for all your input, it very enlightning
experience hehe...
The solution was brought to me via a developer forum, pellesoft.se
(swedish mvp). He gave me this link, that I would like to contribute
to you all other sql-server developers in distress hehe
http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html
Rgds
.hobil
SparvHok 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.
>|||According to this articl
http://www.sqlservercentral.com/columnists/ckempster/deattachandreattachdatabases.asp
at the bottom of this article it says that this error Server: Msg 1813,
Level 16, State 2, Line 1
occurs when you have multiple log files.
"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.
>|||According to this articl
http://www.sqlservercentral.com/columnists/ckempster/deattachandreattachdatabases.asp
at the bottom of this article it says that this error Server: Msg 1813,
Level 16, State 2, Line 1
occurs when you have multiple log files.
"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.
>