Damaged log file

[Former Member]

Hello

I had a motherboard and harddisk failure on my development server. After much effort I have been able to get the specialists to recover most of the files on the disk, especially the SQL datafiles seem to be intact. However there is a problem with the logfile. Is there anyway of using an older version of the logfile, or ignoring the logfile altogether (as in starting with a fresh empty log from a new install). The server is only used for development, so there were no transactions taking place at the time of the crash. Problem is my latest backup is a bit old and there are some important developments I need to rescue.

[clas_hortien · Accepted solution]

Hello,

you should open a OSS message for this in the component BC-DB-MSS. Mention my name within the message and open a WTS connection to the affected system.

Even if this is a development system you must know, that you will get locigal inconsistencies within your database when you have to rebuild the log. You cannot recover from this logical inconsistencies as nobody knows where to find them. Furthermore might the datafiles also corrupted, what you can't see currently (as you cannot recover). But fixing the log will not necessarily fix you problem, maybe it will only show you a much bigger problem (if the datafiles are damaged).

Best regards

Clas

[Former Member]

Hi,

I had the same problem. You can rebuild log file from MDF and NDF database files.

I resolve this problem like this:

- copy corrupted database files (*.mdf and *.ndf files)

- in enterprise manager, create new empty database with the sam SID name.

- databases files will be located in the same directories where old corrupted database.

- new database with the same SID name created.

- stop sql instance

- copy (with overwrite) already created *.mdf and *.ndf files with your old database (do not copy corrupted *.ldf file). Attention! names of new and old database file names must be the same.

- start sql instance (database will be in state - SUSPECT)

- start query analyser and connect to MASTER database.

- enter this sql statement:

sp_configure 'allow updates', 1

and run script

- enter this sql statement:

reconfigure with override

and run script

- enter this sql statement:

update sysdatabases
set status = 32768
where dbid = DB_ID('<your_sid>')

and run script

- enter this sql statement:

sp_configure 'allow updates', 0

and run script

- enter this sql statement:

reconfigure with override

and run script

after this your database turn on "emergency recovery mode".

- stop sql instance

- delete *.ldf log files

- start sql instance

- start query analyser and connect to MASTER database.

- enter this sql statement:

dbcc rebuild_log('<your_sid>', '<drive_path like c:databasesidloglodfile1.ldf')

and run script

- enter this sql statement:

dbcc checkdb('<your_sid>')

and run script to check errors

- enter this sql statement:

sp_dboption '<your_sid>', 'dbo use only', 'false'

and run script

- stop and start your sql instance

I have tested this solution and it works.

Regards,

Marcin Gajewski

Please reward points for helpful answers

[Former Member]

Thanks Marcin

I followed the procedure. Everything went well up to the point of recreating the log.

After running script:

dbcc rebuild_log('DV2','C:\DV2LOG1\DV2LOG1.LDF')

The result is:

Server: Msg 3624, Level 20, State 1, Line 1

Location: filemgr.cpp:1886

Expression: fcb->GetSize () < fileSize

SPID: 52

Process ID: 800

Connection

Broken

After running script:

dbcc checkdb('DV2')

The result is:

Server: Msg 926, Level 14, State 1, Line 1

Database 'DV2' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

Does this mean the datafiles are also corrupt? Where do I check the SQL Server errorlog?

Regards Hubertus

[clas_hortien]

Hello,

the error is thrown when the actual filesizes mismatch the filesizes that are stored within the MDF file, so the MDF file is corrupted as well. Try to run

dbcc dbrecover(DV2)

first. If this doesn't help i would recommend:

- run

exec sp_resetstatus ('DV2')

- run

DBCC checkdb(DV2) with no_infomsgs, all_errormsgs

Try to fix the errors by running

dbcc checkdb(DV2,<FoundRepairClause>)

If the database is in emercency mode (status in sysdatabases = 32768) you can select from the different tables. Maybe you can select some of the developments from REPOSRC etc. But i don't know which tables belong together, so maybe have to ask SAP from which tables you have to select to get the complete source of your programs.

Best regards

Clas

[Former Member]

Hello Clas

Not too much luck yet. The dbcc checkdb(DV2) results in the message

"Could not read and latch page (1:83353) with latch type SH. sysobjects failed."

I did manage to run select queries from various tables, including TFDIR which list the function modules. However, REPOSRC, REPOTEXT etc give me an error that these objects are not known.

Server: Msg 208, Level 16, State 1, Line 1

Invalid object name 'REPOSRC'.

Is that because they are system tables? Thanks for the help anyway though, at least I got a lot closer.

Regards, Hubertus

[clas_hortien]

