VERIFIED SOLUTION i

Learn to control and maintain tempdb size in AURA

The tempDB system database is a global resource that is available for all users connected to the instance of SQL Server and is used to hold the following:
 
Temporary user objects that are explicitly created, such as global or local temporary tables, temporary stored procedures, table variables, or cursors.
 
  • Internal objects that are created by the SQL Server Database Engine. For example,work tables to store intermediate results for spools or sorting. As tempDB is a global resource, so it is mandatory to use its space effectively and efficiently when the tempDB size comes down to the critical limit. It is recommended to keep an eye on some performance majors of tempDB and keep monitoring the size of tempDB.
     
  • tempDB should reside on its own dedicated physical disks. This allows it to split I/O transactions from the remainder of volumes on the SQL Server.
  • The size of the tempdb database can affect the performance of a system. For example, if the size that is defined for tempdb is too small, part of the system-processing load may be taken up with auto-growing tempdb to the size required to support the workload every time you restart the instance of SQL Server. You can avoid this overhead by increasing the sizes of the tempdb data and log file.
There are various ways to free up the space occupied by tempDB. Mechanism using which tempDB is controlled and maintained are decided by DBA depending
on the database policies of Organization hence it is always recommended to contact DBA maintaining the server if any issue/concerns related to tempDB occurs.

 
UPDATED:  September 4, 2017