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.
| Author |
Topic |
|
SqlZ
Yak Posting Veteran
69 Posts |
 Posted - 2001-11-19 : 14:59:01
|
| I know its a weird subject line but didn't know what else to write here. Here is my situation...I ran this update statement on approximately 34 million records:UPDATE FPS SET FPS.patstatus_programid=CASE p.shortnameWHEN 'CAD' THEN 1WHEN 'HF' THEN 2WHEN 'COPD' THEN 3WHEN 'Diabetes' THEN 4ELSE 5 ENDFROM FactPatientStatus FPSINNER JOIN programdiagnosishist pdh ON (FPS.pat_patientid=pdh.patientid)INNER JOIN patientdiagnosis pd ON (pdh.patientdiagnosisid=pd.patientdiagnosisid)INNER JOIN program p ON (pd.icdcode=p.programicdcode)WHERE FPS.patstatus_programid IS NULLAND FPS.patstatus_EffectiveDate BETWEEN pdh.programstartdate AND IsNull(pdh.programenddate,Getdate())I know this is a very expensive (4 table join) update statement but it ran for 63 hours and did not complete. I stopped the statement because I felt it would run forever, and I wanted to double check all of my indexes to see if I missed anything. I can't even connect to my sql server. Is it rolling back everything and is there any way to stop this? Next time should I turn off the transaction log in some way? Bulk-Loaded db? I am at a loss to why this is killing my system. I know it probably should be running slow because its rolling back all the transactions but shouldn't I be able to at least connect? One last thing, there is nothing else running on this server besides SQL. Thanks in advance and if you need more details, please ask. |
|
|
|
|
|
Server is crawling (Explained Inside)