While playing with tempDB database on my machine, I have made some mistake and then was not able to start one SQL Instance. As usual, started troubleshooting and used sysinternals tool to find the cause of the problem.
First, I looked into ERRORLOG and found below messages. I have highlighted some text for clarity.
2014-08-07 05:53:44.13 spid11s Clearing tempdb database.
2014-08-07 05:53:44.40 spid11s Error: 5123, Severity: 16, State: 1.
2014-08-07 05:53:44.40 spid11s CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file ‘E:\TempDB\tempdb.mdf’.
2014-08-07 05:53:45.42 spid11s Error: 17204, Severity: 16, State: 1.
2014-08-07 05:53:45.42 spid11s FCB::Open failed: Could not open file E:\TempDB\tempdb.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
2014-08-07 05:53:45.43 spid11s Error: 5120, Severity: 16, State: 101.
2014-08-07 05:53:45.43 spid11s Unable to open the physical file "E:\TempDB\tempdb.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
2014-08-07 05:53:45.46 spid11s Error: 1802, Severity: 16, State: 4.
2014-08-07 05:53:45.46 spid11s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2014-08-07 05:53:45.46 spid11s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2014-08-07 05:53:45.46 spid11s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
2014-08-07 05:53:49.68 Logon Error: 17188, Severity: 16, State: 1.
2014-08-07 05:53:49.68 Logon SQL Server cannot accept new connections, because it is shutting down. The connection has been closed. [CLIENT: <local machine>]
Due to OS Error 32, SQL was not able to use files which are needed by TempDB database and unable to start.
Next task for us would be to find out which is that “another process”. If it’s an open handle by a use mode process we would be able to find out using Process Explorer. Once you download and run it, we can see something like below.
Then press Ctrl+F or use Menu option “Find” > “Find Handle or DLL” as shown below
In the find window provide file name with complete path and search. I was able to get below
Now, since I know that “another process” I can take the action which is suitable. This happened to me because my two instances are pointing to same location for tempdb database files. I have rectified them on non-starting instance by below steps.
1. Started SQL Server using “Net Start MSSQL$SQL2014 /mSQLCMD /f /T3608”
2. Connected to SQL via SQLCMD –S(local)\SQL2014
3. Executed below T-SQL
USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\tempdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\tempdb.ldf'); GO
4. Stopped SQL via “net stop MSSQL$SQL2014”
5. Started SQL normally.
(Click on Image to enlarge)
Hope this would help you in troubleshooting OS Error 32 for other application as well.