Attach DB with corrupt .ldf?

Hey gang,

I've been trying to restore a DB from it's MDF and LDF all morning;
the catch? The LDF appears to be corrupt.

When I first started the SQL Server, the db in question was marked as
"Suspect'. I did some research on this and it has caused me to attempt
detaching, backing up, deleting the LDF, using ATTACH DB and even
sp_attach_single_file_db, and sp_add_data_file_recover_suspect_db.

Nothing works.

So, with an MDF and no LDF ... is it possible to recreate this
database somehow? Can I attach the MDF to another (empty) database to
retrieve it's contents? (Tried it, couldn't get it to work), can I
extract the contents of the MDF - even if it's just the objects and
not the data itself - in some capacity?

I've read in various places about attaching an MDF with no LDF and the
system will recreate the LDF as needed, but that doesn't work either.

Ideas are most, most, most welcome.[posted and mailed]

NF (natalyafaden@.bellsouth.net) writes:
> I've been trying to restore a DB from it's MDF and LDF all morning;
> the catch? The LDF appears to be corrupt.
> When I first started the SQL Server, the db in question was marked as
> "Suspect'. I did some research on this and it has caused me to attempt
> detaching, backing up, deleting the LDF, using ATTACH DB and even
> sp_attach_single_file_db, and sp_add_data_file_recover_suspect_db.
> Nothing works.
> So, with an MDF and no LDF ... is it possible to recreate this
> database somehow? Can I attach the MDF to another (empty) database to
> retrieve it's contents? (Tried it, couldn't get it to work), can I
> extract the contents of the MDF - even if it's just the objects and
> not the data itself - in some capacity?
> I've read in various places about attaching an MDF with no LDF and the
> system will recreate the LDF as needed, but that doesn't work either.

There is a way, but I am not going to post it, because it's a
path too dangerous.

My first advice is that you open a case with Microsoft support. Yes,
that will cost you an arm and a leg, but consider how many arms and
legs losing the data will cost you. They have the procedures to recover
as much as possible.

My second advice is to restore from a clean backup.

The way I know means building a log from nothing and at all, and lead
you into the MDF as it was when things went bad. You may find a very
good database. You may also find a database that is just a big mess,
because you got it mid-transaction. And the fact that the LDF is corrupt
is an indication of that. Corruption may exist both in the SQL Server
structures and in your own data.

And if you don't understand the essence of what I'm saying above, don't
ask any further questions, but get on the phone with Microsoft.

If you absolutely want to do this on your own as a last resort before
you throw the database away, drop me a mail.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Assertion Errors:

Hi All,

I have started getting Assertion Errors in SQL.
It appears when I process a cube (Most of the time)
Other SQL statements, usually with a join or 6 do the same thing.

Whaving a scratch around google, I noticed the most people who get
these errors are using SATA drives. Either RAID or not.
Surprise, I am using SATA in RAID 1.

Is this a common thing with SATA? I can't go to the pwers that be and
say I need a couple large SCSI drives because I _think_ it's the
SATA's.

Another very odd thing that happened thismorning was I copied the mdf
and ldf files off my machine (About 70GB) and onto the server. attached
them and SQL was happy.
Select Count(*) from aview gave me the count I was expecting.
Select * From aview returned no rows. most of the time.

I thought I was going mad. F5 works, then it doesn't then it does then,
you get the point.
Backup and restore seemed better until the errors below started...

HELP!!!!

Thanks
Cheers,
Crispin

