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.