How-To : réduire l’enveloppe de tempdb

Jeudi, juillet 7, 2011
By David Baffaleuf in SQL Server (dbaffaleuf@capdata-osmozium.com) [70 article(s)]

Pour rééquilibrer un peu le discours global des articles, dans lesquels on essaie d’aborder des sujets très peu étayés sur le net, et qui du coup peuvent paraître parfois obscurs à celui qui cherche juste comment  réduire son journal de transactions où changer son compte de service, nous allons partir sur une autre série de How-To sur des actions basiques, avec des démos sous la forme de petites vidéos. Et ce pour deux raisons principales:

- C’est plus pratique pour nous ;-) . Il m’est arrivé de passer plusieurs semaines sur un seul article donc ça ne va pas faire de mal de parler de choses communes, et en plus s’il y a une démo à l’appui…
- Ça parle à plus de gens. Je suis conscient qu’on ne doit pas être très nombreux à s’intéresser aux schedulers non-préemptifs et aux IO completion ports (re- ;-) ).

Ces articles seront taggés ‘howto‘ dans le nuage de tags, vous pourrez donc les retrouver tous facilement. Pour ce premier, on va parler d’un sujet qui revient de temps en temps dans les forums, à savoir comment faire pour réduire la taille de tempdb sur SQL 2005 et SQL 2008.

Démo sur SQL 2005:

Get the Flash Player to see this player.

Sur SQL Server 2005:

Parce que ça a l’air trivial comme ça, en théorie. Vous vous dites, trop facile je fais un dbcc shrinkfile du ou des fichiers de données de tempdb. Ou alors je fais un ALTER DATABASE tempdb MODIFY FILE (NAME=…, SIZE=…) et je redémarre.

Voyons voir:

select serverproperty('productversion')
-- Taille initiale
SELECT size/128 FROM master.sys.master_files WHERE file_id=1 and database_id=2 
 -- Taille courante
select size/128 from tempdb.sys.database_files WHERE file_id=1
---------------------------
9.00.4035.00

---------------------------
1699

---------------------------
1699
alter database tempdb modify file(name='tempdev',size=500MB)

Msg 5039, Niveau 16, État 1, Ligne 2
Échec de MODIFY FILE. La taille spécifiée est inférieure à la taille en cours.

Dans cette version de SQL Server, la base tempdb est considérée comme les autres bases: on ne peut pas réduire l’enveloppe à une valeur inférieure à la taille en cours. Quant au DBCC SHRINKFILE / SHRINKDATABASE, il ne va fonctionner que sur une base sans activité, or sur une instance en production, il y a toujours de l’activité dans tempdb: tris, agrégats, tables temporaires, version store, etc… Toute cette activité va le plus souvent empêcher DBCC SHRINKFILE de réduire l’enveloppe à la taille désirée:

select name,size/128 from master.sys.master_files where database_id=2 and file_id=1
GO

-----------------
1699

dbcc shrinkfile('tempdev',500)
GO

DbId	FileId	CurrentSize	MinimumSize	UsedPages	EstimatedPages
------- ------- --------------  --------------- --------------  --------------
2	1	200200	         1024	         200192	         200192

Les tailles sont exprimées en nombre de pages. Taille en Mb = 200200/128 = 1564 Mb, donc on n’a pas récupéré grand chose. Vous pouvez tenter le shrinkfile avec moins d’activité si vous ne souhaitez pas une interruption de service, mais si ça ne fonctionne pas, il n’y qu’une seule façon de régler le problème: il faut redémarrer l’instance avec la configuration minimale:

Démarrer SQL Server manuellement dans un prompt DOS en utilisant les options -f -c:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlservr -c -f
2011-07-06 07:35:12.17 Server      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
        Nov 24 2008 13:01:59
        Copyright (c) 1988-2005 Microsoft Corporation
        Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

2011-07-06 07:35:12.18 Server      (c) 2005 Microsoft Corporation.
2011-07-06 07:35:12.18 Server      All rights reserved.
2011-07-06 07:35:12.18 Server      Server process ID is 4076.
2011-07-06 07:35:12.18 Server      Authentication mode is MIXED.
2011-07-06 07:35:12.19 Server      Logging SQL Server messages in file
                                    'V:\DBA2\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2011-07-06 07:35:12.19 Server      This instance of SQL Server last reported using a process
                                  ID of 3496 at 06/07/2011 07:34:18 (local) 06/07/2011 05:34:18
                                  (UTC). This is an informational message only;
 no user action is required.
2011-07-06 07:35:12.19 Server      Registry startup parameters:
2011-07-06 07:35:12.19 Server            -d V:\DBA2\MSSQL.1\MSSQL\DATA\master.mdf
2011-07-06 07:35:12.20 Server            -e V:\DBA2\MSSQL.1\MSSQL\LOG\ERRORLOG
2011-07-06 07:35:12.20 Server            -l V:\DBA2\MSSQL.1\MSSQL\DATA\mastlog.ldf
2011-07-06 07:35:12.20 Server      Command Line Startup Parameters:
2011-07-06 07:35:12.20 Server            -c
2011-07-06 07:35:12.20 Server            -f
2011-07-06 07:35:12.26 Server      Warning: The server instance was started using minimal
                                   configuration startup option (-f). Starting an instance
                                   of SQL Server with minimal configuration places the server
                                   in single-user mode automatically.  After the server has been
                                   started with minimal configuration, you should change the appropriate
                                   server option value or values, stop, and then restart the server.
