Pourquoi suis-je “Le fichier journal de base de données " base de données tempdb est pleine”

Laissez-nous avoir une table de paiements de 35 colonnes de la clé primaire (autoinc bigint) et 3 non mis en cluster, non unique indices (chacun sur une colonne int).

Entre les colonnes de la table, nous avons deux champs de type dateheure:

payment_date datetime NOT NULL
edit_date datetime NULL

La table a environ 1 200 000 lignes.
Seulement ~1000 de lignes ont edit_date colonne = null.
9000 de lignes ont edit_date pas nul et n'est pas égal à payment_date
D'autres ont edit_date=payment_date

Lorsque nous exécutons requête 1:

select top 1 *
from payments
where edit_date is not null and (payment_date=edit_date or payment_date<>edit_date)
order by payment_date desc

Pourquoi suis-je “Le fichier journal de base de données

serveur a besoin d'un couple de secondes pour le faire. Mais si nous courons requête 2:

select top 1 *
from payments
where edit_date is not null
order by payment_date desc

Pourquoi suis-je “Le fichier journal de base de données

l'exécution se termine avec Le fichier journal de base de données " base de données tempdb est pleine. Sauvegarder le journal des transactions de la base de données afin de libérer de l'espace du journal.

Si nous remplaçons * avec une certaine colonne, voir requête 3

select top 1 payment_date
from payments
where edit_date is not null
order by payment_date desc

Pourquoi suis-je “Le fichier journal de base de données

il termine dans un couple de secondes.

Où est la magie?

MODIFIER
J'ai modifié la requête de 1, de sorte qu'il fonctionne sur exactement le même nombre de lignes que la 2ème requête. Et encore, il retourne en une seconde, alors que la requête 2 remplit la base de données tempdb.

RÉPONSE
J'ai suivi les conseils pour ajouter un index, l'a fait pour les deux champs de date - tout a commencé un travail rapide, comme prévu. Cependant, la question était de savoir - pourquoi, dans cette situation exacte de sql server se comportent différemment sur des requêtes (requête 1 vs requête 2); je voulais comprendre la logique de l'optimisation du serveur. Je suis d'accord si les deux requêtes n'a utilisé la base de données tempdb de la même manière, mais ils n'ont pas....

