Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,644,765 hits
  • Select GETDATE()

    July 2019
    M T W T F S S
    « Apr    
    1234567
    891011121314
    15161718192021
    22232425262728
    293031  
  • Advertisements

Posts Tagged ‘tempdb’

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 AlwaysOnPaperback, Kindle
  • Advertisements

    Posted in Tips and Tricks | Tagged: , , | 3 Comments »

    Tips and Tricks : Error: 5171 – tempdb.mdf is not a primary database file

    Posted by blakhani on August 12, 2014


    If you are getting same error for database other than tempdb then there is a serious issue with the file. Primary file is a database file which contains information about database itself like location, size of other files and other information about the database. Error 5171 means that SQL Server is attempting to get the information for a database from a file that is not the primary file.

    While doing some testing with TempDB database I started getting below errors in ERRORLOG and SQL Server was not getting started.

    2014-08-12 05:08:24.91 spid9s      Clearing tempdb database.

    2014-08-12 05:08:28.20 spid9s      Error: 5171, Severity: 16, State: 1.

    2014-08-12 05:08:28.20 spid9s      F:\TEMPDB\tempdb.mdf is not a primary database file.

    2014-08-12 05:08:28.26 spid9s      Error: 1802, Severity: 16, State: 4.

    2014-08-12 05:08:28.26 spid9s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

    2014-08-12 05:08:28.26 spid9s      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-12 05:08:28.29 spid9s      SQL Server shutdown has been initiated

     

    This started happening after I moved TempDB to new location using my own earlier blog. Here is the command which I have run

    USE master; 
    GO 
    ALTER DATABASE tempdb 
    MODIFY FILE (NAME = tempdev, FILENAME = 'F:\TEMPDB\tempdb.mdf'); 
    GO 
    ALTER DATABASE tempdb 
    MODIFY FILE (NAME = templog, FILENAME = 'F:\TEMPDB\tempdb.mdf'); 
    GO 
    
    

    If you notice closely, I have made mistake in extension of the files and due to which both files are same. This can easily be corrected by starting SQL in minimal configuration using parameter f and correcting the path.

    When I tried the same in SQL Server 2014, I got below error message, which is amazing.

    Msg 12106, Level 16, State 1, Line 6

    The path name ‘F:\TEMPDB\tempdb.mdf’ is already used by another database file. Change to another valid and UNUSED name.

    If this is happening for database other than TempDB after moving then you may want to check if move command was proper or not. You need to check logical name and the physical file path. If this is after some crash then you may need to restore from a last known good backup. If you don’t have backup then … you need to find a new assignment! Take this as a new lesson and move on. There are data recovery tools available but I have not worked with them and can’t recommend anyone.

    Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Tips and Tricks, Troubleshooting | Tagged: , , , | 1 Comment »