Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Posts Tagged ‘Could not create tempdb’

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
  • Advertisements

    Posted in Tips and Tricks, Troubleshooting | Tagged: , , , | 1 Comment »

    Tips and Tricks : OS error: 32(The process cannot access the file because it is being used by another process.).

    Posted by blakhani on August 7, 2014


    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.

    image

    Then press Ctrl+F or use Menu option “Find” > “Find Handle or DLL” as shown below

    image

    In the find window provide file name with complete path and search. I was able to get below

    image

    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.

    image

    (Click on Image to enlarge)

    Hope this would help you in troubleshooting OS Error 32 for other application as well.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Troubleshooting | Tagged: , , , , , , , , | 2 Comments »