Corrupt Log-file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Corrupt Log-file

Author

Topic

xtsmats
Starting Member

6 Posts

Posted - 2003-09-30 : 11:55:20

I have problems with attaching a database.
The database was attached to a Server witch is now dead.
When I try to attached the database with this command:

CREATE DATABASE hubert
ON PRIMARY (FILENAME = 'c:\projekt\N2S_demo.mdf')
,
(NAME = apa,
FILENAME = 'c:\projekt\N2S_log.ldf')
FOR ATTACH

I get this error message:
Server: Msg 9003, Level 20, State 6, Line 1
The LSN (506:42:1) passed to log scan in database 'hubert' is invalid.

Connection Broken

I get the same error message when I use Enterprise Manager.
If I try to attach the database with out the log-file (from Enterprise Manger) I get this error message:

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'apa'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'H:\Program Files\Microsoft SQL Server\MSSQL\Data\N2S_log.ldf' may be incorrect.

Is there any other way to attach a database without the log-file?
Or is there any way to fix a corrupt log-file?

Regards
Mats

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-30 : 12:44:19

Try using sp_attach_single_file_db in Query Analyzer. Information about it is in SQL Server Books Online.

Don't you have a recent full backup that you can use? This file is probably corrupt and can not be used, so you would need to use the last full backup and then apply any differential backups or transaction logs to get it closer to the point of failure.

Tara

xtsmats
Starting Member

6 Posts

Posted - 2003-09-30 : 12:59:07

I get the same reply when I use sp_attach_single_file_db:

Server: Msg 9003, Level 20, State 6, Line 1
The LSN (506:42:1) passed to log scan in database 'apa' is invalid.

Connection Broken

Or when it can't find the log-file:

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'apa'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'H:\Program Files\Microsoft SQL Server\MSSQL\Data\N2S_log.ldf' may be incorrect.

And no there is no recent backup. So, I need to know if there is any way to fix this.

/Mats
/Mats

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-30 : 13:01:10

Did you delete the LDF file yet? sp_attach_single_file_db will create a new one for you, but you need to delete the old one first.

Tara

xtsmats
Starting Member

6 Posts

Posted - 2003-09-30 : 13:27:58

I renamed the log-file. But then it complains about that it can't find it and it it doesen't create a new one. Can the database-file also be corrupt when this doesn't work? Is there any way to check a file if it's not attached?

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'apa'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'H:\Program Files\Microsoft SQL Server\MSSQL\Data\N2S_log.ldf' may be incorrect.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-30 : 13:34:29

What is the command that you are using for sp_attach_single_file_db (after the file is renamed)? Is the path correct in the command?

Tara

xtsmats
Starting Member

6 Posts

Posted - 2003-09-30 : 13:45:06

Yes, it is the correct path. If it's incorrect you get another error message.

sp_attach_single_file_db @dbname = 'apa'
, @physname = 'c:\projekt\N2S_demo.mdf'

/Mats

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-30 : 13:47:55

From what I can tell, your MDF is corrupt and can not be used to attach the database. Without a previous MDF file or a database backup, there is no way to recover this database.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources