The Isolation level is default that is read committed. I will consider moving the table out of transaction scope. Can't trace now because i'm not sure when the deadlock will...

Hello opc.three. I agree with you. Even follow the same rules for providing DB permissions. This was required for temporary basis so it worked fine:

sp_addrolemember 'db_owner', 'user_name'

Thanks to all for...

Will this script do:

sp_addrolemember 'db_owner', 'user_name'

I want to grant my database developers dbo permissions on development environment.

Thanks Cadavre & Grant for pointing me to the issue. I will check more about parameter sniffing as per the links provided by you. 🙂

I think NO need for trigger refresh. However you might like to change the same to the other table, in case you are using triggers for auditing purpose.

I'll try to get the execution plan if get a chance to grab it when such incidence occurs.

Can you please suggest what steps should i take or what data should i capture which might help in finding the root cause during the time when that proc is...

I'm sure because at that moment, system DBAs have checked that there is no blocking. Only blocking is caused because of that stucked proc instance for other instances.

Secondly we delete...

It is hard to capture the execution plan of the proc instance which is getting stuck as it is coming very randomly. Like once in a month or two and...

I'll try if I can get the execution plan whenever we get this situation again. Meanwhile I am sure that there is no process blocking the stuck procedure instance.

Can anyone help on this ?

This is no time specific. So there is no quesiton of getting blocked by any process. I guess it is because of bad query plan but not sure. How to...

Gail, though I know it is useful to have the execution plan but it looks difficult to get as it comes very randomly and at very unusual time (at around...

Is it getting blocked?

The one instance which is getting stuck is blocking other processes but not getting stuck itself. It's just in the running mode for infinity.

Is that specific...

It worked perfectly. Thanks a lot. 🙂