Discussion :

[mb10]

Bonjour,

Suite à une opération exceptionnelle, la tempDB d'une base à augmenter.
Je voulais savoir s'il était possible de réduire la taille de la TempDB sans risque en faisant un shrink comme sur une base de production.

Cordialement.

[dbaffaleuf]

Est-ce le (les) fichier(s) de données de tempdb qui ont augmenté, ou est-ce le journal ?

- S'il s'agit du journal, un dbcc shrinkfile devrait suffire.
- S'il s'agit des fichiers de données c'est plus compliqué, parce qu'il y a toujours plus ou moins d'activité dans la base, et des extents alloués en fin de fichier qui empêchent sa réduction sur disque. (http://support.microsoft.com/kb/307487). Le plus simple est de faire un stop/start du service pendant les heures de non utilisation de l'instance.

Autre question: que renvoient:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
select name, size/128,max_size from tempdb.sys.database_files 
GO
--
dbcc sqlperf(logspace)
GO

[mb10]

C'est le fichier de données de la TempDB qui a augmenté.

Résultat requêtes :

name | (No column name) | max_size
tempdata | 58530 | -1
templog | 459 | -1


Database Name | Log Size (MB) | Log Space Used (%) | Status
tempdb | 459,1172 | 67,67967 | 0

Cordialement.

[dbaffaleuf]

OK. Tenter un shrinkfile avec de l'activité me paraît compliqué et risqué : le shrink en déplaçant des pages verrouille des plages d'offsets dans le fichier MDF, ralentit toute l'activité IO type checkpoint, lazy writer, etc... Quant à un dbcc shrinkfile truncateonly, il y a toujours un extent en fin de fichier qui bloque la réduction. Il faut trouver une plage de non utilisation pour redémarrer.

Autre chose, la taille initiale de tempdb:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select size/128 from master.sys.sysaltfiles where fileid=1 and dbid=2

merci,

[mb10]

résultat de la requête :

(No column name)
58530

Cordialement.

[dbaffaleuf]

Ah donc quand tu affiches les propriétés de la base tempdb, -> Fichiers (ou Files) la taille initiale de tempdev est de 58Gb ?

[mb10]

Oui, c'est pour ça que je veux la réduire.
Je voudrais la réduire car il n'y a aucune raison pour qu'elle reste à cette taille.

Cordialement.

[elsuket]

Bonjour,

Si vous savez quelles opérations on conduit le fichier de données de TempDB à grossir, et que cette opération n'est plus exécutée, faites donc.

Si ce n'est pas le cas, alors il est probable que tôt ou tard, cet espace sera repris par SQL Server, avec tout ce que cela implique : ralentissement global comme vous l'a explique dbaffaleuf, fragmentation du fichier, ...

Que vous retourne la première requête de ce billet, exécutée dans le contexte de la base de données TempDB ?

@++

[dbaffaleuf]

Oui, c'est pour ça que je veux la réduire.
Je voudrais la réduire car il n'y a aucune raison pour qu'elle reste à cette taille.

Cordialement.

Dans ce cas c'est différent, il faut le faire en single user avec les paramètres minimums:

1) Stopper le service MSSQL dans sqlservermanager.msc
2) Ouvrir une fenêtre DOS, se placer sous le répertoire des binaires de MSSQL.
3) sqlservr -c -f -m
4) Laisser la fenêtre ouverte, ne pas la réduire sur la barre des tâches (ça ferait swapper le processus)
5) dans une autre fenêtre DOS, se connecter sous SQLCMD avec un compte sysadmin et faire un ALTER DATABASE tempdb MODIFY FILE, par exemple une réduction à 20GB:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
DOS>sqlcmd -E
1> alter database tempdb modify file(name='tempdev',size=20GB)
2> go
1> exit

6) se déconnecter de la session
7) faire un Ctrl-C dans la fenêtre DOS d'exécution de sqlservr
8) redémarrer le service via sqlservermanager.msc

Vérifier avant que l'initialisation instantanée est active pour ton instance ie que le compte de service possède le privilège SeManageVolumePrivilege (effectuer des tâches de maintenance de volume).

[elsuket]

Il me semble qui suffit d'exécuter :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
ALTER DATABASE TEMPDB MODIFY FILE
(NAME='tempdev', SIZE=20GB)
GO

Puis de redémarrer le service. Non ? (j'ai bien vu que ce n'est pas la démarche proposée par la page que vous avez référencée)

@++

[SQLpro]

Oui, mais là tu la fixes à 20 Go de manière définitive !!!!!

A +

[dbaffaleuf]

non ce n'est pas le pb mais SQL Server refusera de mettre une taille inférieure à la taille initiale:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
select size/128 from tempdb.sys.database_files where file_id <> 2 
------
20
 
ALTER DATABASE TEMPDB MODIFY FILE
(NAME='tempdev', SIZE=10MB)
 
Msg*5039, Niveau*16, État*1, Ligne*1
Échec de MODIFY FILE. La taille spécifiée est inférieure à la taille en cours.

sqlservr -c -f permet de démarrer avec une tempdb minimale (taille de model)

[dbaffaleuf]

J'ai édité la réponse #9 pour plus de clarté, la connexion sous DOS se fait avec SQLCMD

[mikedavem]

Bonsoir,

Suite à une opération exceptionnelle, la tempDB d'une base à augmenter.

Que fais cette opération exceptionnelle ?

Que donne à tout hasard la commande suivante sur vos bases ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DBCC CHECKDB('<maBase>') WITH ESTIMATEONLY

++

[mb10]

Bonjour,

Voici le résultat de la requête DBCC CHECKDB('tempdb') WITH ESTIMATEONLY :

1

Cordialement.

[mikedavem]

En fait il faut surtout récupérer la valeur max que vous pouvez trouver pour l'ensemble de vos bases. Par exemple :

Database A - 5
Database B - 890
Database C - 1020
Database D - 2

Valeur Max : 1020

++

[mb10]

Rebonjour,

Valeur max récupérée pour la requête DBCC CHECKDB('<maBase>') WITH ESTIMATEONLY:

Estimated TEMPDB space needed for CHECKALLOC (KB)
151579

Estimated TEMPDB space needed for CHECKTABLES (KB)
17492

Cordialement.

[dbaffaleuf]

Donc si <mabase> est la plus grosse base sur ton instance, il faudra 150Mb au maximum dans tempdb pour faire passer un DBCC CHECKDB. De ce point de vue, 20GB est donc largement suffisant.

[mikedavem]

Donc si <mabase> est la plus grosse base sur ton instance, il faudra 150Mb au maximum dans tempdb pour faire passer un DBCC CHECKDB. De ce point de vue, 20GB est donc largement suffisant.

CQFD

++

[mb10]

Bonjour,

Est ce qu'exécuter la requête suivante suffit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
ALTER DATABASE TEMPDB MODIFY FILE
(NAME='tempdev', SIZE=20GB)
GO

alors que la base est en production ?

Cordialement.