[SQL]Remove TempDB and meet an issue


One of the DB I/O issues encountered, and I found the "Log" file and the "Data" file located at the same folder/ the same LUN.

So I need to move the "Log" file to improve the performance.

Environment: Clustered

Preliminary: move the instance to the other node.

Command: Type the command to move tempdb Log file

(NAME = templog, FILENAME = 'H:\MSSQL\Data\templog.ldf')

Then if I want to DELETE the old tempdb.ldf, I have to restart SQL Server service

But I forgot, it's on Clustered environment, so when I opend SSMS, I got an error below

Property Size is not available for Database '[tempdb]'

I thought, OH OH I CANNOT access the "TEMPDB", how about others users, I do not know how to fix....

DO NOT BE PANIC, Follow me to do this

Step 1: open a query and type

SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files

If it's successful, it means your tempdb is available to access, but WHY I got this error when I open the properties of TEMPDB.

Give it the time. I don't know why but the SSMS took 10 mins to recovery the properties, then it will be available to open

Newer Older