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.
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
Continuez votre lecture sur le blog :
- Pourquoi il faut sauvegarder les bases systèmes (David Baffaleuf) [SQL Server]
- How-To: réduire la taille du journal de transactions sur disque (David Baffaleuf) [SQL Server]
- Sessions consommatrices dans tempdb (David Baffaleuf) [SQL ServerVintage]
- Le chiffrement et SQL Server – Episode 2 : Mise en oeuvre de TDE (Capdata team) [SQL Server]
- Production SQL Server : banalisation des instances (Benjamin VESAN) [SQL Server]
Super article David !
Clair et pratique.
Et pour une fois j’ai tout compris !
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+ !!!!
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
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.
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
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+