I have a scheduled job that processes documents stored in tables in a SQL database.
It processes documents “10 at a time” and moves the binary document data to another database and has been running for about a week continously. (There are A LOT of documents to process)
Today the job got a timeout and I’ve been trying to figure out the cause. At first I suspected that it could be some big file but this does not seem to be the issue.
Instead, I’ve noticed that there seems to be some issue with a specific column and this seems to be the column “filename”. This column is just a type “nvarchar” though so it’s so weird.
My findings are that if I simply comment the column “filename” so it is not a part of the select then the query works. If I add it (and I need it!) then I get a timeout. Also, even if I don’t add it to the select but instead add it to an “order by filename” or “group by filename” then I also get a timeout.
My query, that returns about 3600 records, is the one down below. In the example that I’ve posted below I have removed my “top 10”. The result is the same though, whenever adding “filename” I get timeout.
select
att.AttachmentId,
att.FileSize,
att.MimeType,
att.FileName
from Attachment att
join ActivityMimeAttachment ama on att.AttachmentId = ama.AttachmentId
join ActivityPointerBase apb on apb.ActivityId = ama.ObjectId
where apb.CreatedOn < (GETDATE() - 1095)
and att.Body is not null
order by att.FileSize desc
--option (recompile)
It just seems so weird that this started happening. I would really like to know the name of the potential file that causes the timeout but the irony is that I cannot see it because adding the filename causes the issue. But I my program has processed 1.8 million files so far without issue and now all of a sudden this happens..
Any ideas or suggestions are welcome.
Also worth mentioning is that I’m using “Dynamics 365 Linq” to do the select which translates into this SQL query. So making a stored procedure is not an option. I could however make some index on the database perhaps but like I mentioned, I’ve processed a huge amount of files so far without issues..