September 29, 2007 at 9:46 pm
dear forum,
firstly i have a backup, so can restore.
our L drive where the file sits filled up overnight and I got an alert this morning. So no room to shrink or anything like that.
So dettached the mydatabse from enterprise manger (no one is on the system by the way its a sunday here) deleted the log file and in the process of reattaching, i was going to accept the prompt that the log file recreates itself.
The error message I got was Error 1813: Could not oen database 'mydatabase' create database is aborted Devide Activate error the physical file name L:\SQL Logs\mydatabase_log.ldf may be incorrect.
normally the log file recreates itself. can anyone offer me any assistance please
also used the following command in
exec sp_attach_db mydatabase,
'D:\MSQL\Data\mydatabase.MDF'
'L:\SQL Logs\mydatabase_log.LDF'
thad did not work either, same result of physical file name L:SQL Logs\mydatabase_log.ldf may be incorrect.
appreciate any thoughts here, its becoming a little urgent as users want to test things.
also we have plenty of diskspace too, i thought that might be the cause, but its not.:D
regards,
September 30, 2007 at 7:41 am
have tried stop and restarting sql server and then re attach the database. still a problem.
i have now gone to restore, since it is test database not a problem
all is working
September 30, 2007 at 1:29 pm
Amanda Walshaw (9/29/2007)
dear forum,So dettached the mydatabse from enterprise manger (no one is on the system by the way its a sunday here) deleted the log file and in the process of reattaching, i was going to accept the prompt that the log file recreates itself.
Why would you delete a log file? Do you want a corrupt database? Deleting the log file could easily cause transactional inconsistencies or even suspect database.
See this blog post from a former member of the SQL Storage engine team, specifically the section on rebuilding your transaction log - Corruption - Last Resorts That People Try First
What would have worked better would have been to move the log file to another drive and then attach. You could then back the log up and shrink the log down to a reasonable size
I would highly recommend restore from your backup. You have no idea what state the database is in.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2007 at 7:51 pm
Well I 100% agree with Gail that why would you delete the log file??? This would certainly lead to serious implications including the database going to suspect status. Also if you don't have the recent transaction log backup then you may lose out on the transactions done since the last full bacup or the differntial backup.
Secondly coming to your point that log file is automatically created. For that you don't need to give the sp_attach_db command rather you should use sp_attach_single_file_db to attach the mdf file and the log fle will automatically be created.
From BOL
sp_attach_single_file_db works only on databases that have a single log file. When sp_attach_single_file_db attaches the database to the server, it builds a new log file. If the database is read-only, the log file is built in its previous location.
September 30, 2007 at 8:52 pm
Never delete a log file. Even if the drive is full there are options. You can backup with truncate_only, fun a shrink only on the log, and more.
October 1, 2007 at 12:18 am
I'd go with GilaMonster's advice.
Keep in mind that when you are going to restore, you can actualy move the log-file to a new location during the restore itself. Nomore hassle afterward. :w00t:
restore yourdb from yourdevice
with MOVE N'yourdb_Data' TO N'yourpath\yourdb_Data.mdf'
, MOVE N'yourdb_Log' TO N'yourNEWpath\yourdb_log.ldf'
-- , replace --ONLY IF NEEDED !!
, recovery -- only for the last restore statement
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply