Discussion :

[awatif]

lorsque je lance une requete de suppression dans l'analyseur de requétes
j'ai le message suivant:

Impossible d'allouer de l'espace pour l'objet '(SYSTEM table id: -940166310)' dans la base de données 'TEMPDB' parce que le groupe de fichiers 'DEFAULT' est plein.

que faire

[Youssef TAZI SIDQUI]

Bonjour,
Vérifie si l'espace disque est suffisant.

[SQLpro]

Cela signifie que les fichiers sous jacent à la base de données tempdb de SQL Server sont pleins. Cela peut arriver si :
- le disque contenant les fichiers est saturé
- la taille des fichiers de la base tempdb a été fixé à une valeur précise sans possibilité de s'agrandir.

A +

[Promesses]

Cela signifie que les fichiers sous jacent à la base de données tempdb de SQL Server sont pleins. Cela peut arriver si :
- le disque contenant les fichiers est saturé
- la taille des fichiers de la base tempdb a été fixé à une valeur précise sans possibilité de s'agrandir.

A +

Bonjour SQLpro,
il s'avère que j'ai à peu près le même probleme.

Code :

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

Impossible d'allouer une nouvelle page pour la base de données 'TEMPDB'. Plus de pages disponibles dans le groupe de fichiers DEFAULT. Libérez de l'espace en supprimant des objets, en ajoutant des fichiers ou en autorisant la croissance des fichiers.

Sur mon serveur SQL Server, j'éxecute une requete une requete dans l'analyseur de requete sur un serveur distant, et j'obtiens ce message d'erreur.

J'ai tout vérifié, j'ai plein de places sur le disque (50Go), la base tempdb est en croissance automatique.

Alors, pour tester j'ai retiré un champs dans la requête, j'ai donc eu le message que cette personne a eu.
Enfin pour continuer mes tests, j'en ai retiré deux puis trois, et la ma requete est passé.
Ca semble typiquement des symptomes de manque de place, mais tout mes paramêtres sont bons pour qu'elle puisse augmenter comme elle veut cette tempdb.

La requete éxecutée ramène 1100211 lignes.

Vois tu autre chose à faire ?

Sinon, un peu plus d'informations:
après avoir fait

Code :

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

sp_helpdb 'tempdb'

j'obtiens ces infos:

Code :

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

tempdb	  12026.25 MB	sa	2	août 16 200	Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=French_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics	80

Code :

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

1
2
tempdev                                                                                                                         	1	f:\databases\system\tempdb.mdf                                                                                                                                                                                                                                  	PRIMARY	12269568 KB	Unlimited	50%	data only
templog                                                                                                                         	2	f:\databases\system\templog.mdf                                                                                                                                                                                                                                 	NULL	45312 KB	Unlimited	30%	log only

Il y a 60 Go de libre sur le disque.

Voici le select qui est fait:

Code :

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

1
2
3
4
5
        'Post-paid' as Type_Produit,
	convert(char(10), DateTime, 103) as date_appel,
	convert( char(5), DateTime, 108) as heure_appel,	
	convert(char(10),Variable7) Num_dossier,
	convert(char(5),Variable3) as parc

En mettant en commentaire les deux derniers champs, la requête s'éxecute bien. (me ramene donc 1100211 lignes)
Sinon, la requete me ramene des lignes aussi si je prend exactement que les 11072 première lignes pas une de plus.

j'ai tout dit

[ZERS]

Ouch !
12Go pour une tempDb, ca commence à faire !!!!!!

Est-ce que tu as eu ces 12Go à la suite d'une unique requête ou bien n'as-tu jamais vider les logs, fait un schrink de la base ???

Bref, voici le petit code habituel (à faire après un back-up de tes bases, hors tempDB), à lancer sous l'analyseur de requete, et automatisable via un batch ou procédure stockée:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
use mabase -- on peut repeter ces commandes autant de fois qu'on a des bases users sur le serveur
go
checkpoint
go
use master
go
dump transaction mabase with truncate_only
go
DBBC SCHRINKDB(mabase)
go
DBBC SCHRINKDB(tempdb)
go

Après cela les tailles de tempdDB et de/des mabase devraient être moins grosses. Ca bousille un peu les perfs mais ca pourrait résoudre vos soucis respectifs

[rudib]

Salut à tous,

Un détail pour info : tempdb est par nature en "truncate on checkpoint", aussi connu sous le doux nom de recovery mode simple. En d'autres termes, son journal de transaction n'a pas besoin / ne doit pas être sauvegardé.

[ZERS]

Salut à tous,

Un détail pour info : tempdb est par nature en "truncate on checkpoint", aussi connu sous le doux nom de recovery mode simple. En d'autres termes, son journal de transaction n'a pas besoin / ne doit pas être sauvegardé.

Bien pour cela que j'ai précisé à nos amis de sauvegarder toutes leurs bases, mais pas TempDB

[Promesses]

Merci Zers pour ta réponse.
Malheureusement, cela ne donne rien.

J'exécute cette requete dans l'analyseur de requête, je n'ai pas besoin de faire de l'espace sur une base données quelconque, si ?
(mis à part tempdb)

Ce n'est pas possible que cela soit la tempdb distante ?

[rudib]

Bonjour,

Peux-tu poster le code complet de ton SELECT pour voir pourquoi il sollicite tempdb ?

[ZERS]

Bonjour,

Peux-tu poster le code complet de ton SELECT pour voir pourquoi il sollicite tempdb ?

+1

[Promesses]

Bonjour,

Peux-tu poster le code complet de ton SELECT pour voir pourquoi il sollicite tempdb ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT DISTINCT
	'Post-paid' as Type_Produit,
	convert(char(10), DateTime, 103) as date_appel,
	convert( char(5), DateTime, 108) as heure_appel,	
	convert(char(10),Variable7) Num_dossier,
	convert(char(5),Variable3) as parc
FROM 
	t_Route_Call_Detail
WHERE 
	DateTime between '17/07/2006' AND DATEADD(ss, - 1,CONVERT(datetime,CONVERT(varchar(10), GETDATE(), 103))) AND 
	DialedNumberString in ('890710129','890710133','890710134','890710137') AND 
	Variable3 not in ('10','15') AND 
	Variable3 is not null AND 
	Variable7 is not null

Je ne sais pas comment ca peut aider...
Si je retire les deux derniers champs, la requete s'execute bien.

[ZERS]

Alors là !

Je sais pas si Rudi va trouver ce qui cloche mais moi je vois pas. Je ne connais pas assez SQL server pour voir en quoi tempDB ici est sollcitée ? Rudi, eine kleine idee?

Ceci dit, perso je déconseille fortement d'utiliser des mots reservés comme noms de colonne (genre datetime dans ton code)

M'enfin je ne pense pas que ca vienne de là...

As-tu le droit de faire une copie de ta table dans une autre, en changeant le nom de cette colonne ?? Si oui, essaie mais je doute que cela vienne de là...

[ZERS]

Merci Zers pour ta réponse.
Malheureusement, cela ne donne rien.

Est-ce qu'au moins cela a réduit la taille de tempDb ?

[rudib]

Bon, la requête n'a pas l'air d'être optimale, mais cela paraît tout de même étrange qu'elle demande tant de place dans tempdb.

Ce que tu peux essayer de faire :
- jeter un oeil sur le plan d'exécution de cette requête pour voir comment elle se comporte (CTRL+L), et afficher les I/O si tu réussis à la faire tourner jusqu'au bout : SET STATISTICS IO ON, avant la requête.
- à l'aide du profiler, tracer les demandes d'augmentation de taille de fichier de tempdb, en utilisant les événements Database / Data File Auto Grow et Database / Log File Auto Grow, avec les colonnes FileName et Integer Data.
- selon ton sp_helpdb, le fichier de données de tempdb est paramétré pour grandir de 50%, peux-tu essayer de mettre une valeur fixe, comme 500 mb, pour voir s'il arrive au bout ?

[ZERS]

Rudi,

pour mon information : quelles sont les commandes de cette requête qui sollicitent tempDB ??

[rudib]

Je ne sais pas trop, difficile à dire sans le plan d'exécution. Ce qu'on peut déjà dire, c'est que selon la clause WHERE, il fait très probablement en scan de la table. Il faudrait voir si ça change qqch d'enlever les CONVERT().
Tiens, est-on en SQL Server 2005 avec une isolation en row versioning (read committed avec l'option READ_COMMITTED_SNAPSHOT à ON, ou isolation snapshot) ? Si c'est le cas, tempdb est très sollicité pour stocker les versions des lignes.

[voyageur]

Je remet ce topic à jour car j'ai le meme probleme que toi : une requete select qui sature la TempDb.
En regardant le plan d'execution il y a un trés gros hash match( inner join logique) et d'aprés la doc microsoft le hashage est consommateur de TempDB, je trouve cela etrange tout de meme car cette meme requete executé quelques jours auparavant allait jusqu'au bout sans probleme.

Could not allocate space for object '(SYSTEM table id: -210167308)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.

[SQLpro]

Avez vous des index sous les colonnes jointes ? Index primaire et index de FK ???

Depuis quand n'avez vous pas fait de défrag ?

Que dit la commande DBCC SHOWCONTIG lancé sur les index sous jacents à la joiture ???

Postez la réponse....

A +

[voyageur]

Avez vous des index sous les colonnes jointes ? Index primaire et index de FK ???

Depuis quand n'avez vous pas fait de défrag ?

Que dit la commande DBCC SHOWCONTIG lancé sur les index sous jacents à la joiture ???

Postez la réponse....

A +

La jointure est faite entre deux PK. La requete fait egalement un filtre sur une colonne indexée, et il y a aussi une sous requete avec selection sur PK.
C'est cette requete précisement qui sature la tempdb:

Code :

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

1
2
3
4
5
6
7
 
INSERT tabledestination
select champ1,champ2, ......,champ30
from table1 t1, table2 t2
inner join t1.PK=t2.PK
and t1.date > '20051231' -- c'est un string
and t2.PK not in ( select PK from tabledestination)

La défragmentation est faite une fois toute les semaines.
Voici les résultats obtenues sur les deux tables de la jointure :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
Table: 'table1' (1659152956); index ID: 0, database ID: 155
TABLE level scan performed.
- Pages Scanned................................: 687962
- Extents Scanned..............................: 86010
- Extent Switches..............................: 86009
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.98% [85996:86010]
- Extent Scan Fragmentation ...................: 56.13%
- Avg. Bytes Free per Page.....................: 336.2
- Avg. Page Density (full).....................: 95.85%
DBCC SHOWCONTIG scanning 'table1' table...
 
Table: 'table1' (1659152956); index ID: 7, database ID: 155
LEAF level scan performed.
- Pages Scanned................................: 115677
- Extents Scanned..............................: 14512
- Extent Switches..............................: 14511
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.64% [14460:14512]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.18%
- Avg. Bytes Free per Page.....................: 792.1
- Avg. Page Density (full).....................: 90.21%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Table: 'table2' (16459152966); index ID: 0, database ID: 155
TABLE level scan performed.
- Pages Scanned................................: 687962
- Extents Scanned..............................: 86010
- Extent Switches..............................: 86009
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.98% [85996:86010]
- Extent Scan Fragmentation ...................: 56.13%
- Avg. Bytes Free per Page.....................: 336.2
- Avg. Page Density (full).....................: 95.85%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

En regardant le nombre de DiskIO que fait la requete j'ai relevé plus de 5000 000 de DiskIO juste avant le plantage.
Apparemment la quantité de données renvoyée par la requete est trop importante d'autant plus que la taille maximale de tempdb est de 10 GO.

[SQLpro]

Correct.

Postez la requête.

A +