[SQL]Remove TempDB and meet an issue

No comments

2014-06-30


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

No comments

2014-06-27

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

SessionTable NamesT-SQL
52Sale.Category  and Sale.CarBEGIN TRAN, SELECT
53Sale.Car and Sale.CategoryBEGIN TRAN, SELECT
54Sale.CategorySELECT



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?
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