issue with reattach database - SQL 2000 Enterprise Manager
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
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
Or you can restrict file growth for example to 20 MB.
-FA
-FA
ASKER
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.
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.
ASKER
also i have tried the following command in my
exec sp_attach_db mydatabase,
'D:\MSQL\Data\mydatabase.M
'L:\SQL Logs\mydatabase_log.LDF'
same error - physical name of the mydatabase_log file is incorrect.
again we have plenty fo diskspace
exec sp_attach_db mydatabase,
'D:\MSQL\Data\mydatabase.M
'L:\SQL Logs\mydatabase_log.LDF'
same error - physical name of the mydatabase_log file is incorrect.
again we have plenty fo diskspace
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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