суббота, 27 ноября 2021 г.

Move or drop tempdb in MSSQL

Drop TEMP DB:

USE [tempdb]

GO

DBCC SHRINKFILE (N'tempdb1', EMPTYFILE)

GO

ALTER DATABASE [tempdb] REMOVE FILE [tempdb1]

GO 


If file is locked, run this script:

DBCC DROPCLEANBUFFERS;

GO

DBCC FREESYSTEMCACHE ('ALL');

GO

DBCC FREEPROCCACHE;

GO

DBCC FREESESSIONCACHE;

GO


Move tempdb:

USE master;

GO

ALTER DATABASE tempdb 

MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\DATA\tempdb.mdf');

GO