À la fin il me marque que la réponse à la première, où j'ai vu le doivent-être les symptômes de mon problème et la première, ainsi, des pensées sur la façon d'éviter cela (c'est à dire indices)

Avez-vous songé à la suite de l'avis de la message d'erreur? Certaines requêtes nécessitera l'utilisation de la base de données tempdb, et si le fichier journal est plein, aucune autre transaction ne peut être effectuée.
Je n'ai pas..mais ce que je suis en train de le savoir est pourquoi presque similaire, les requêtes sont exécutées d'une manière complètement différente...et comment suis-je à la construction de requêtes pour éviter ce genre de behaivour..
N'utilisez pas de * pour sélectionner toutes les colonnes, pour commencer. Et ce journal des transactions.
avez-vous essayé ceci, comme vous le rencontrez 35*(90,000) documents générés???? select top 1 payment_date de paiements, où edit_date n'est pas null et payment_date=edit_date commande par payment_date desc
Les requêtes de 1,3 sont exécutés instantanément; seulement la 2ème requête manque de la taille de tempdb

OriginalL'auteur horgh | 2012-08-09

5

Cela se passe en cause certaines mesures dans un plan d'exécution peut déclencher écrit à tempdb en particulier certains sorts et joins impliquant beaucoup de données.

Puisque vous êtes le tri d'un tableau avec une charge de bateau de colonnes, SQL décide qu'il serait être fou pour effectuer le tri seul en temp db sans les données associées. Si il fait cela, il aurait besoin de faire un gazzilion inefficace recherches signet sur la table sous-jacente.

Suivre ces règles:
1. Essayez de sélectionner uniquement les données dont vous avez besoin
2. La taille de la base de données tempdb de manière appropriée, si vous avez besoin de faire un fou de requêtes de ce genre un gazzilion lignes, vous avez mieux à une taille appropriée tempdb
Mais pourquoi ne pas faire la même "fou" opération, lorsque j'essaie d'exécuter la première (voir la question) requête...si on compte le nombre de lignes résultant de la première et de la deuxième requêtes, leur presque égale (environ un million)...mais en prenant TOP 1 dans la 1ère marche bien, mais dans la 2ème, il échoue......Ce qui, dans la 2ème requête provoque journal d'utilisation?
les transactions cause du journal l'utilisation. La question que vous devriez poser est de savoir "quelles sont les causes de la base de données tempdb d'utilisation".
Désolé, je voulais dire exactement ce que...
vous pouvez le voir sur le plan, placez le pointeur sur la ligne grasse, son transfert de toutes les données dans la base de données tempdb, donc il est possible de trier (y compris toutes les colonnes dans la première et 1 colonne dans la deuxième) ... après il se déplace toutes les données de là, il sélectionne la première ligne. assurer que le plan est de la merde, mais si vous avez ajouté un index sur paymentdate ce serait ultra rapide dans les deux cas. sinon, vous pouvez à la main le code de sorte qu'il ne fait pas de données shuffle.

OriginalL'auteur Sam Saffron
4

Généralement, la base de données tempdb se remplit quand vous êtes faible sur l'espace disque, ou lorsque vous avez réglé une trop faible taille maximale de croissance de base de données.
Beaucoup de gens pensent que la base de données tempdb est utilisé uniquement pour les #des tables temporaires. Alors qu'en fait, vous pouvez facilement remplir la base de données tempdb sans jamais créer une seule table temp. Quelques autres scénarios qui peuvent causer de la base de données tempdb à remplir:
- tri nécessite plus de mémoire que ce qui a été alloué à SQL
  Le serveur sera forcé de faire son travail dans la base de données tempdb;
- si le tri nécessite plus d'espace que vous avez attribué à la base de données tempdb
  l'une des erreurs ci-dessus se produisent;
- DBCC CheckDB('base de données') va effectuer son travail dans la base de données tempdb -- sur
  les grandes bases de données, ce qui peut consommer un peu d'espace;
- DBCC DBREINDEX ou des commandes DBCC similaires à "Trier dans tempdb" option
  jeu aussi potentiellement remplir la base de données tempdb;
- grands jeux de résultats impliquant les syndicats, commande par /group by, cartésien
  les jointures, les jointures, les curseurs, les tables temporaires, les variables de table, et
  le hachage peut souvent besoin de l'aide de la base de données tempdb;
- toutes les transactions non validées et non annulée pouvez laisser
  les objets orphelins dans la base de données tempdb;
- utilisation d'un DSN ODBC avec l'option " créer stockée temporaire
  les procédures de jeu peut laisser des objets pour la vie de la
  connexion.
  
  UTILISER la base de données tempdb
  ALLER
```
    SELECT name 
        FROM tempdb..sysobjects 

    SELECT OBJECT_NAME(id), rowcnt 
        FROM tempdb..sysindexes 
        WHERE OBJECT_NAME(id) LIKE '#%' 
        ORDER BY rowcnt DESC
```
La hausse du nombre de lignes, les valeurs indiquent le plus grand des tables temporaires qui sont consommatrices d'espace.

Correctif à court terme
```
DBCC OPENTRAN -- or DBCC OPENTRAN('tempdb')
DBCC INPUTBUFFER(<number>)
KILL <number>
```
De prévention à Long terme
```
-- SQL Server 7.0, should show 'trunc. log on chkpt.' 
-- or 'recovery=SIMPLE' as part of status column: 

EXEC sp_helpdb 'tempdb' 

-- SQL Server 2000, should yield 'SIMPLE': 

SELECT DATABASEPROPERTYEX('tempdb', 'recovery')
ALTER DATABASE tempdb SET RECOVERY SIMPLE
```
Référence : https://web.archive.org/web/20080509095429/http://sqlserver2000.databases.aspfaq.com:80/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html

Autres références : http://social.msdn.microsoft.com/Forums/is/transactsql/thread/af493428-2062-4445-88e4-07ac65fedb76

avez-vous essayé???? select top 1 payment_date de paiements, où edit_date n'est pas null et payment_date=edit_date commande par payment_date desc
J'ai testé toutes les requêtes fournies dans la question. Ou ce que tu demandes?
Je voulais savoir, combien de temps cette requête est à exécuter
Il est écrit dans le fond de la question, juste après que la requête et de son plan d'exécution: il termine dans un couple de secondes.
la base de données tempdb ne peut être en mode de récupération simple.

OriginalL'auteur NG.

Vous devez vous connecter pour publier un commentaire.