Thank you for all the suggestions

Hi yes it is set to 2048 I will check about awe and lock pages in memory I have proposed to upgrade

And the SSIS packages ran to success they haven’t done any debug and the packages ran through MSDB

Thank you I might go into perfom to find out what is going on they do reboot the server

It’s only 2 GB I don’t think I should lower it more than that do you think can affect SSIS operations if it’s set to too low ?

you are right i have changed the account of sql to a service account and it works it was permission issue thank you

I have set up alerting on both sides but only the mirror is the one that sends messages with threshold alerts ... the alert comes when 80 per sent threshold...

fixed it with changing the MAXDOP to 4

thank you will have a look but still i dont get why it failing intermittent with this error.

QUOTED_IDENTIFIER IS ON on the query

Both of them are ON ODBC and db settings

I m not that good in reading execution plans but I did see the different between the scans and the seeks. Is there anything you think I should look out for? 

I have backed up DEV db and restored it on TEST server  and then i have run on that DEV db on Test server the same query i was running...

they are virtual and i think its not environment related since i have restore the db from DEV to test and it was running as fast as in DEV.

Similar servers and memory is more on Test which doesnt make sense either