Log for database tempdb is not available
SQLServerLogMgr::LogWriter: Operating system error 170(The requested resource is in use.) encountered.


Yeah, this is bubbled up from the OS error the logwriter is running into. In this case it's OS error 170 which is the resource is in use. That's pretty damning that the issue is outside of SQL Server (barring any bad 3rd party DLLs loaded into the address space).

Normally when I see this it's anti-virus, host protection, defragmentation software, backup software, etc., that ends up holding long locks on the files or in clustered environments when the drives aren't properly setup on the back end.


  Storage team has excluded these files from anti-virus scanning.


Not trying to be rude, but I'm not buying it. Tell them you want proof. Also, just because the file is "excluded" doesn't mean the anti-virus software doesn't look at it, it still does - just doesn't do all the in-depth checks in most cases.

Now What?

I'd start with checking to see if a rogue application is trying to do something. The problem is that this could happen at any time and offending (if any) application could hold the lock for a short time or for a long time - we don't know.

One of the ways to quickly do this would be to use the sysinternals handle application on the file having the issue. Since this error has an error code in SQL Server for it, you should be able to create an agent alert to execute a job which calls a small batch file or PowerShell or whatever you create to run handle against the file. That should help try to grab the information quickly (but might not be quick enough).

Another way to go about it, especially if you can reproduce it fairly frequently or easy, is to run process monitor (procmon) which is another sysinternals tool to capture what is touching that file other than SQL Server.