June 23, 2005 at 8:55 pm
Today for reasons unfathomly, one of the user database on the SQL Std 2000 (SP4) changed to 'loading' status. I could find nothing in the SQL Server logs.
I manually performed a sp_detach_db successfully.
However, when I now attempt to use either sp_attach_db or sp_attach_single_file_db I get the following error:
I even get the same error if I construct my own
create database GLEN
on ( filename = 'C:\client\mgw\mssql\xxGLEN.mdf' )
log on ( filename = 'C:\client\mgw\mssql\log\xxGLEN_Log.ldf' )
for ATTACH
-- start error --
Server: Msg 9004, Level 21, State 10, Line 1
An error occurred while processing the log for database 'GLEN'.
Connection Broken
-- end error --
One point of note, the original files where stored on different drives - D: for the MDF and L: for the LDF
If I use the sp_attach_single_file_db specifying only the existing MDF, I get the following error message:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'GLEN'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'L:\MSSQL\LOG\GLEN_log.ldf' may be incorrect.
Last night's backup failed to occur, so I would have to revert to a backup from the night before.
Has anyone encountered this before situation before?
Robert
June 24, 2005 at 2:35 am
The link below should help
June 24, 2005 at 5:32 am
how about renaming the "old" log-file and then performing the sp_detach_db ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 24, 2005 at 7:27 pm
Thanks for the replies.
Binnie - the KB article applies when restoring a database. This is not the case here.
Alzdba - I had renamed the old ldf file and then used the sp_attach_single_file_db, but this does not work. See error message mention in original posting.
I have already done the sp_detach_db, and cannot get the either attach to work.
Robert
August 15, 2005 at 3:35 pm
yes i encounter the same problem the solution is..
-- delete both .mdf and .ldf files
-- restore the database again with the backup file
-- than try detach database
EXECUTE sp_detach_db @dbname = N'xyz'
-- than try attach database through
sp_attach_single_file_db @dbname = N'xyz',
@physname = 'd:\xyz.mdf'
hope this will work..
-Santosh
August 16, 2005 at 7:02 am
DON'T delete the .mdf and .ldf files. Just rename the extension (i.e. mydb.mdf.old).
That way you still have them. Then do as suggested above, but when you detach the restored db, rename those files and restore the original .mdf and .ldf to their real name and try to attach them.
One thing....did someone restore the database when this happened? Usually when a db is 'loading' it means a restore is occuring. This can happen if the restore is done using WITH NORECOVERY. Then it's awaiting additional restore files.
Next time this occurs, first try:
RESTORE DATABASE db_name WITH RECOVERY.
-SQLBill
August 16, 2005 at 7:40 am
Good call, SQLBill.
My thoughts exactly.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply