Recently we get stuck with an issue in Our Production SQL Server instance while moving the cluster instance to another node.
Experienced DBAs knows that SQL Sever has it’s own Logs folder however there are multiple ways to find this folder, some of them are described here.
Upon reviewing several error logs, we came to know that while adding multiple Data files for TempDB to improve performance , the DBA has mistakenly duplicated the logical file name and same name was assigned to two different physical files for TempDB.
Below are some findings from logs.
2015-03-24 16:20:23.21 spid9s Starting up database ‘tempdb’.
2015-03-24 16:20:23.47 spid9s Error: 1828, Severity: 16, State: 1.
2015-03-24 16:20:23.47 spid9s The logical file name “tempdbDatafile3” is already in use. Choose a different name.
2015-03-24 16:20:23.48 spid9s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
To fix this issue we have done the following:
1) Inform all the clients to take their systems into maintenance mode which are connected with this SQL Server instance.
2) Open SQL Server Configuration Manager and disable the TCP/IP. This will help in running SQL Server in Single User Mode & avoid connections coming to this instance. Our Service was already failing to start.
3) Open 2 command prompt with elevated privileges and run this command in 1st window: sqlservr -s MSSQLSERVER -c -f
You may face the error below.
To find the location of sqlservr.exe in the registry, the key name is “SQLBinRoot” and path is :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\Setup
here MSSQL11.MSSQLSERVER is name of your instance.
Copy the value of “SQLBinRoot” which could be like : C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008\MSSQL\Binn (this path may varies due to version difference).
Execute above command:
Once its started, scroll down and try to find Pipes connection end point:
Copy this connection string (\\.\pipe\MSSQL$SQL2008\sql\query in above example). This will very according to version and named instance configuration.
More about Named Pipes can be found here.
In 2nd Command prompt window run this command: sqlcmd -S \\.\pipe\MSSQL$SQL2008\sql\query
If you face below error, try stopping sql server and restarting again in first window (simply Press Ctrl+ C and press Y to stop it).
In second attempt we are able to connect.
Now to find the files related to TempDB, execute the following command.
select name, physical_name from sys.master_files where name like ‘%temp%’;
It will show you files list and you can identify the issue (Duplicated logical name found tempdbDatafile3)
To remove this file because we cannot rename in our case due to duplication run below command.
alter database tempdb remove FILE tempdbDatafile3;
Run select command again to verify if file is deleted.
After verification stop/close all command windows, enable the TCP/IP pipes in SQL Server configuration manager and start SQL Instance Service.
Read Move about Alter database file command.
Enjoy SQL Servering…