[SQL]Remove TempDB and meet an issue

Situation:
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

   

ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'H:\MSSQL\Data\templog.ldf')
GO

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]'

[SQL]DeadLock practice

3 Sessions, 2 Tables, 2 Transactions for Updating, 3 "SELECT" statements

Session	Table Names	T-SQL
52	Sale.Category  and Sale.Car	BEGIN TRAN, SELECT
53	Sale.Car and Sale.Category	BEGIN TRAN, SELECT
54	Sale.Category	SELECT

[ASP.NET]HTTP Error 404.3 - Not Found

[SQL]Database Files Sizes in All Databases

SQL has lots ways to get information that you needed. On the other way, they will confuse you. (Sys.databases, sys.database_files, and dbo.sysfiles)

Because you cannot remember which way is fit your situation or when your boss comes to you and he says: “I want the result right now!!”

What are you gonna do?

[SQL]sp_MSforeachdb Syntax Error, Msg 102 Level 15

Situation:

It's been painful for me for a long time. I used to write a code to query Tables, Users, or something else. Sometimes are fine, sometime has errors showed up like below..


My example scripts like below

EXEC sp_MSforeachdb '
SELECT CASE WHEN ''?'' is NOT NULL THEN ''?'' ELSE ''NODANAME'' END AS [DBName],dp.name AS [UserName],USER_NAME(drm.role_principal_id) AS [AssociatedDBRole]
FROM ?.sys.database_principals dp
LEFT OUTER JOIN ?.sys.database_role_members drm
ON dp.principal_id=drm.member_principal_id
WHERE dp.sid NOT IN (0x01) AND dp.sid IS NOT NULL AND dp.is_fixed_role <> 1 AND dp.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY DBName'

After couple hours efforts, finally I found the solution