Est-il prudent d'activer la réduction automatique du serveur SQL ?

(J'ai d'abord posé une question normale, mais j'ai ensuite découvert la bonne méthode - merci BrentO)

Non, jamais.

J'ai rencontré ce problème plusieurs fois maintenant sur ServerFault et je veux atteindre un large public avec quelques bons conseils. Si des personnes désapprouvent cette façon de faire, downvotez et j'enlèverai volontiers ce message.

La réduction automatique est un paramètre très courant des bases de données. Cela semble être une bonne idée - supprimer l'espace supplémentaire de la base de données. Il existe de nombreux "administrateurs de bases de données involontaires" (pensez à TFS, SharePoint, BizTalk ou tout simplement au bon vieux serveur SQL) qui ne savent peut-être pas que la réduction automatique est un mal absolu.

Lorsque j'étais chez Microsoft, je possédais le moteur de stockage SQL Server et j'ai essayé de supprimer la fonction de rétrécissement automatique, mais elle devait rester pour des raisons de rétrocompatibilité.

Pourquoi l'auto-rétraction est-elle si mauvaise ?

La base de données est susceptible de croître à nouveau, alors pourquoi la réduire ?

1. La réduction de la taille des fichiers (Shrink-grow) provoque une fragmentation du système de fichiers et consomme beaucoup de ressources.
2. Vous ne pouvez pas contrôler le moment où il se déclenche (même s'il est régulier).
3. Il utilise beaucoup de ressources. Déplacer des pages dans la base de données demande de l'unité centrale, beaucoup d'entrées-sorties et génère un grand nombre de journaux de transactions.
4. C'est là que le bât blesse : la réduction des fichiers de données (qu'elle soit automatique ou non) entraîne une fragmentation massive de l'index, ce qui se traduit par des performances médiocres.

J'ai publié un billet de blog il y a quelque temps qui contient un exemple de SQL script qui montre les problèmes qu'il cause et l'explique un peu plus en détail. Voir Rétraction automatique - Désactivez-la ! (il n'y a pas de publicité ou d'autres choses de ce genre sur mon blog). Ne confondez pas cela avec la réduction du fichier journal, qui est utile et nécessaire à l'occasion.

Alors faites-vous une faveur - regardez dans les paramètres de votre base de données et désactivez la réduction automatique. Vous ne devriez pas non plus avoir de rétrécissement dans vos plans de maintenance, exactement pour la même raison. Faites passer le message à vos collègues.

Édition : je devrais ajouter ceci, suite à la deuxième réponse - on pense souvent à tort que l'interruption d'une opération de réduction peut entraîner une corruption. Non, ce n'est pas le cas. J'étais propriétaire du code de réduction dans SQL Server - il annule le déplacement de page en cours s'il est interrompu.

J'espère que cela vous aidera !

Bien sûr, Paul a raison.

Voir toutes les bases de données et leurs paramètres d'authentification. Si vous avez beaucoup de bases de données, l'une d'entre elles se faufilera.

sp_msforeachdb  @command1 = 'Select ''[?]'',DATABASEPROPERTYEX(''?'',''IsAutoShrink'')'

Est-ce que c'est dans le registre du dmv quelque part....Je me pose la question.

Il n'est pas "dangereux" - il n'endommagera rien.

Mais ce n'est pas recommandé pour les environnements de production où la base de données peut décider de commencer un exercice de réarrangement coûteux juste avant qu'une pile de demandes n'arrive, ce qui fait que ces demandes prennent plus de temps à être servies. Il est préférable de planifier les opérations de réduction en même temps que d'autres opérations de maintenance telles que les sauvegardes (en fait, après les sauvegardes - le journal des transactions sera ainsi mieux respecté). Vous pouvez toujours configurer un moniteur pour vous informer lorsque l'espace alloué inutilisé dépasse un certain ratio ou une taille fixe.

Je crois que l'option est désactivée par défaut pour toutes les bases de données dans toutes les éditions de MSSQL à l'exception d'Express.

Un livre blanc disponible sur TechNet explique plus en détail la maintenance de SQL.

http://technet.microsoft.com/en-us/library/cc262731.aspx

J'ai vu un serveur SQL avec les deux options Autogrow et Autoshrink activées. Ce serveur (relativement puissant) était terriblement lent, parce qu'il ne faisait que rétrécir et augmenter les fichiers de la base de données tout au long de la journée. Autoshrink peut être utile, mais je recommande deux choses :

1. Désactiver l'Autoshrink par défaut.
2. Documentez les configurations de votre serveur, afin de savoir où Autogrow et Autoshrink sont activés et où ils ne le sont pas.