Share via

TEMPDB files fail to be created with error: "CREATE FILE encountered operating system error 32"

  • Article

The TEMPDB database is a unique database, as its files are deleted and recreated every time you restart the SQL Server services.

If all the files of TEMPDB fail to be recreated for some reason, the SQL Server service won't be able to start.

In a specific scenario, the error we saw was:

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 '<folder\tempdb.mdf>'

Some common root causes for this error are:

[1]

Antivirus exclusions have not been set correctly at the machine that is running the SQL Server service.

The exclusions are documented here: How to choose antivirus software to run on computers that are running SQL Server

[2]

There is another process "touching" the TEMPDB files, like a backup application or another instance of the SQL Server service.

To check this possibility you can use the Process Explorer tool to see which processes have locks at the TEMPDB files.

[3]

The metadata of the TEMPDB database are mixed and there exist multiple TEMPDB files that have different logical names but they use the same physical name.

To check this possibility, you can run these two queries and check if some of the files have the same physical name:

SELECT name, physical_name from sys.master_files

SELECT name, filename from sysaltfiles

In this scenario, if you capture a Process Monitor trace, you will see a "SHARING VIOLATION" event regarding the sqlservr.exe process.

To address this issue, you can simply change the location of the physical files so that they are created at different locations on the disk, with different names and they do not collide.

e.g.

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'M:\TEMPDB\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'M:\TEMPDB\templog.ldf');

GO

Comments