Salut à tous !
Aujourd'hui, on va parler de "tempdb", une base de données système dans Microsoft SQL Server qui stocke des données temporaires telles que des tables temporaires et des variables de table, des plans d'exécution de requêtes et des résultats de requêtes intermédiaires. Cette base de données est essentielle au bon fonctionnement de SQL Server, et il est important de contrôler sa taille. (ça fait chelou avec les mots en français plutôt qu'en anglais mais vous comprenez, "temporary tables, table variable, query execution plan, query intermediary results, etc etc")
Le problème que l'on va traiter concerne la croissance excessive de la base de données "tempdb".
Pour déterminer la cause de ce problème, la première étape consiste à lancer SQL Server Management Studio et à accéder à la base de données "tempdb".
On fait un clic droit sur la "tempdb" et ensuite => Tasks => Shrink => Files => Ne réduisez rien, regardez simplement l'espace disponible. Si l'espace disponible est élevé, cela indique qu'une requête mal optimisée est en cours d'exécution et que "tempdb" ne libère pas l'espace disponible.
Si l'espace disponible est faible, accédez à la base de données "tempdb" et exécutez un rapport standard, "Top Transactions by Age", pour identifier si de nombreuses transactions sont ouvertes, ce qui pourrait provoquer la croissance de la base de données et la non-libération de l'espace.
Si cela ne renvoie rien, vous pouvez exécuter un script qui affiche tous les processus en cours d'exécution et la quantité d'espace qu'ils utilisent dans "tempdb".
Le script suivant devrait fonctionner :
select
t1.session_id
, t1.request_id
, task_alloc_GB = cast((t1.task_alloc_pages * 8./1024./1024.) as numeric(10,1))
, task_dealloc_GB = cast((t1.task_dealloc_pages * 8./1024./1024.) as numeric(10,1))
, host= case when t1.session_id <= 50 then 'SYS' else s1.host_name end
, s1.login_name
, s1.status
, s1.last_request_start_time
, s1.last_request_end_time
, s1.row_count
, s1.transaction_isolation_level
, query_text=
coalesce((SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1THEN LEN(CONVERT(nvarchar(max),text)) * 2ELSE statement_end_offset
END - t2.statement_start_offset)/2)
FROM sys.dm_exec_sql_text(t2.sql_handle)) , 'Not currently executing')
, query_plan=(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle))
from
(Select session_id, request_id
, task_alloc_pages=sum(internal_objects_alloc_page_count + user_objects_alloc_page_count)
, task_dealloc_pages = sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count)
from sys.dm_db_task_space_usage
group by session_id, request_id) as t1
left join sys.dm_exec_requests as t2 on
t1.session_id = t2.session_id
and t1.request_id = t2.request_id
left join sys.dm_exec_sessions as s1 on
t1.session_id=s1.session_id
where
t1.session_id > 50 -- ignore system unless you suspect there's a problem there
and t1.session_id <> @@SPID -- ignore this request itself
order by t1.task_alloc_pages DESC;
GO
Une fois que vous avez toutes ces informations, vous pouvez commencer à trouver la cause de ce problème de taille excessive et savoir exactement "qui" ou "quoi" utilise l'espace.
Pour corriger ce problème de manière temporaire, redémarrez simplement SQL Server, ce qui effacera le fichier journal de la "tempdb". Ensuite vous pouvez modifier les paramètres de l'auto-growth et afin de mettre une taille fixe ou désactiver ce settings. Logiquement la query qui pose problème n'arrivera plus à s'exécuter et on devrait trouver le coupable.
En suivant ces étapes, vous pouvez facilement identifier la cause de la croissance excessive de "tempdb" et résoudre le problème.
J'espère que cet article vous a été utile. À bientôt !