Loosing primari keys when system crash

Hello,
When we our MS-SQL server has system crash a lot of all our primair keys are lost.
Some time even fields are lost.
We are running MS-SQL server on a cluster and on a stand alone server
Dos some one no the problem and nows a solution.What kind of the backup strategy do you use?

Lookup value query joining two tables

David:

It is not a join as keys are not present in both the tables; between
will just restrict the values for test_val. However, the following
solution has been proposed by Joe Celko, which works. Thanks to
everybody who contibuted. David and Joe, thanks so much.

UPDATE T1
SET factor
= (SELECT T2.factor
FROM T2
WHERE T1.test_val BETWEEN T2.from_val AND T2.to_val)
WHERE EXISTS
(SELECT *
FROM T2
WHERE T1.test_val BETWEEN T2.from_val AND T2.to_val);

--CELKO--> It is not a join as keys are not present in both the tables

Keys are not a requirement for a join. Any boolean expression (including the
BETWEEN predicate) can be used as the basis of a join between two tables.
That's with or without keys or corresponding columns in both tables.

--
David Portas
SQL Server MVP
--

Lookup error redirection problem

Hi,

Maybe someone can assist me in solving the following error.
I created a facttable with surrogate keys. In the package that processes the fact data a lookup-task is supposed to find the appropriate dimension key.
If a certain dimension row is not found, this error is redirected.
In this errorflow a stored procedure will add the missing row in the dimensiontable, after which another lookup takes place. Then the results of both lookups are brought together in a union all task.

When I test this errorflow I notice that the missing dimension row is indeed added to the dimensiontable. The only task that turns red is the first lookup. Please refer below for the errormessages.
I understand the first one; that is why I created the redirection. But what does the rest mean? And even if I redirect the error for further processing is it still counted as a raised error? If the maximum allowed is 1 (as indicated) will the task still fail?

Any help will be greatly appreciated. Thanks in advance and regards,

Albert.

The errormessages are as follows:
Error: 0xC020901E at Xforms, lookups en wegschrijven, Lookup LosplaatsKey [5071]: Row yielded no match during lookup.
Error: 0xC0047072 at Xforms, lookups en wegschrijven, DTS.Pipeline: No object exists with the ID 5317.
Error: 0xC0047022 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Lookup nwe losplaats" (5315) failed with error code 0xC0047072. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread1" has exited with error code 0xC0047072. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047039 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread2" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047039 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread3" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047021 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread2" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047021 at Xforms, lookups en wegschrijven, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread3" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
.
.
.
Task failed: Xforms, lookups en wegschrijven
Warning: 0x80019002 at FactRittenInit: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (8) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "FactRittenInit.dtsx" finished: Failure.

It looks like it is not set to redirect. The error "Row yielded no match during lookup." hints this.
if you are certain the row is in the dim, is the case the same? Lookups are case sensitive.

Try deleting the lookup and re-adding it?|||

Hi Crispin,

Thanks for your reply. What I described is not yet a production situation, but a test in development.

The testcase is such that indeed one lookup should not yield a match. This row is then redirected and added by means of a stored procedure. After this a new lookup is performed and then the result is unioned with the first flow.

| Error redirect

V

Lookup Key --> OLE DB Command (proc adds row)

| |

V V

Union all <-- Lookup new key

|

V

The behaviour is that 'Lookup Key' notices that one dimension row is missing (which is true) and turns red. The lookup is redirected as expected, the 'OLE DB Command' adds the missing row in the dimension table and the 'Lookup new key' and 'Union all' succeed.

Nevertheless then the process stops because of the errormessages mentioned before.

Albert

|||I'm more concerned with this error:

Error: 0xC0047072 at Xforms, lookups en wegschrijven, DTS.Pipeline: No object exists with the ID 5317.|||It looks like your second failure is on the second lookup - after the new row is added. Lookups, by default, cache all their data at the beginning of the data flow. So even if you are adding the new rows, the cached values in the second lookup won't have it. To work around this, go to the Advanced tab of the second lookup, and check enable memory restriction, which turns off caching.|||

Hi John,

You are right! Never thought of that. Thanks (you save me a headache).

Albert.

Lookup Component Stalls

