Thanks much Barry. Not sure how to post the DDL for the table. I'm not the owner, creator or administrator. Anyway I'm attaching the screen shot for those three tables Patient_v1/Consultations/Measure. So that you can see the indices clearly. There are no keys of them.

Also attached the execution plan.

From my end, the error message is always:

"Could not allocate space for object '<temporary system object: 422219562090496>' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."

At server end, the tempdb fills up all the disc space; and the sql uses up all the CPU.

One quick question for you: What should an DBA or the IT person having access to the db server do to clean up the situation without restarting the server? Both of us are not experienced in this kind of case.

Very grateful for any advice.

Best,

Helen