I had a problem the other day with the SQL temporary database files taking up an excessive amount of SQL resources with their high I/O, I found it was getting to a point where SQL was creating blocking PIDs that would cause database service interruptions.
The files in question in SQL terminology were known as the TempDB files.
I also observed in the SQL log that it was actually pausing I/O on rare occasions while it was waiting for this file to finish writing or growing, usually, this problem occurs if you have a long running transaction in SQL that causes a lot of data to be written to these temporary files, this action usually occurs most when you have a database in “simple” mode and then you get a long running transaction.
In this particular example, I also noticed incorrect file system management, where the name of the previous database was storing these TempDB files - ironically, the name of this folder had the database name.
The next course of action was to create an SQL query that would query what files were used by what database (this includes system databases)
SELECT
db.name AS [Database Name],
mf.name AS [Logical Name],
mf.type_desc AS [File Type],
mf.physical_name AS [Path],
CAST((mf.Size * 8) / 1024.0 AS DECIMAL(18, 1)) AS [Size (MB)],
CAST((FILEPROPERTY(mf.name, 'SpaceUsed') * 8) / 1024.0 AS DECIMAL(18, 1)) AS [Used Space (MB)],
CAST((mf.Size * 8) / 1024.0 - (FILEPROPERTY(mf.name, 'SpaceUsed') * 8) / 1024.0 AS DECIMAL(18, 1)) AS [Free Space (MB)],
CAST((FILEPROPERTY(mf.name, 'SpaceUsed') * 8) / (mf.Size * 8.0) * 100 AS DECIMAL(18, 1)) AS [Space Used %],
'By ' + IIF(mf.is_percent_growth = 1,
CAST(mf.growth AS VARCHAR(10)) + '%',
CONVERT(VARCHAR(30), CAST((mf.growth * 8) / 1024.0 AS DECIMAL(18, 1))) + ' MB') AS [Autogrowth],
IIF(mf.max_size = 0, 'No growth',
IIF(mf.max_size = -1, 'Unlimited',
CAST((CAST(mf.max_size AS BIGINT) * 8) / 1024 AS VARCHAR(30)) + ' MB')) AS [Maximum Size],
db.recovery_model_desc AS [Recovery Model],
db.state_desc AS [Database State],
db.create_date AS [Creation Date]
FROM sys.master_files AS mf
INNER JOIN sys.databases AS db
ON db.database_id = mf.database_id
WHERE db.state_desc = 'ONLINE'
ORDER BY [Database Name], [File Type];
This will return the name of the database and the files as below:
That pointed me towards TempDB, so it was only been logical to write another query to give you a list of long run transactions:
SELECT
tsu.session_id,
s.login_name,
DB_NAME(s.database_id) as DatabaseName,
CAST((tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) * 8.0 / 1024 AS DECIMAL(10, 2)) AS [TempDB MB Used],
DATEDIFF(MINUTE, s.last_request_start_time, GETDATE()) as [Minutes Running],
s.host_name,
s.program_name,
r.command,
t.text as [SQL Text],
s.status,
s.cpu_time,
s.memory_usage,
r.wait_type,
r.wait_time,
r.blocking_session_id
FROM sys.dm_db_task_space_usage tsu
INNER JOIN sys.dm_exec_sessions s ON tsu.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON r.session_id = tsu.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE tsu.session_id <> @@SPID
AND (tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) > 0
ORDER BY [TempDB MB Used] DESC;
This will then provide a query with the long running transactions and the time they have been running and from what computer and application.