How to manage the TempDB in order to improve performance?
search cancel

How to manage the TempDB in order to improve performance?

book

Article ID: 181562

calendar_today

Updated On:

Products

IT Management Suite Client Management Suite Server Management Suite

Issue/Introduction

 The following error is seen in the Notification Server database:

Description: Security context setup failed: 
The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.

Environment

ITMS 8.x

Resolution

Question
The NS uses the TempDB quite intensively, and not just for temporary table creation, but also for temporally placement of data before it is processed.  How can the TempDB be managed in such a way as to obtain good performance from it?

Answer
Monitor the TempDB for a week, and then create two files that are 75% of the maximum size it reached over that week.

Shrinking the tempdb is a bad idea, as you end up fragmenting the physical file all over the disk. It is better to allocate the file(s) once with sufficient space to grow.  

The following article provides some detailed information about how to optimise the TempDB: 

https://learn.microsoft.com/en-US/sql/relational-databases/databases/shrink-tempdb-database?view=sql-server-ver16