Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Tips and Tricks : Why TempDB files lesser than Configured?

Posted by blakhani on September 16, 2014


TempDB is one of the special system database which has special characteristics. Recently I was trying to simulate a problem and demonstrate the solution to tempdb PFS page contention. While doing something I realized that number of tempDB files were only 2 as opposed to 4 which I configured.

Here is the query which I used to find configured files vs actual files.

use master
select    name, physical_name 
from    sys.master_files
where    database_id = 2
go
use tempdb
go
Select    name,physical_name 
from    sys.database_files

 

Then I looked into the SQL Server ERRORLOG and found below messages during startup.

2014-09-16 17:45:56.330 spid11s      Starting up database ‘tempdb’.

2014-09-16 17:45:57.380 spid11s      Error: 5123, Severity: 16, State: 1.

2014-09-16 17:45:57.380 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\AnotherTempDBFile.ndf’.

2014-09-16 17:45:57.380 spid11s      Error: 5123, Severity: 16, State: 1.

2014-09-16 17:45:57.380 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\AnotherTempDBFile.ndf’.

Why this happened? Well. I had done some goof-up and pointed two instances to use same file. Which means that it is expected behavior that if tempdb database is able to initialize the primary files, it would come up and use only two files. This would also happen if there is a problem with the location of newly added files.

2014-09-16 15:31:14.290 spid11s      Starting up database ‘tempdb’.

2014-09-16 15:31:14.890 spid11s      Error: 5123, Severity: 16, State: 1.

2014-09-16 15:31:14.890 spid11s      CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘E:\TempDB\AnotherTempDBFile.ndf’.

2014-09-16 15:31:14.890 spid11s      Error: 5123, Severity: 16, State: 1.

2014-09-16 15:31:14.890 spid11s      CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘E:\TempDB\AnotherTempDBFile.ndf’.

To repro above, I have renamed the folder TempDB to something else. Note that same thing might happen if we have space issues to create new files.

 

Hope this helps.

 

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn ā€“ Paperback, Kindle
  • Advertisements

    3 Responses to “Tips and Tricks : Why TempDB files lesser than Configured?”

    1. Aman said

      Quite funny šŸ™‚

      Thanks for sharing!!

    2. Can you even do that (point 2 instances to use the same tempdb files?!) It should be a crime šŸ™‚

      Thank-you very much for sharing!

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: