developing a log analyzer tool and After queering it and generating reports Tempdb is getting full very quickly .
Depending on what your tool does when generating reports - creates lot of tempdb tables, spills data to tempdb when using sort operators, etc, I would suggest you to presize your tempdb.
You are better off optimizing the SQL that is generating the report. May be putting proper index/s will definitely help.
Its always a best practice to only query the data that you require.
Start by analyzing wait stats.
You can use sp_whoisactive to analyze whats going on your server instance.
Below query will help you to identify the sessions that use the tempdb heavily :
Lists the TempDB usage per each active session.
It helps identifying the sessions that use the tempdb heavily with internal objects.
When the internal objects usage is high, the session is probably using big hash tables or spooling in worktables. It could be a symptom of an inefficient plan or a missing index.
Shrinking a TempDB full of internal objects will probably have no effect, because the engine will not release the deallocated space.
;WITH task_space_usage AS (
-- SUM alloc/delloc pages
SUM(internal_objects_alloc_page_count) AS alloc_pages,
SUM(internal_objects_dealloc_page_count) AS dealloc_pages
FROM sys.dm_db_task_space_usage WITH (NOLOCK)
WHERE session_id <> @@SPID
GROUP BY session_id, request_id
TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
-- Extract statement from sql text
ERQ.statement_start_offset / 2,
CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
) AS [statement text],
FROM task_space_usage AS TSU
--- Changed from inner join to left outer join to return rows for sessions that aren't currently actively running queries.
left outer join sys.dm_exec_requests ERQ WITH (NOLOCK)
ON TSU.session_id = ERQ.session_id
AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESC
Is there any solution to clean the tempdb file in microsoft sql server 2008 on a timely basis other than restarting the server ?
Use it carefully on a production server !!
The only possible alternative to restarting the service, is running
DBCC FREESYSTEMCACHE('ALL')-- that will clear all cached objects, including not only internal objects, but also cached query plans.
DBCC FREESYSTEMCACHE ('tempdb') -- clears cache for tempdb
DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables') -- clears all the temp table and variables
Also, refer to Difference between FreeProcCache and FreeSystemCache