i am using a lookup component to do a typical SCD. Compare the Natural keys and if they are the same -- REdirect the rows and do whatever, If not present -- means the Error Rows -- redirect and do whatever.

WHen I use the component to do a Historical Load (which means -- there are no rows are in the Destination table) and put the Memory to Partial Cache -- the Data Flow STalls after about 46,000 rows, it just doesnt complete after that. But the moment I switch it to Full Cache -- it flows -- But Partial is what I am supposed to be using -- keeping in mind -- the Incremental Loads. Why does the component stall ?

I had used Partial Cache in an earlier project -- with a 18 Million Row Table --(albeit for incremental load) and it worked fine (though is was slow -- but tleast it worked) -- but now I am trying to load just 300,000 rows but it stalls.

I am using a 2GB RAM machine -- and set the Memory to 750 MB/500 MB nothing worked

I tried two different machines -- same thing happened.

Any insight will be appreciated.

I am jut wondering why you said that the partial cache is required in the historical load. I always use (after making sure the server have enough memory available) full cache.

How is your LKup set up? you should be using the only required columns; you said this is for a SCD; so you should be uisng a query with the busines/natural key columns only (please provide the number of columns and data type of the required columns). Also, how is the source component set up? are you using 'fast load'?

|||

Hmmm so you use Full Cache always? If I leave it at Full Cache -- from next time onwards during Incremental Loads -- it will load all 290,000 rows into memory and possibly less than 10 rows have changed. I dont want to do that.

Yes I bring ONLY the required columns actually 4 columns NaturalKey, InferredMember, Hash1 and Hash2 -- all Integers

Yes I use Fast Load.

I use OLEDB for my Source.

|||

JaguarRDA wrote:

Hmmm so you use Full Cache always? If I leave it at Full Cache -- from next time onwards during Incremental Loads -- it will load all 290,000 rows into memory and possibly less than 10 rows have changed. I dont want to do that.

Yes I bring ONLY the required columns actually 4 columns NaturalKey, InferredMember, Hash1 and Hash2 -- all Integers

Yes I use Fast Load.

I use OLEDB for my Source.

An INT32 field, though (call it "natural key") will only take up 1.1MB of memory to store 290,000 rows. Not too bad, eh?|||

That is exactly my point. Even with 4 columns SSIS should be able to cache the whole result set in no time. I would recommned you to watch the progress tab in BIDS to check how long the lookup caching takes. Check also the task manager to see if there is any other process taking to much resources from the box. You can try to replace the OLE DB destination with a rowcount transformation just to test the transformation speed. You can remove the lookup and destination and use the same row count to mesaure the 'reading' speed from the source component.

Make sure you are using fast load in your OLE DB destination.

Lookup as Inner join ?

Hello all.....

I got a doubt whether we can use Lookup as an inner join.
If I have duplicate keys in lookup source....can I get the duplicate rows also into the destination.

Ex:
source:
c1 c2 c3

1 a1 b1
2 a2 b2
3 a3 b3
lookup table:
c1 c4 c5

1 c1 d1
2 c21 c22
2 c23 c24
3 c3 c3

noe the result should be:
c1 c2 c3 c4 c5
-- -- -- -- --
1 a1 b1 c1 d1
2 a2 b2 c21 c22
2 a2 b2 c23 c24
3 a3 b3 c3 c3

in msdn I read the look up will get the first match.....is it possible to get all matches?

Thank you

No, you can't get multiple matches from the Lookup. You should use the Merge Join component to do that.
|||

Dear friend,

use the merge Join, and you can specify LEFT, INNER or RIGHT JOIN!

Helped?

regards!

|||

yes I know about the merge join componant. but for that I need to sort the data before joining. and till now I used my source table which has 6 million records only once and with the multicast I generated all the neccessary fact tables. and to generated one more facts table I need to make an inner join with another table. that's why I asked whether there is any setting where we can get all the matches. I can also use the merge join here....and to sort this 6 M records in the middle of the package is taking all the RAM and time.

I can't sort them in the starting because this table is alredy sorted with some other columns for merge join

input.

anyways thank you for your help. If it's not possible with lookup...I need to do something else. I think I need to read the table again with needed keys sorted.

Thank you.