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..
The server is a production server. A few days ago the server ran into some problems so the DBA:s restarted it, added some diskspace for log files and maybe they did something more. The weird thing is that after this issue the problem with selecting the data has dissappeared. The queries now execute without issues and the code I've written works as intended. I do not know what or why the issue was really fixed but the problem obviously was some edge case of circumstances that I will never know.