Orphaned Sessions and locks: missing

Showing posts with label missing. Show all posts

Friday, March 23, 2012

outer join

Hello,

I am having problems with an outer join statement.
I have written a procedure that tests a table for missing and corrupt data and
to test my procedure, I take a table with 100% correct entries and corrupt them by hand. Then I test if my repaird data is looking like the correct data did.
To do the test, I copy the correct data into a temp table "copy", join it with the "repaired" table and check if any fields look different. The problem is, that i don't get the missing data. The statement is looking like this:

select o.*,'#',k.* from repaired o right outer join copy k on
(str(o.a) + 'A' + str(o.b) + 'A' + str(o.c) =str(k.a)+ 'A' + str(k.b) + 'A' + str(k.c) )
where
o.D<>k.D or
o.E<>k.E or
o.F<>k.F or
...

I have dont the concatenation because I thougt, that a join with 3 fields could be responsible for not finding the missing data in table "copy".
Before that it looked like:

... on (o.a=k.a and o.b=o.b and o.c = k.c) where ...

In table "copy" is a record missing that is in table "repaired".
Why is my statement not printingout that missing record?
Shouldn't be an outer join exactly what I have to use for finding missing data?

I anybody can help me, I would be very happy.

SvenIn table "copy" is a record missing that is in table "repaired".try LEFT OUTER JOIN instead ;)|||Your first join attempt: ... on (o.a=k.a and o.b=o.b and o.c = k.c) where ... joins o.b on itself. This sort of typo happens with the gratuitous use of table aliases.
Use a left outer join, as Rudy has suggested.
Try using the binary_checksum value rather than listing all your columns in the where clause:select repaired.*,
'#',
copy.*
from --repaired
(select binary_checksum(*) checkvalue,
repaired.*
from repaired) repaired
left outer join --copy
(select binary_checksum(*) checkvalue,
repaired.*
from copy) copy
on repaired.a = copy.a
and repaired.b = copy.b
and repaired.c = copy.c
where repaired.checkvalue <> copy.checkvalue

Monday, March 12, 2012

OT: SQL Data on RAID5 Missing disk?

