Log for database tempdb is not available

Question

I have been struggling with this issue from so many months now, I am saying its not a database issue and assigning this case to Storage and Operating system team and they are assigning it to me afterwards. This issue is happening repeatedly and there is no any defined pattern of occurrence.

I checked the same question asked here and I can say this is not an issue with database corruption as I am using Ola Hallengren's script for maintenance job and database integrity check(checkdb) is done on weekly basis for User and system database and no issues reported in that.

Accessed second link also for the similar question and can confirm that tempdb is in simple recovery state.

I added one additional file for data and log both so that if one becomes unavailable, other one will still be accessible however later I came to know that the access of tempdb is sequential, so it will go to second file only when first gets full:

Every time this issue occurs, i could see that we have another error in the windows application log as below:

SQLServerLogMgr::LogWriter: Operating system error 170(The requested resource is in use.) encountered.

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

One thing to note here - I checked other system databases and their file locations and could see that for master and model, data and log file is in the same drive(E drive) whereas for tempdb and msdb, data is in E drive and log file is in G drive, not sure if this is relevant.

Is there any sequence of checking system database when a job triggers or any event gets fired? If its an issue with that drive then, msdb is also on the same drive.

This is a clustered server with database drives shared in two servers and server is used for sharepoint application.

Server Version: Windows Server 2012 Standard
SQL Server: Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64) 
    Jan  5 2018 22:11:56 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Only option that we are left with is to restart the service or fail-over - it just doesn't sound good to do so.

Any help in this regard is appreciated.

Answer 1

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.