2011-07-06 07:35:12.27 Serveur     SQL Server is starting at normal priority base (=7)...
2011-07-06 07:35:12.27 Serveur     Detected 2 CPUs. This is an informational message; no user action is required.
2011-07-06 07:35:12.94 Serveur     Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node...
2011-07-06 07:35:12.96 Serveur     Support for distributed transactions was not enabled for this instance of the Database Engine because it was started using the minimal
                                  configuration option.  
2011-07-06 07:35:12.97 spid5s      Starting up database 'master'.
2011-07-06 07:35:13.11 spid5s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2011-07-06 07:35:13.16 spid5s      Server started with '-f' option. Auditing will not be started. This is an informational message only; no user action is required.
2011-07-06 07:35:13.18 spid5s      Starting up database 'mssqlsystemresource'.
2011-07-06 07:35:13.20 spid5s      The resource database build version is 9.00.4035. This is an informational message only. No user action is required.
2011-07-06 07:35:13.40 spid6s      Starting up database 'model'.
2011-07-06 07:35:13.40 spid5s      Server name is 'DBA2'. This is an informational message only. No user action is required.
(...)

-f indique à SQL Server de démarrer avec les options minimales et en mode mono utilisateur (pas besoin d’utiliser le mode -m). Dans ce mode, tempdb reprend la taille initiale de la base model (2Mb), donc on va pouvoir faire passer un ALTER DATABASE MODIFY FILE.
-c indique que SQL Server ne démarre pas en tant que service

A partir de là on se connecte dans une seconde fenêtre DOS:

DOS>sqlcmd -E
1> select size/128 from tempdb.sys.database_files WHERE file_id=1
2> go

-----------
          2

1> alter database tempdb modify file(name='tempdev',size=500MB)
2> go
1> exit

En on redémarre via SQL Configuration Manager.

Sur SQL Server 2008:

La bonne nouvelle, c’est qu’à partir de SQL Server 2008, on n’a plus besoin de redémarrer en mode minimal, un ALTER DATABASE MODIFY FILE fonctionne avec des tailles inférieures à la taille en cours (uniquement sur tempdb):

select serverproperty('productversion')
-- Taille initiale
SELECT size/128 FROM master.sys.master_files WHERE file_id=1 and database_id=2
 -- Taille courante
select size/128 from tempdb.sys.database_files WHERE file_id=1

-------------------
10.0.2757.0

-------------------
1564

-------------------
1564

alter database tempdb modify file(name='tempdev',size=500MB)

Command(s) completed successfully.

Suivi d’un arrêt / redémarrage de l’instance avec SQL Configuration Manager.

A suivre un autre how-to SQL Server. A+

David B.

Liens:

http://support.microsoft.com/kb/307487/en-us

9.00.4035.00

Continuez votre lecture sur le blog :




Cliquer pour partager cet article sur Viadeo
Cliquer sur "CAPTURER" pour sauvegarder cet article dans Evernote Clip to Evernote

Tags: ,

6 Responses to “How-To : réduire l’enveloppe de tempdb”

  1. GDE

    Super article David !
    Clair et pratique.
    Et pour une fois j’ai tout compris !

    #15221
  2. Ah il est de retour le glouton des bois, la frayeur des restaurateurs parisiens !! Merci pour ton commentaire, dès que je reviens sur Paris on se fait une flamenkuche à volonté !!

    A+ !!!!

    #15246
  3. juvamine

    J’ai tout de même une interrogation, la tempdb ne va t elle pas reprendre du poids rapidement ?

    2è question : Un redémmarage du service sql n’est il pas censé réduire la tempdb à sa taille initiale ?

    Merci de vos réponses
    juvamine

    #17285
  4. Hello Juvamine,

    1) Souvent, on choisit de réduire la taille de tempdb après une période d’observation, quand on constate qu’elle a été surdimensionnée par exemple, et qu’on n’utilisera jamais la volumétrie telle qu’elle a été allouée, parce qu’on a des besoins plus modestes. Il ne faut pas le faire systématiquement, tu as raison, ça correspond à un besoin précis.

    2) Justement l’idée c’est de modifier la taille initiale, dans l’exemple de 1,7Gb pour la réduire à 500Mb. Au redémarrage suivant, la base repart avec une taille de 500Mb.

    Merci pour tes commentaires,

    David B.

    #17292
  5. juvamine

    Alors je continue.
    J’ai un cas (qui me surprend), où en moins d’1h, ma tempdb passe de 8MO à 11GO (et je ne me suis pas trompé d’unité).
    A priori elle reste à 11GO au final.
    Il faut que j’étudie le pourquoi du comment grace à cet article notamment ? http://blog.capdata.fr/index.php/sql-server-sessions-consommatrices-dans-tempdb/

    En tout cas le blog me semble sympa, c’est juste dommage de ne pas pouvoir créer une flux exclusivement SQL Server. Amicalement,
    juva

    #17344
  6. Exact, tu peux récupérer les traitements qui remplissent tempdb en utilisant les scripts fournis dans l’article. Après si 11Gb ce n’est pas gênant du point de vue volumétrie et que finalement la consommation est justifiée, tu peux affecter 11gb comme taille initiale et à chaque redémarrage, tempdb repartira sur 11gb. Attention cependant, il faut penser à activer l’initialisation instantanée pour réduire le temps de création du fichier de tempdb au démarrage (ça ferait bien un autre mini howto, ça !)

    A+

    #17347

Leave a Reply