My SQL data is on a software Raid5 partition on my Windows Server 2003
Enterprise.
It appears one of my disks is not coming up and another disk is reported as
"Missing" so my SQL data is not being found!!
There is another disk that nows shows up as Unknown and uninitialized. Is
this my "missing" disk?
How do I recover from this without losing my data on the Raid'
Any help would be appreciated!!!"news.microsoft.com" <junk@.ijunk.com> wrote in message
news:#oKeSA#DHHA.1196@.TK2MSFTNGP02.phx.gbl...
> Attached is what it looks like in Disk Manager.
> I tried to "Reactivate" the "Missing" disk but that didn't work and a
> bunch of events in the System log
> "The device detected a controller error on \device\harddisk1".
> And there are alos informational events in the system log for harddisk3
> such as:
> "dmio: Harddisk3 write error at block:145743940: status 0xc00000a2"
> So it appears that one drive is just physcially dead I guess and another
> is corrupted.
> I'm starting to panick because our backups the the SQL are old...
> Is there any safe way to recover from this situation'
>
You full database backups are old. Do you have an unbroken sequence of
transaction log backups dating from your last full backup?
David|||I don't think so, but I'm not sure.
The Raid that failed is my D drive where my SQL database was located.
Are you saying there might be transaction logs on the C drive somewhere?
The last backup was months ago.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ujOYcT%23DHHA.4680@.TK2MSFTNGP04.phx.gbl...
>
> "news.microsoft.com" <junk@.ijunk.com> wrote in message
> news:#oKeSA#DHHA.1196@.TK2MSFTNGP02.phx.gbl...
>> Attached is what it looks like in Disk Manager.
>> I tried to "Reactivate" the "Missing" disk but that didn't work and a
>> bunch of events in the System log
>> "The device detected a controller error on \device\harddisk1".
>> And there are alos informational events in the system log for harddisk3
>> such as:
>> "dmio: Harddisk3 write error at block:145743940: status 0xc00000a2"
>> So it appears that one drive is just physcially dead I guess and another
>> is corrupted.
>> I'm starting to panick because our backups the the SQL are old...
>> Is there any safe way to recover from this situation'
> You full database backups are old. Do you have an unbroken sequence of
> transaction log backups dating from your last full backup?
> David|||> Are you saying there might be transaction logs on the C drive somewhere?
It depends on where you decided to store the transaction log file(s) for the database. With a (IMO)
sound backup strategy:
Backup database regularly, say every day.
Backup the transaction log regularly, say every hour.
You would now do the "final" log backup, assuming the transaction log file(s), the ldf file, for the
database is available. BACKUP LOG using the NO_TRUNCATE option.
Then restore the most recent database backup and all subsequent log backups. If you managed to do
the "final" log backup, you have zero data loss.
Assuming that your most recent backup is indeed one month ago:
My guess is that you don't do regular log backups. I also guess that the database in question is in
simple recovery mode (otherwise you will have large ldf file(s) for the database).
Based on above: If you cannot get the disk back, you will only get to the one month old backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"news.microsoft.com" <junk@.ijunk.com> wrote in message
news:%23AWC5i%23DHHA.4464@.TK2MSFTNGP06.phx.gbl...
>I don't think so, but I'm not sure.
> The Raid that failed is my D drive where my SQL database was located.
> Are you saying there might be transaction logs on the C drive somewhere?
> The last backup was months ago.
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message
> news:ujOYcT%23DHHA.4680@.TK2MSFTNGP04.phx.gbl...
>>
>> "news.microsoft.com" <junk@.ijunk.com> wrote in message
>> news:#oKeSA#DHHA.1196@.TK2MSFTNGP02.phx.gbl...
>> Attached is what it looks like in Disk Manager.
>> I tried to "Reactivate" the "Missing" disk but that didn't work and a bunch of events in the
>> System log
>> "The device detected a controller error on \device\harddisk1".
>> And there are alos informational events in the system log for harddisk3 such as:
>> "dmio: Harddisk3 write error at block:145743940: status 0xc00000a2"
>> So it appears that one drive is just physcially dead I guess and another is corrupted.
>> I'm starting to panick because our backups the the SQL are old...
>> Is there any safe way to recover from this situation'
>>
>> You full database backups are old. Do you have an unbroken sequence of transaction log backups
>> dating from your last full backup?
>> David
>|||>> On 11/24/2006 at 9:50 AM, in message
<#AWC5i#DHHA.4464@.TK2MSFTNGP06.phx.gbl>,
news.microsoft.com<junk@.ijunk.com> wrote:
> I don't think so, but I'm not sure.
> The Raid that failed is my D drive where my SQL database was
> located.
> Are you saying there might be transaction logs on the C drive
> somewhere?
> The last backup was months ago.
If the data truly is important, don't risk screwing things up more by
trying things yourself unless you are *extremely* confident in your own
skills.
This is the time to pay someone who knows what they are doing.
Otherwise you risk turning a possibly recoverable scenario into a
non-recoverable scenario.
MS PSS might be a good place to start, as might your hardware vendor.
Make sure you explain that you don't have backups, so you want to be
very careful not to do anything that is likely to cause more problems.|||Check Tibor's article:
Minimizing data loss when accidents happens
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"news.microsoft.com" <junk@.ijunk.com> wrote in message
news:%23AWC5i%23DHHA.4464@.TK2MSFTNGP06.phx.gbl...
>I don't think so, but I'm not sure.
> The Raid that failed is my D drive where my SQL database was located.
> Are you saying there might be transaction logs on the C drive somewhere?
> The last backup was months ago.
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:ujOYcT%23DHHA.4680@.TK2MSFTNGP04.phx.gbl...
>>
>> "news.microsoft.com" <junk@.ijunk.com> wrote in message
>> news:#oKeSA#DHHA.1196@.TK2MSFTNGP02.phx.gbl...
>> Attached is what it looks like in Disk Manager.
>> I tried to "Reactivate" the "Missing" disk but that didn't work and a
>> bunch of events in the System log
>> "The device detected a controller error on \device\harddisk1".
>> And there are alos informational events in the system log for harddisk3
>> such as:
>> "dmio: Harddisk3 write error at block:145743940: status 0xc00000a2"
>> So it appears that one drive is just physcially dead I guess and another
>> is corrupted.
>> I'm starting to panick because our backups the the SQL are old...
>> Is there any safe way to recover from this situation'
>>
>> You full database backups are old. Do you have an unbroken sequence of
>> transaction log backups dating from your last full backup?
>> David
>|||news.microsoft.com wrote:
> I don't think so, but I'm not sure.
> The Raid that failed is my D drive where my SQL database was located.
> Are you saying there might be transaction logs on the C drive somewhere?
> The last backup was months ago.
>
Painful as it may be, I suggest you first get someone on site who knows
what they are doing. Obviously a lot of damage has already been done
and now your data is at risk.
Except for a read-only database, RAID5 is not a great choice for a
database server because of the performance and availability
constraints.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Well, I physically went to the server and found one disk on boot sounded
like two stones grinding on each other at 10k RPM...
The other failed disk just says "Media Error" on the Adeptec BIOS boot
discovery.
If I could find some temporary way of getting this drive to work for like 10
minutes then I could copy out the database.
Any ideas?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1164397255.045467.37770@.f16g2000cwb.googlegroups.com...
> news.microsoft.com wrote:
>> I don't think so, but I'm not sure.
>> The Raid that failed is my D drive where my SQL database was located.
>> Are you saying there might be transaction logs on the C drive somewhere?
>> The last backup was months ago.
>>
> Painful as it may be, I suggest you first get someone on site who knows
> what they are doing. Obviously a lot of damage has already been done
> and now your data is at risk.
> Except for a read-only database, RAID5 is not a great choice for a
> database server because of the performance and availability
> constraints.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Friday, March 23, 2012

outer join

Monday, March 12, 2012

OT: SQL Data on RAID5 Missing disk?

OT: SQL Data on RAID5 Missing disk?

Orphaned Sessions and locks

Blog Archive

About Me