info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



T-SQL Delete Extra TempDB Data Files
by BF (Principal Consultant; Architecture; Engineering)
2018-11-01








Solution:


--Try Delete TempDB Data Files

ALTER DATABASE [tempdb] REMOVE FILE [tempdb7]
GO

Msg 5042, Level 16, State 1, Line 2
The file 'tempdb7' cannot be removed because it is not empty.

USE [tempdb];
GO
DBCC SHRINKFILE (tempdb7, EMPTYFILE);
GO

DBCC SHRINKFILE: Page 14:157472 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 10
Cannot move all contents of file "tempdb7" to other places to complete the emptyfile operation.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



--Fix:

DBCC FREESYSTEMCACHE ('ALL')
DBCC FREEPROCCACHE
DBCC SHRINKFILE (tempdb7, EMPTYFILE);
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdb7]
GO

sp_helpdb 'tempDB'

Re-Start SQL Server Service


--Relocate TempDB Data File & adjust size
ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev,
FILENAME = 'D:\DB\tempdb.mdf',
SIZE = 2048MB,
MAXSIZE = 2048MB,
FILEGROWTH = 512MB)
GO

--Relocate TempDB Log File & adjust size
ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog,
FILENAME = 'D:\DB\templog.ldf',
SIZE = 2048MB,
FILEGROWTH = 1024MB)
GO

Re-Start SQL Server Service


--Add New TempDB Data Files
ALTER DATABASE [tempdb]
ADD FILE
(NAME = N'tempdb2',
FILENAME = N'D:\DB\tempdb2.ndf',
SIZE = 2048MB,
MAXSIZE = 2048MB,
FILEGROWTH = 512MB)


--Add New TempDB Data Files
ALTER DATABASE [tempdb]
ADD FILE
(NAME = N'tempdb3',
FILENAME = N'D:\DB\tempdb3.ndf',
SIZE = 2048MB,
MAXSIZE = 2048MB,
FILEGROWTH = 512MB)

--Add New TempDB Data Files
ALTER DATABASE [tempdb]
ADD FILE
(NAME = N'tempdb4',
FILENAME = N'D:\DB\tempdb4.ndf',
SIZE = 2048MB,
MAXSIZE = 2048MB,
FILEGROWTH = 512MB)


--Add New TempDB Data Files
ALTER DATABASE [tempdb]
ADD FILE
(NAME = N'tempdb5',
FILENAME = N'D:\DB\tempdb5.ndf',
SIZE = 2048MB,
MAXSIZE = 2048MB,
FILEGROWTH = 512MB)

Re-Start SQL Server Service