17066 :
SQL Server Assertion: File:
<q:\SPHINX\NTDBMS\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.<crispin.proctor@.gmail.com> wrote in message
news:1105982769.678231.261080@.f14g2000cwb.googlegr oups.com...
> Hi All,
> I have started getting Assertion Errors in SQL.
> It appears when I process a cube (Most of the time)
> Other SQL statements, usually with a join or 6 do the same thing.
> Whaving a scratch around google, I noticed the most people who get
> these errors are using SATA drives. Either RAID or not.
> Surprise, I am using SATA in RAID 1.
> Is this a common thing with SATA? I can't go to the pwers that be and
> say I need a couple large SCSI drives because I _think_ it's the
> SATA's.
> Another very odd thing that happened thismorning was I copied the mdf
> and ldf files off my machine (About 70GB) and onto the server. attached
> them and SQL was happy.
> Select Count(*) from aview gave me the count I was expecting.
> Select * From aview returned no rows. most of the time.
> I thought I was going mad. F5 works, then it doesn't then it does then,
> you get the point.
> Backup and restore seemed better until the errors below started...
>
> HELP!!!!
> Thanks
> Cheers,
> Crispin
> 17066 :
> SQL Server Assertion: File:
> <q:\SPHINX\NTDBMS\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.

This KB article seems to describe what you're seeing:

http://support.microsoft.com/kb/828337

Simon|||Thanks Simon,

Problem is the checkDB does the same thing.
We have replaced the drives, drive controller etc.
Even replaced the mother board.
Only thing I have not replaced on the machine is the mouse :)

My concern is we have shipped some servers to client with a simlar
config. (SATA drives running RAID 1)
Now if this is a limitation of SATA I am in a world of sh1t.
We use SATA drives as a cheaper alternative to SCSI for some clients.
I reasoned they would be faster than IDE and if Dell, HP etc have SANs
running on SATA drives, they can't be all that bad.

arrrrggggggg. This is killing me. It's actually gettiing worse. Run the
diagnostics on the drives and no error's reported.

Cheers,
Crispin

Simon Hayes wrote:
> <crispin.proctor@.gmail.com> wrote in message
> news:1105982769.678231.261080@.f14g2000cwb.googlegr oups.com...
> > Hi All,
> > I have started getting Assertion Errors in SQL.
> > It appears when I process a cube (Most of the time)
> > Other SQL statements, usually with a join or 6 do the same thing.
> > Whaving a scratch around google, I noticed the most people who get
> > these errors are using SATA drives. Either RAID or not.
> > Surprise, I am using SATA in RAID 1.
> > Is this a common thing with SATA? I can't go to the pwers that be
and
> > say I need a couple large SCSI drives because I _think_ it's the
> > SATA's.
> > Another very odd thing that happened thismorning was I copied the
mdf
> > and ldf files off my machine (About 70GB) and onto the server.
attached
> > them and SQL was happy.
> > Select Count(*) from aview gave me the count I was expecting.
> > Select * From aview returned no rows. most of the time.
> > I thought I was going mad. F5 works, then it doesn't then it does
then,
> > you get the point.
> > Backup and restore seemed better until the errors below started...
> > HELP!!!!
> > Thanks
> > Cheers,
> > Crispin
> > 17066 :
> > SQL Server Assertion: File:
> > <q:\SPHINX\NTDBMS\storeng\drs\include\record.inl>, line=1447
> > Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> This KB article seems to describe what you're seeing:
> http://support.microsoft.com/kb/828337
> Simon|||(crispin.proctor@.gmail.com) writes:
> Problem is the checkDB does the same thing.
> We have replaced the drives, drive controller etc.
> Even replaced the mother board.
> Only thing I have not replaced on the machine is the mouse :)
> My concern is we have shipped some servers to client with a simlar
> config. (SATA drives running RAID 1)
> Now if this is a limitation of SATA I am in a world of sh1t.
> We use SATA drives as a cheaper alternative to SCSI for some clients.
> I reasoned they would be faster than IDE and if Dell, HP etc have SANs
> running on SATA drives, they can't be all that bad.

The KB article that Simon referred you to, suggested that you should
open a case with Microsoft, and I would encourage you do that.

Assertion errors are always bugs in SQL Server in the sense that they
should not occur. But it could be that instead you should have gotten
a better error message. In your case, it appears that your database is
corrupt. (Which could be due to a previous hardware error.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp