|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > attach single file
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
attach single file
|
|
| Oleg Mironov 2002-11-08, 12:23 pm |
| We detached a database using:
EXEC sp_detach_db @dbname = 'SFIP_Main'
We deleted the physical log file for that database.
Then tried to reattach using:
EXEC sp_attach_single_file_db @dbname = 'SFIP_Main',
@physname = 'W:\SFIP_Main\SFIP_Main_Data.MDF'
We get an error message
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'SFIP_Main'. CREATE DATABASE
is aborted.
Device activation error. The physical file
name 'w:\sfip_main\SFIP_Main_log.LDF' may be incorrect.
We have done this many, many times in the past and are
totally lost as to what to do. How to we get this
database reattached?
| |
| Richard Ding 2002-11-08, 1:23 pm |
| Have you added more log files recently? If yes, the following KB article may
help:
http://support.microsoft.com/defaul...;en-us;qQ271223
Richard
"Oleg Mironov" <omironov@biobeef.com> wrote in message
news:3bbf01c28751$b408ede0$35e
f2ecf@TKMSFTNGXA11...
> We detached a database using:
> EXEC sp_detach_db @dbname = 'SFIP_Main'
>
> We deleted the physical log file for that database.
>
> Then tried to reattach using:
>
> EXEC sp_attach_single_file_db @dbname = 'SFIP_Main',
> @physname = 'W:\SFIP_Main\SFIP_Main_Data.MDF'
>
> We get an error message
>
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'SFIP_Main'. CREATE DATABASE
> is aborted.
> Device activation error. The physical file
> name 'w:\sfip_main\SFIP_Main_log.LDF' may be incorrect.
>
> We have done this many, many times in the past and are
> totally lost as to what to do. How to we get this
> database reattached?
>
| |
| Greg Linwood 2002-11-08, 6:23 pm |
| Hi Oleg.
I'm guessing that your w:\ mapping might be visible from your desktop pc,
but not from the SQL Server computer? If you're using SQL 2000, try using
the SQL Enterprise Manager - right click on databases / All Tasks / Attach.
This presents the file system dialog from the context of the SQL Server
rather than from your PC. On the server, the file might be on C:\ for
example?
HTH
Cheers,
Greg Linwood
Search the Google archive before re-posting:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Oleg Mironov" <omironov@biobeef.com> wrote in message
news:3bbf01c28751$b408ede0$35e
f2ecf@TKMSFTNGXA11...
> We detached a database using:
> EXEC sp_detach_db @dbname = 'SFIP_Main'
>
> We deleted the physical log file for that database.
>
> Then tried to reattach using:
>
> EXEC sp_attach_single_file_db @dbname = 'SFIP_Main',
> @physname = 'W:\SFIP_Main\SFIP_Main_Data.MDF'
>
> We get an error message
>
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'SFIP_Main'. CREATE DATABASE
> is aborted.
> Device activation error. The physical file
> name 'w:\sfip_main\SFIP_Main_log.LDF' may be incorrect.
>
> We have done this many, many times in the past and are
> totally lost as to what to do. How to we get this
> database reattached?
>
| |
| Oleg M - via Bruce Hawkins 2002-11-10, 12:23 pm |
| Greg,
Thanks for responding. In the past I have done this ...
using a Client PC mapping and thus the attach failed.
This is not the case now. The drive was changed to W: on
the server and is actually W:.
I was doing some exporting of data that required quite a
bit of inefficient Update statements which caused the Log
to balloon in size. In the past I just detach the
database, delete the log file and then using
sp_attach_single_file - reattach the database - new log
created.
This time it would not reattach and gave the error message
in the original post.
>> Server: Msg 1813, Level 16, State 2, Line 1
>> Could not open new database 'SFIP_Main'. CREATE DATABASE
>> is aborted.
>> Device activation error. The physical file
>> name 'w:\sfip_main\SFIP_Main_log.LDF' may be incorrect.
I'm screwed because I do not have a backup of the changes
that were done during the past two days and it will be
difficult to recreate.
Any other suggestions?
Thanks
Bruce
>-----Original Message-----
>Hi Oleg.
>
>I'm guessing that your w:\ mapping might be visible from
your desktop pc,
>but not from the SQL Server computer? If you're using SQL
2000, try using
>the SQL Enterprise Manager - right click on databases /
All Tasks / Attach.
>This presents the file system dialog from the context of
the SQL Server
>rather than from your PC. On the server, the file might
be on C:\ for
>example?
>
>HTH
>
>Cheers,
>Greg Linwood
>Search the Google archive before re-posting:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Oleg Mironov" <omironov@biobeef.com> wrote in message
> news:3bbf01c28751$b408ede0$35e
f2ecf@TKMSFTNGXA11...
>> We detached a database using:
>> EXEC sp_detach_db @dbname = 'SFIP_Main'
>>
>> We deleted the physical log file for that database.
>>
>> Then tried to reattach using:
>>
>> EXEC sp_attach_single_file_db @dbname = 'SFIP_Main',
>> @physname = 'W:\SFIP_Main\SFIP_Main_Data.MDF'
>>
>> We get an error message
>>
>> Server: Msg 1813, Level 16, State 2, Line 1
>> Could not open new database 'SFIP_Main'. CREATE DATABASE
>> is aborted.
>> Device activation error. The physical file
>> name 'w:\sfip_main\SFIP_Main_log.LDF' may be incorrect.
>>
>> We have done this many, many times in the past and are
>> totally lost as to what to do. How to we get this
>> database reattached?
>>
>
>
>.
>
| |
| BruceHawkins c/o Oleg 2002-11-10, 12:23 pm |
| Richard,
We do not have multiple log files and so this would not
apply to this situation. I found that article myself by
search in the KnowledgeBase for the error message >Server:
Msg 1813, Level 16, State 2, Line 1.
Since I detached successfully I did not anticipate any
problems reattaching. I was just a 30 second time lag
between detaching, physically deleting the Log file and
then issuing the sp_single_attach_file command. Like I
said in the original post (actually Oleg) I have done this
many times before successfully.
Any other suggestions?
Bruce
>-----Original Message-----
>Have you added more log files recently? If yes, the
following KB article may
>help:
>
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;qQ271223
>
>
>
>Richard
>
>
>"Oleg Mironov" <omironov@biobeef.com> wrote in message
> news:3bbf01c28751$b408ede0$35e
f2ecf@TKMSFTNGXA11...
>> We detached a database using:
>> EXEC sp_detach_db @dbname = 'SFIP_Main'
>>
>> We deleted the physical log file for that database.
>>
>> Then tried to reattach using:
>>
>> EXEC sp_attach_single_file_db @dbname = 'SFIP_Main',
>> @physname = 'W:\SFIP_Main\SFIP_Main_Data.MDF'
>>
>> We get an error message
>>
>> Server: Msg 1813, Level 16, State 2, Line 1
>> Could not open new database 'SFIP_Main'. CREATE DATABASE
>> is aborted.
>> Device activation error. The physical file
>> name 'w:\sfip_main\SFIP_Main_log.LDF' may be incorrect.
>>
>> We have done this many, many times in the past and are
>> totally lost as to what to do. How to we get this
>> database reattached?
>>
>
>
>.
>
| |
| Andrew J. Kelly 2002-11-10, 2:23 pm |
| > Since I detached successfully I did not anticipate any
> problems reattaching. I was just a 30 second time lag
> between detaching, physically deleting the Log file and
> then issuing the sp_single_attach_file command. Like I
> said in the original post (actually Oleg) I have done this
> many times before successfully.
>
Just because you got away with it before doesn't mean you will in the future
as you have now found out. This is not a supported method of clearing the
log file. Every time I hear of people doing this it make the hairs on the
back of my neck stand up. Bottom line is don't do this if you care at all
about your data. There have been several posts from MS support here in
this or the .programming newsgroup detailing how to attempt to recover from
this situation since it happens often. I don't have a link but I am sure if
you search these 2 ng's and or google you will find what your after and
hopefully a way to recover.
--
Andrew J. Kelly, SQL Server MVP
TargitInteractive
"BruceHawkins c/o Oleg" <bhawkins@biobeef.com> wrote in message
news:31a501c288e2$54c79e70$3ae
f2ecf@TKMSFTNGXA09...
> Richard,
>
> We do not have multiple log files and so this would not
> apply to this situation. I found that article myself by
> search in the KnowledgeBase for the error message >Server:
> Msg 1813, Level 16, State 2, Line 1.
>
> Since I detached successfully I did not anticipate any
> problems reattaching. I was just a 30 second time lag
> between detaching, physically deleting the Log file and
> then issuing the sp_single_attach_file command. Like I
> said in the original post (actually Oleg) I have done this
> many times before successfully.
>
> Any other suggestions?
>
> Bruce
>
> >-----Original Message-----
> >Have you added more log files recently? If yes, the
> following KB article may
> >help:
> >
> >http://support.microsoft.com/default.aspx?scid=kb;en-
> us;qQ271223
> >
> >
> >
> >Richard
> >
> >
> >"Oleg Mironov" <omironov@biobeef.com> wrote in message
> > news:3bbf01c28751$b408ede0$35e
f2ecf@TKMSFTNGXA11...
> >> We detached a database using:
> >> EXEC sp_detach_db @dbname = 'SFIP_Main'
> >>
> >> We deleted the physical log file for that database.
> >>
> >> Then tried to reattach using:
> >>
> >> EXEC sp_attach_single_file_db @dbname = 'SFIP_Main',
> >> @physname = 'W:\SFIP_Main\SFIP_Main_Data.MDF'
> >>
> >> We get an error message
> >>
> >> Server: Msg 1813, Level 16, State 2, Line 1
> >> Could not open new database 'SFIP_Main'. CREATE DATABASE
> >> is aborted.
> >> Device activation error. The physical file
> >> name 'w:\sfip_main\SFIP_Main_log.LDF' may be incorrect.
> >>
> >> We have done this many, many times in the past and are
> >> totally lost as to what to do. How to we get this
> >> database reattached?
> >>
> >
> >
> >.
> >
| |
| Josh Goodwin 2002-11-11, 4:23 pm |
| I had this same thing happen to me earlier today (except the detach
failed because the server was out of disk space). I used a combo of
the instructions found here:
http://www.sqlmag.com/forums/messag...2&threadid=8047
and the script found here:
http://www.storageadmin.com/Files/0.../Listing_03.txt
to get things running again.
To everyone else: What is the preferred method of physically
shrinking the trx logs? I've always done it with the sp_detach_db and
sp_attach_single_file_db. I have not found any other way to
physically reduce the file size of the trx log.
Josh
"Oleg M - via Bruce Hawkins" <bhawkins@biobeef.com> wrote in message news:< b85d01c288e0$5cb0aed0$37ef2ecf
@TKMSFTNGXA13>...
> Greg,
>
> Thanks for responding. In the past I have done this ...
> using a Client PC mapping and thus the attach failed.
> This is not the case now. The drive was changed to W: on
> the server and is actually W:.
>
> I was doing some exporting of data that required quite a
> bit of inefficient Update statements which caused the Log
> to balloon in size. In the past I just detach the
> database, delete the log file and then using
> sp_attach_single_file - reattach the database - new log
> created.
>
> This time it would not reattach and gave the error message
> in the original post.
> >> Server: Msg 1813, Level 16, State 2, Line 1
> >> Could not open new database 'SFIP_Main'. CREATE DATABASE
> >> is aborted.
> >> Device activation error. The physical file
> >> name 'w:\sfip_main\SFIP_Main_log.LDF' may be incorrect.
>
> I'm screwed because I do not have a backup of the changes
> that were done during the past two days and it will be
> difficult to recreate.
>
> Any other suggestions?
>
> Thanks
> Bruce
>
> >-----Original Message-----
> >Hi Oleg.
> >
> >I'm guessing that your w:\ mapping might be visible from
> your desktop pc,
> >but not from the SQL Server computer? If you're using SQL
> 2000, try using
> >the SQL Enterprise Manager - right click on databases /
> All Tasks / Attach.
> >This presents the file system dialog from the context of
> the SQL Server
> >rather than from your PC. On the server, the file might
> be on C:\ for
> >example?
> >
> >HTH
> >
> >Cheers,
> >Greg Linwood
> >Search the Google archive before re-posting:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >"Oleg Mironov" <omironov@biobeef.com> wrote in message
> > news:3bbf01c28751$b408ede0$35e
f2ecf@TKMSFTNGXA11...
> >> We detached a database using:
> >> EXEC sp_detach_db @dbname = 'SFIP_Main'
> >>
> >> We deleted the physical log file for that database.
> >>
> >> Then tried to reattach using:
> >>
> >> EXEC sp_attach_single_file_db @dbname = 'SFIP_Main',
> >> @physname = 'W:\SFIP_Main\SFIP_Main_Data.MDF'
> >>
> >> We get an error message
> >>
> >> Server: Msg 1813, Level 16, State 2, Line 1
> >> Could not open new database 'SFIP_Main'. CREATE DATABASE
> >> is aborted.
> >> Device activation error. The physical file
> >> name 'w:\sfip_main\SFIP_Main_log.LDF' may be incorrect.
> >>
> >> We have done this many, many times in the past and are
> >> totally lost as to what to do. How to we get this
> >> database reattached?
> >>
> >
> >
> >.
> >
| |
| Dan Guzman 2002-11-11, 10:23 pm |
| One should never delete the log file unless instructed to do so by your
primary support provider. Be prepared to restore from backup if you
want to try it.
See http://support.microsoft.com/defaul...kb;en-us;272318 for
instructions on shrinking the transaction log.
--
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"Josh Goodwin" <jgoodwin@aimsystems.com> wrote in message
news:424c379d.0211111348.601424ca@posting.google.com...
> I had this same thing happen to me earlier today (except the detach
> failed because the server was out of disk space). I used a combo of
> the instructions found here:
>
> http://www.sqlmag.com/forums/messag...2&threadid=8047
>
> and the script found here:
>
> http://www.storageadmin.com/Files/0.../Listing_03.txt
>
> to get things running again.
>
> To everyone else: What is the preferred method of physically
> shrinking the trx logs? I've always done it with the sp_detach_db and
> sp_attach_single_file_db. I have not found any other way to
> physically reduce the file size of the trx log.
>
> Josh
>
> "Oleg M - via Bruce Hawkins" <bhawkins@biobeef.com> wrote in message
news:< b85d01c288e0$5cb0aed0$37ef2ecf
@TKMSFTNGXA13>...
> > Greg,
> >
> > Thanks for responding. In the past I have done this ...
> > using a Client PC mapping and thus the attach failed.
> > This is not the case now. The drive was changed to W: on
> > the server and is actually W:.
> >
> > I was doing some exporting of data that required quite a
> > bit of inefficient Update statements which caused the Log
> > to balloon in size. In the past I just detach the
> > database, delete the log file and then using
> > sp_attach_single_file - reattach the database - new log
> > created.
> >
> > This time it would not reattach and gave the error message
> > in the original post.
> > >> Server: Msg 1813, Level 16, State 2, Line 1
> > >> Could not open new database 'SFIP_Main'. CREATE DATABASE
> > >> is aborted.
> > >> Device activation error. The physical file
> > >> name 'w:\sfip_main\SFIP_Main_log.LDF' may be incorrect.
> >
> > I'm screwed because I do not have a backup of the changes
> > that were done during the past two days and it will be
> > difficult to recreate.
> >
> > Any other suggestions?
> >
> > Thanks
> > Bruce
> >
> > >-----Original Message-----
> > >Hi Oleg.
> > >
> > >I'm guessing that your w:\ mapping might be visible from
> > your desktop pc,
> > >but not from the SQL Server computer? If you're using SQL
> > 2000, try using
> > >the SQL Enterprise Manager - right click on databases /
> > All Tasks / Attach.
> > >This presents the file system dialog from the context of
> > the SQL Server
> > >rather than from your PC. On the server, the file might
> > be on C:\ for
> > >example?
> > >
> > >HTH
> > >
> > >Cheers,
> > >Greg Linwood
> > >Search the Google archive before re-posting:
> > >http://groups.google.com/groups?
> > oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >"Oleg Mironov" <omironov@biobeef.com> wrote in message
> > > news:3bbf01c28751$b408ede0$35e
f2ecf@TKMSFTNGXA11...
> > >> We detached a database using:
> > >> EXEC sp_detach_db @dbname = 'SFIP_Main'
> > >>
> > >> We deleted the physical log file for that database.
> > >>
> > >> Then tried to reattach using:
> > >>
> > >> EXEC sp_attach_single_file_db @dbname = 'SFIP_Main',
> > >> @physname = 'W:\SFIP_Main\SFIP_Main_Data.MDF'
> > >>
> > >> We get an error message
> > >>
> > >> Server: Msg 1813, Level 16, State 2, Line 1
> > >> Could not open new database 'SFIP_Main'. CREATE DATABASE
> > >> is aborted.
> > >> Device activation error. The physical file
> > >> name 'w:\sfip_main\SFIP_Main_log.LDF' may be incorrect.
> > >>
> > >> We have done this many, many times in the past and are
> > >> totally lost as to what to do. How to we get this
> > >> database reattached?
> > >>
> > >
> > >
> > >.
> > >
| |
| Dankov 2002-11-12, 1:23 am |
|
"Oleg Mironov" <omironov@biobeef.com> wrote in message
news:3bbf01c28751$b408ede0$35e
f2ecf@TKMSFTNGXA11...
> Then tried to reattach using:
> EXEC sp_attach_single_file_db @dbname = 'SFIP_Main',
> @physname = 'W:\SFIP_Main\SFIP_Main_Data.MDF'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'SFIP_Main'. CREATE DATABASE
> is aborted.
You can try to attach with the utility "MDF forced attacher"
http://forcedattach.nm.ru
Gleb
|
|
|
|
|