Limiter la croissance de la base de données temporaire SQL

Question

Je suis confronté à un problème sérieux dans mon serveur de production où la base de données temporaire augmente de façon exponentielle. Existe-t-il un moyen de récupérer l'espace de la BD temporaire sans redémarrer le service SQL ?

Cheers Kannan.

Answer 1

Je ne tiendrais pas compte des messages vous conseillant de changer le modèle de récupération ou de limiter la taille de tempDB( !).

Vous devez trouver la cause réelle de la croissance.

Si la trace par défaut est activée (elle l'est par défaut, dès la sortie de l'emballage), vous pouvez rétrospectivement trouver ce qui a causé la croissance en exécutant ceci :

--check if default trace is enabled
if  exists (select 1 from sys.configurations where configuration_id = 1568)
BEGIN

declare @defaultTraceFilepath nvarchar(256)

--get the current trace rollover file
select @defaultTraceFilepath = CONVERT(varchar(256), value) from ::fn_trace_getinfo(0)
where property = 2

SELECT ntusername,loginname, objectname, e.category_id, textdata, starttime,spid,hostname, eventclass,databasename, e.name 
FROM ::fn_trace_gettable(@defaultTraceFilepath,0)
      inner join sys.trace_events e
            on eventclass = trace_event_id
       INNER JOIN sys.trace_categories AS cat
            ON e.category_id = cat.category_id
where 
      databasename = 'tempDB' and 
      cat.category_id = 2 and --database category
      e.trace_event_id in (92,93) --db file growth

END

Sinon, vous pouvez lancer une trace SQL Profiler pour capturer ces événements. Activez la capture des événements de croissance automatique, des avertissements de tri et des avertissements de jointure et recherchez les jointures croisées, les jointures de hachage ou les conditions de jointure manquantes.

Le serveur SQL expose un moyen d'identifier les allocations d'espace tempDB par les requêtes en cours d'exécution, en utilisant des DMV :

-- This DMV query shows currently executing tasks and tempdb space usage
-- Once you have isolated the task(s) that are generating lots 
-- of internal object allocations, 
-- you can find out which TSQL statement and its query plan 
-- for detailed analysis
select top 10
  t1.session_id, 
  t1.request_id, 
  t1.task_alloc,
  t1.task_dealloc,  
  (SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
          (CASE WHEN statement_end_offset = -1 
              THEN LEN(CONVERT(nvarchar(max),text)) * 2 
                   ELSE statement_end_offset 
              END - t2.statement_start_offset)/2)
     FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
 (SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle)) as query_plan
from      (Select session_id, request_id,
sum(internal_objects_alloc_page_count +   user_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count) as task_dealloc
       from sys.dm_db_task_space_usage 
       group by session_id, request_id) as t1, 
       sys.dm_exec_requests as t2
where t1.session_id = t2.session_id and 
(t1.request_id = t2.request_id) and 
      t1.session_id > 50
order by t1.task_alloc DESC

( Réf. .)

Answer 2

Vous pouvez utiliser DBCC SHRINKFILE pour réduire les fichiers tempdb et récupérer de l'espace.

DBCC SHRINKFILE ('tempdev', 1) DBCC SHRINKFILE ('templog', 1)

Les noms de fichiers peuvent être trouvés dans la table sysfiles.

Vous devez encore découvrir la cause première, mais cela peut vous donner un peu de répit jusqu'à ce que vous y parveniez. La quantité d'espace que vous récupérerez dépendra de l'utilisation et d'autres facteurs.

Aussi :

Comment réduire la base de données tempdb dans SQL Server

http://support.microsoft.com/kb/307487

Answer 3

En mode SIMPLE, le journal de la base de données tempdb est constamment tronqué, et il ne peut jamais être sauvegardé. Vérifiez donc qu'elle est en mode simple