TempDB growing from SQL Server agent collection activity (4290144)

The SQL Server agent does not release the TempDB space and subsequently the tempdb space fills up.
Stopping and then restarting the FglAM allows the TempDB space to be reused.

CAUSE 1

The TempDB grows as designed by Microsoft. R&D recommends to limit the size of the TempDB.

CAUSE 2

Microsoft SQL Server Error logs are of a large size. The Error logs can be found here: http://support.microsoft.com/kb/966659

Note: Microsoft's KB above was retired but can still be accessed in Internet Archive.

CAUSE 3

A Foglight collection is causing major use of the TempDB

RESOLUTION 1

TempDB growth was found to be caused by internal Microsoft SQL Server database processes.

RESOLUTION 2

SQL Server monitoring may read the SQL Server Error Logs into tempdb tables and parse for errors and error codes. Large Error Logs will increase tempdb usage. Restart SQL Server and/or move or rename the existing error logs to reduce the tempdb usage.

* A workaround of disabling the SQL Server Errorlog collection may also be implemented as a temporary fix.

The following SQL Server Collections should be disabled:

• Error Log Check
• Error Log List
• SQL Agent Error Log
• SQL Server Error Log

1. Browse to: Database Dashboard | Select the affected agents | Agent Administration | Collection Frequencies
2. Highlight the each of the noted Collections and click [Edit].
3. Uncheck "Enable collection" - do so for all 4 noted above or the log collections will still trigger the issue.
4. Save changes. 

RESOLUTION 3

The following query can be executed on the monitored SQL Server to gauge the tempdb usage per app and per logon via Foglight monitoring:

SELECT DES.session_id AS [SESSION ID],
Db_name(DDSSU.database_id) AS [DATABASE Name],
host_name AS [System Name],
program_name AS [Program Name],
login_name AS [USER Name],
status,
( user_objects_alloc_page_count * 8 ) AS
[SPACE Allocated FOR USER Objects (in KB)],
( user_objects_dealloc_page_count * 8 ) AS
[SPACE Deallocated FOR USER Objects (in KB)],
( internal_objects_alloc_page_count * 8 ) AS
[SPACE Allocated FOR Internal Objects (in KB)],
( internal_objects_dealloc_page_count * 8 ) AS
[SPACE Deallocated FOR Internal Objects (in KB)],
cpu_time AS [CPU TIME (in milisec)],
total_scheduled_time AS
[Total Scheduled TIME (in milisec)],
total_elapsed_time AS
[Elapsed TIME (in milisec)],
( memory_usage * 8 ) AS [Memory USAGE (in KB)],
CASE is_user_process
WHEN 1 THEN 'user session'
WHEN 0 THEN 'system session'
END AS [SESSION Type],
row_count AS [ROW COUNT]
FROM tempdb.sys.dm_db_session_space_usage AS DDSSU
INNER JOIN sys.dm_exec_sessions AS DES
ON DDSSU.session_id = DES.session_id
ORDER BY [space allocated for internal objects (in kb)] DESC