A user complained that a SQL query has started timing out. I couldn't see the timeouts in sql logs but when I started a trace, I could see timeouts:
"Error 1222 Severity 16 State 18"
It was from spid 17 which is a system process but in user database. After some research I found that
"This error means that a lock was requested in msdb and timed out. Usually, that's going to mean it's a big transaction on a big temp table or a big sort, or something of that type"
As per this link, I tried disabling ghost record removal process using:
DBCC TRACEON (661,-1)
But that didn't help either so I changed it back.
Any idea what I can do?