May 31, 2019 at 2:07 pm
While running the script below I am seeing all the tempdb files but I am just able to see the primary and log file(2 files) in the GUI.
Use master
GO
SELECT
name AS [LogicalName]
,physical_name AS [Location]
,state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
May 31, 2019 at 2:19 pm
What GUI do you use?
btw, id of tempdb is 2
May 31, 2019 at 2:21 pm
When looking into the properties of TempDB on SSMS, I just see two files not all the files that the script provides.
May 31, 2019 at 2:51 pm
Microsoft SQL Server Management Studio 14.0.17289.0
Microsoft Analysis Services Client Tools 14.0.1016.283
Microsoft Data Access Components (MDAC) 10.0.17134.1
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 9.11.17134.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.17134
May 31, 2019 at 2:59 pm
Other thing is I was able to delete those files which were not showing on GUI but when I run the scripts those file name still show up.
Maybe failing it to another node might clear up , if not going to re-start the SQL services.
May 31, 2019 at 3:25 pm
This happened to us yesterday when the Server team cloned a production server, the disks/partitions weren't the same as production so he couldn't find the tempdb files in the cloned server, the sql instance wouldn't start, what we did was start in minimal configuration mode.
Net start MSSQLSERVER /f
we mapped the main tempdb files(the instance can start if you only map the tempdev and templog) in another drive, we restarted the instance and it started.
use master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = N'E:\SQL SERVER DATAFILES\TEMPDB\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = N'E:\SQL SERVER DATAFILES\TEMPDB\templog.ldf');
GO
when we went to the GUI we couldn't see the other 6 tempdb files, what we did was removed them and recreated them.
ALTER DATABASE TEMPDB REMOVE FILE tempdb1;
GO
when you can see the files in master_files means you can remove them (that's what i think).
Greetings.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply