• Same, I have auto seed on and most of the time I have some db's that get out of sync.  Like about 15-20 out of 170 db's need manually...

Same here, mine were fine for a few years before this issue started about 6 months ago.

I also started to experience this about 6 months ago, I wonder if an SP introduced the issue.  I had over 300 tiny databases in my AG.  I've reduced it...

You might try DBCC Updateusage.

I've had to run that coming off some old servers to fix performance issues if it seems storage is your related wait types.

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-ver16

Have you reviewed the queries involved in the deadlocks?

Perhaps you are under -indexed or over-indexed.  Maybe the queries can be tuned to hit the tables more efficiently (and same order).

I'm with Jeff M, probably a security setup issue, like remote user mapping issue.

If using Win Auth, it could be an SPN issue.

Or possibly not calling it correctly in the...

• This reply was modified 2 years, 11 months ago by rothj.
• This reply was modified 2 years, 11 months ago by rothj.
• This reply was modified 2 years, 11 months ago by rothj.
• This reply was modified 2 years, 11 months ago by rothj.
• This reply was modified 2 years, 11 months ago by rothj.

Since nobody has suggested anything...look into the SPN's if it's a domain user.

Double hop scenario for Windows accounts will show up as anonymous (Kerberos not forwarded).

You might check the schema change history report (data from default trace).

On the database, right click, choose Reports > Standard Reports > Schema Changes History from context menu.

I agree with Johan's approach.  Use Data Migration Assistant to check for deprecated code.

Go with a new server with all the latest. You build it with a different name like...

Thanks for everyone's input.  I presented my findings to the SR DEV, he also feels the Bank subquery is redundant.  He also doesn't believe we need DISTINCT and NOLOCK.  He...

No missing index suggestion. It didn't use the your recommended one.

But when Jonathon added both columns the WHERE clause, it might have taken care of it.

c.CustomerNumber = t.CustomerNumber

AND c.Customer = t.Customer

Jonathon, your second query brought the spool back.  But I...

I'm...

What is the difference in using 'EXISTS' instead of 'IN'?

Yes!   Thank You very much!