August 13, 2002 at 4:42 pm
Log file for a given database was lost - now the database appears as 'suspect' in Enterprise Mgr. (SQL 2K). How do I recover from this? Help please!
Geoff
August 13, 2002 at 5:35 pm
You can detach, then reattach using sp_attach_single_file_db which will create the log file for you. If you had multiple log files it's harder, I've got an article posted that has a work around for that case.
Andy
August 14, 2002 at 9:49 am
I tried it and rec'd the following results back:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'AS_2'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'E:\Program Files\Microsoft SQL Server\MSSQL\LOG\ActiveSales_Log.LDF' may be incorrect.
The referenced file 'ActiveSales_Log.LDF' is the log file that was lost - I don't know why it is referenced in the error message?? HELP! Thanks for the help thus far...
August 14, 2002 at 9:57 am
Here is the script I ran and the results:
Script:
EXEC sp_detach_db @dbname = 'ActiveSales'
EXEC sp_attach_single_file_db @dbname = 'AS_2',
@physname = 'e:\Program Files\Microsoft SQL Server\MSSQL\Data\ActiveSales_Data.MDF'
Results:
Server: Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
The database 'ActiveSales' does not exist. Use sp_helpdb to show available databases.
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'AS_2'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'E:\Program Files\Microsoft SQL Server\MSSQL\LOG\ActiveSales_Log.LDF' may be incorrect.
August 14, 2002 at 10:08 am
Stop the server and copy the .mdf file. Then try an attach, overwriting the existing database.
Steve Jones
August 14, 2002 at 10:41 am
I stopped SQL Server, renamed the file to 'ActiveSales.mdf'. Then I restarted the server. I dropped the old database called 'ActiveSales'. I still get the following error:
Script:
EXEC sp_attach_single_file_db @dbname = 'AS2',
@physname = 'e:\Program Files\Microsoft SQL Server\MSSQL\Data\ActiveSales.MDF'
Results:
Could not open new database 'AS2'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'E:\Program Files\Microsoft SQL Server\MSSQL\LOG\ActiveSales_Log.LDF' may be incorrect.
I also tried:
Script:
EXEC sp_attach_db @dbname = N'AS2',
@filename1 = N'e:\Program Files\Microsoft SQL Server\MSSQL\Data\activesales.mdf'
Results:
Could not open new database 'AS2'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'E:\Program Files\Microsoft SQL Server\MSSQL\LOG\ActiveSales_Log.LDF' may be incorrect.
August 14, 2002 at 10:41 am
I stopped SQL Server, renamed the file to 'ActiveSales.mdf'. Then I restarted the server. I dropped the old database called 'ActiveSales'. I still get the following error:
Script:
EXEC sp_attach_single_file_db @dbname = 'AS2',
@physname = 'e:\Program Files\Microsoft SQL Server\MSSQL\Data\ActiveSales.MDF'
Results:
Could not open new database 'AS2'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'E:\Program Files\Microsoft SQL Server\MSSQL\LOG\ActiveSales_Log.LDF' may be incorrect.
I also tried:
Script:
EXEC sp_attach_db @dbname = N'AS2',
@filename1 = N'e:\Program Files\Microsoft SQL Server\MSSQL\Data\activesales.mdf'
Results:
Could not open new database 'AS2'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'E:\Program Files\Microsoft SQL Server\MSSQL\LOG\ActiveSales_Log.LDF' may be incorrect.
August 14, 2002 at 10:48 am
Is the path for the log file in existence? It will not create it. Use the WITH MOVE option to move the log file to a valid path.
Steve Jones
August 14, 2002 at 11:27 am
Yes, the path is definitely correct. However, I think that the data file somehow refers to that log file (which is not recoverable) thus the error message.
August 14, 2002 at 11:50 am
August 15, 2002 at 2:55 am
Hi Collin7,
Do your database have more than one log file? sp_attach_single_file_db does not work for databases with multiple log files.
Johnny Lim
August 15, 2002 at 5:41 am
try this:
exec sp_attach_db 'activesales' ,'e:\Program Files\Microsoft SQL Server\MSSQL\Data\ActiveSales_Data.MDF'
I'm assuming that your database name is activesales and not as_2.
We use this procedure when want to resize our log files...sql server will create a log when one is not supply when you attach a database.
August 15, 2002 at 8:54 am
Hi,
You can use dbcc rebuild_log to build a new log, whenever you lost the logs for you DB.
Step:
1) Put your database in emergency mode, by update sysdatabases system tables column status=32768 (don't forget to save the value from status column before update it)
2) restart SQL Server, so the DB is in bypass mode
3) Run:
DBCC traceon(3604)
dbcc rebuild_log('dbname', 'new_log_path_and_filename')
4) Restart SQL Server in Single User Mode and Run dbcc checkdb
5) Reset status for DB using the value saved in the first step.
6) Restart the SQL Server in Multi-User Mode.
If all is ok, you can use the database.
Remember: dbcc is undocumented and is not supported by Microsoft.
August 15, 2002 at 9:10 am
Make sure the .mdf is where it is expected with the name that is expected. Then stop and restart the service.
The server will often recreate the log file on a startup if it does not exist.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply