?
Solved

issue with reattach database - SQL 2000 Enterprise Manager

Posted on 2007-09-29
7
Medium Priority
?
611 Views
Last Modified: 2012-05-05
We had a log file completely full, it was too big to use shrink command.

What I did before was detach the database, delete the log file and reattach the database and a new log file is recreated.
on this occasion i am getting the error, when i was prompted to create a new log file i got the error message
Error 1813: Could not open database 'MyDatabase' Create database is aborted.
Device activation error. The physical file name L\SQL Logs\\mydatabase_log.ldf  may be incorrect.

i am using sql 2000
0
Comment
Question by:Amanda Walshaw
  • 3
  • 3
7 Comments
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 19986182
Hi,
You can delete the log file from "Transaction Log" tab in dialog that open when you click Properties in context menu of DB.
Then you can create new log file.

-FA
0
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 19986184
Or you can restrict file growth for example to 20 MB.

-FA
0
 

Author Comment

by:Amanda Walshaw
ID: 19986235
not understanding you on this one... the database is no longer appearing the EM as i have de-attached the database 'mydatabse'  i have deleted the log file from the L drive, after doing so we have plenty of disk space to recreate.  i could not truncate the log file as it was too big.

i can no longer recreate the log file when i go to reattach database. on the EM screen to attach there is a cross in on the LDF file which is normal, normally it will ask the question that the log file is no longer there, i have also made sure the path is correct, then when you click option to recreate the log file, the database successfully reattaches.  and a new log file is recreated.

just a note we have also moved our servers onto SAN environment, and I don't think the developer allowed enough diskspace when he created the San environment that is why this log file fills up so quickly.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:Amanda Walshaw
ID: 19986329
also i have tried the following command in my

exec sp_attach_db mydatabase,
'D:\MSQL\Data\mydatabase.MDF'
'L:\SQL Logs\mydatabase_log.LDF'

same error - physical name of the mydatabase_log file is incorrect.
again we have plenty fo diskspace

0
 
LVL 14

Assisted Solution

by:Farzad Akbarnejad
Farzad Akbarnejad earned 300 total points
ID: 19986447
try this:
exec sp_attach_db mydatabase,
'D:\MSQL\Data\mydatabase.MDF' 'L:\SQL Logs\mydatabase.LDF'

omit _log from LDF file.

Also you can delete log file when your database is attached and is connected.
but I think that you had a mistype in log file name. Omit _log from LDF file maybe solve your problem.

-FA


0
 
LVL 14

Accepted Solution

by:
Jai S earned 1200 total points
ID: 19986820
i think that should not be a problem...try restarting your SQL Server once...
deleeting a log file should not have any kind of problems...
while reattaching the database ...do not specify any filename for your log file...and try to reattach again...it should work...
0
 

Author Comment

by:Amanda Walshaw
ID: 19986971
hi jaiganesh, i did try all that.

stopping the database, restarting then tried through em to attach again, and not naming the log file.  i have done this heaps of time before and it works.

however I do have a back up and i have now restored.  This is one of our test databases and why the log file has grown so much is becuase of the indexing, to complicate matters, we have moved our servers to SAN environment, and i don't think the engineer alllocated enough disk space.

thanks so much though for your input.  
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question