Hello Hubertus,

you can check the single tables by running a script like

-

---

DECLARE @tablename sysname

DECLARE @username sysname

DECLARE @cmd varchar(4000)

DECLARE crs CURSOR FOR SELECT user_name(uid), name FROM sysobjects WHERE type IN ('U','S') order by name

OPEN crs

FETCH NEXT FROM crs INTO @username, @tablename

WHILE (@@fetch_status = 0)

BEGIN

PRINT convert(char(25),getdate()) + @username + '.' + @tablename

SET @cmd = 'DBCC checktable ([' +@username + '.' + @tablename +']) with no_infomsgs'

EXEC (@cmd)

FETCH NEXT FROM crs INTO @username, @tablename

END

PRINT 'FINISHED'

CLOSE crs

DEALLOCATE crs

-

---

If this script stops by an error, you have to change the select by excluding the damaged table by adding " and name > 'Last found damaged table'"

So you can get through all tables one by one and found the damaged ones. But it may be, that this script will not run at all, when the damage on sysobjects is too serious. Then there are no more options left.

Can you run a

Select * from sysobjects where name = 'REPOSRC'

or do you get an error here too ?

Good luck

Clas

[Former Member]

Hi

A second attempt to recover data from the damaged disk has given me better files, but there are still some corruptions. I will try the options you have mentioned here, but there are definitely some tables missing, specifically all the REPO* tables (exactly the ones I need). The log file seems a bit better though. Is there a way I can rebuild the whole database from the log? In the past I have always only performed full backups, no transaction log backups (out of ignorance) so in theory it should have everything in it since first install. It is about 21 GB. Also, is it possible somehow to use an older backup (the one I'm using now) and apply this logfile to it to get the changes since the last backup. SQL does not seem to like mixing files from different dates even though they are from the same databse.

It is starting to look as though it will be quicker for me simply to redevelop the last months work. Ouch!

[clas_hortien]

Hi,

the missing tables are most likely caused by the corruption on sysobjects as every select uses sysobjects to find the tables within the database. The tables are still there, but you can find them, as the entry in sysobjects is damaged.

What is the recovery model of the database (Full, simple, bilk-logged) ? If it is full or bulk-logged and you never have done a transaction log backup, you should:

- run a transactionlog backup now (to disk or to tape)

- restore an older, pre-failure full database backup to a second box. Leave the database in recovery state, so that you can apply log backups later

- apply the new transactionlog backup and let the database recover

- check if your tables and developments are there

Best regards

Clas

[Former Member]

Thanks Clas, you confirm exactly what I thought the right approach should be. Only problem is that SQL will not let me do the transaction log backup on a suspect or emergency database, and SQL will also not attach to the files. The only way I know to actually open it at all on SQL is to create a new DB with the same name, then stop SQL, replace the files with my files, and start it up again. Of course then it is in suspect status. I am busy recovering the rest of the data from the disk though, so maybe if I can replace the system databases with the same files as they were at the time of the crash, as well as the data and logfiles, then SQL will not detect that I have replaced files and maybe will allow me to run the backup of the transaction log. I would have thought that in normal disaster situations it can easily happen that the database is corrupt and cannot be opened, and so it surprises me that one cannot use the logfile as it is to reconstruct things. You actually have to open the "corrupt" database, backup the transaction log successfully, and only then can one use what is in the log.

My database is in Full recovery, and the restore of the backup works fine. Only thing is I need the latest transaction log to get it up to date again, and I cant use it because it needs to be "backed up" first. Seems like there is a weakness in the backup/restore design of SQL server here.

Regards, Hubertus

[clas_hortien]

Hi,

there is a small ray of light at the end of the tunnel. I just tried it with my pubs database and it worked. Here is the procedure:

- restore the complete database on another server (M2).

Use the newest possible backup

- shutdown the SQL Server with the defect database (M1)

- shutdown the SQL Server on M2

- rename the log on M2 to a new name

- copy over the log file from M1 to M2 and put it at the same location

as the renamed one had before

- start the SQL Server on M2

Now the SQL Server checks the latest LSN (Log Sequence Number) and should roll forward all changes from the log until the newset transaction. When the log is not corrupted (keep your fingers crossed), this should work. In my case i recreated a table, just by add the new log (with the changes) to an older copy of the same database.

Best regards

Clas

[Former Member]

Thanks Clas, that sounds like a good plan. I have actually tried this in a way already, by replacing the logfile in my restored DB with the one from the lost server. Only thing is that I probably did not do it IMMEDIATELY after the restore, and therefor SQL probably sees it as inadmissable. I shall try it.

Thanks