DBCC SHRINKFILE sur le fichier journal ne pas réduire la taille même après le journal de sauvegarde sur le disque

Ok, c'est une solution pour réduire la taille physique du fichier de transaction, mais sans changer le mode de récupération simple.

Dans votre base de données, recherchez l'id_fichier du fichier journal à l'aide de la requête suivante.

SELECT * FROM sys.database_files;

Dans mon exemple, le fichier journal est file_id 2. Maintenant, nous voulons localiser les journaux virtuels en cours d'utilisation, et de le faire avec la commande suivante.

DBCC LOGINFO;

Ici, vous pouvez voir si des journaux virtuels sont utilisés en vérifiant si l'état est 2 (en cours d'utilisation) ou 0 (gratuit). Lors de la réduction de fichiers, les journaux virtuels vides sont physiquement supprimés à partir de la fin du fichier jusqu'à ce qu'il atteigne le premier statut utilisé. C'est pourquoi le rétrécissement d'un fichier journal des transactions le rétrécit parfois en partie, mais supprime tous les journaux virtuels gratuits auxquels vous pouvez vous attendre.

Si vous remarquez un état 2 qui se produit après 0, cela empêche le rétrécissement complet du fichier. Pour contourner ce problème, effectuez une autre sauvegarde du journal des transactions et exécutez immédiatement ces commandes, en fournissant le file_id trouvé ci-dessus, et la taille que vous souhaitez que votre fichier journal doit être réduite.

DBCC SHRINKFILE (file_id, LogSize_MB)

DBCC SHRINKFILE (2, 100);
DBCC LOGINFO;

Cela montrera alors l'allocation de fichier journal virtuel, et j'espère que vous remarquerez qu'elle a été quelque peu réduite. Parce que les fichiers journaux virtuels ne sont pas toujours alloués dans l'ordre, vous devrez peut-être sauvegarder le journal des transactions plusieurs fois et exécuter cette dernière requête à nouveau; mais je peux normalement le réduire dans une sauvegarde ou deux.

J'utilise ce script sur sql server 2008 R2.

USE [db_name]

ALTER DATABASE [db_name] SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE([log_file_name]/log_file_number, wanted_size)

ALTER DATABASE [db_name] SET RECOVERY FULL WITH NO_WAIT

Essayez ceci

ALTER DATABASE XXXX  SET RECOVERY SIMPLE

use XXXX

declare @log_File_Name varchar(200) 

select @log_File_Name  = name from sysfiles where filename like '%LDF'

declare @i int = FILE_IDEX ( @log_File_Name)

dbcc shrinkfile ( @i , 50) 

Paul Randal a une excellente discussion sur ce problème sur son blog: http://www.sqlskills.com/blogs/paul/post/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx

Réduire un fichier journal

Pour les fichiers journaux, le moteur de base de données utilise target_size pour calculer la taille cible pour l'ensemble du journal; par conséquent, target_size est la quantité d'espace libre dans le journal après l'opération de réduction. La taille cible pour l'ensemble du journal est ensuite traduite en taille cible pour chaque fichier journal. DBCC SHRINKFILE essaie de réduire immédiatement chaque fichier journal physique à sa taille cible.

cependant, si une partie du journal logique réside dans le journaux au-delà de la taille cible, le moteur de base de données libère autant d'espace que possible, puis émet un message d'information.

Le message décrit les actions nécessaires pour déplacer le journal logique hors des journaux virtuels à la fin du fichier. Une fois les actions effectuées, DBCC SHRINKFILE peut être utilisé pour libérer l'espace restant.

, Car un fichier journal ne peut être réduit à une limite de fichier journal virtuel, de la réduction d'un fichier journal à une taille plus petite que la taille d'un le fichier journal virtuel peut ne pas être possible, même s'il n'est pas utilisé. La taille du fichier journal virtuel est choisie dynamiquement par le moteur de base de données lorsque les fichiers journaux sont créés ou étendus.

• Dépannage: Le Fichier Ne Rétrécit Pas

Si l'opération de réduction s'exécute sans erreur, mais que la taille du fichier ne semble pas avoir changé, vérifiez que le fichier dispose d'un espace libre suffisant pour le supprimer en effectuant l'une des opérations suivantes opérations:

Exécutez la requête suivante.

SÉLECTIONNEZ le nom ,la taille/128.0 - CAST(FILEPROPERTY(nom, 'SpaceUsed') COMME int) / 128.0 comme AvailableSpaceInMB de sys.database_files;

Exécutez la commande DBCC SQLPERF Pour renvoyer l'espace utilisé dans le journal des transactions.

• Si l'espace libre est insuffisant, l'opération de réduction ne peut pas réduire davantage la taille du fichier.

• Généralement c'est le fichier journal semble ne pas rétrécir. C'est généralement le résultat d'un fichier journal qui n'a pas été tronqué.

• Vous pouvez tronquer le journal par la définition de la base de données modèle de récupération SIMPLE, ou sauvegarde du log, puis en exécutant le DBCC SHRINKFILE opération à nouveau.

Exemple :

Réduction d'un fichier journal à une taille cible spécifiée

L'exemple suivant réduit le fichier journal dans AdventureWorks base de données à 1 MB. Pour permettre à la commande DBCC SHRINKFILE de réduire le fichier, le fichier est d'abord tronqué en définissant le modèle de récupération de base de données sur SIMPLE.

Transact-SQL

Utilisez AdventureWorks2012;
Allez
-- Tronquer le journal en changeant le modèle de récupération de base de données SIMPLE.
ALTER base de données AdventureWorks2012
DÉFINIR LA RÉCUPÉRATION SIMPLE;
Allez
-- Réduire le fichier journal tronqué à 1 Mo.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
Allez
-- Réinitialiser le modèle de récupération de base de données.
ALTER base de données AdventureWorks2012
DÉFINIR LA RÉCUPÉRATION COMPLÈTE;
Aller

Lorsque vous utilisez DBCC SHRINKFILE (Logfile, size), il ne tronque que la fin du fichier journal aussi loin qu'il peut aller. Lorsqu'il atteint le journal virtuel le plus élevé encore utilisé, il ne peut pas rétrécir davantage. Ceci est décrit dans les livres SQL Server en ligne à:

Http://technet.microsoft.com/en-us/library/ms189493.aspx

Donc, une fois le haut de gamme de la bûche est clair, il peut être rétréci en taille. Encore une fois, cela dépendra de la quantité de journal est encore en cours d'utilisation. Le journal peut être effacé par des sauvegardes, mais les sauvegardes n'effaceront pas les transactions incomplètes, de sorte que le journal peut rester dans un VLF haut de gamme même après des sauvegardes répétées.

En ce qui concerne l'augmentation et la diminution des VLF, quelle taille le fichier journal a-t-il été créé à l'origine et quel est le paramètre de croissance du fichier journal? Si elle ne pousse que d'une petite quantité elle créera plus VLFs que quiconque désire.

Un modèle commun pour réduire un fichier journal est CHECKPOINT, BACKUP, SHRINKFILE, CHECKPOINT, BACKUP, SHRINKFILE, etc. jusqu'à ce que vous obteniez des résultats. Il y a plusieurs raisons pour lesquelles le journal peut ne pas être rétractable, y compris une très grande restauration.

Passer du Simple au complet a un problème:

Il y a des règles et des exceptions ici. Nous parlerons des transactions de longue durée en profondeur ci-dessous.

Mais une mise en garde à garder à L'esprit pour le plein Le Mode de récupération est le suivant: si vous passez simplement en mode de récupération complète, mais ne prenez jamais une sauvegarde complète initiale, SQL Server n'honorera pas votre demande d'être en modèle de récupération complète. Votre journal des transactions continuera à fonctionner comme il L'a fait dans Simplejusqu'à ce que vous passiez au modèle de récupération complète et preniez votre première sauvegarde complète.

Le modèle de récupération complet sans sauvegardes de journal est mauvais:

Donc, c'est la raison la plus courante de la croissance incontrôlée des billes? Réponse: être en mode de récupération complète sans avoir de sauvegardes de journal.

Cela arrive tout le temps aux gens.

Pourquoi est-ce une erreur si commune?

Pourquoi ça arrive tout le temps? Parce que chaque nouvelle base de données obtient son paramètre de modèle de récupération initial en regardant la base de données du modèle.

Est toujours un modèle de récupération complet-jusqu'à ce que et à moins que quelqu'un le modifie. Donc, vous pouvez dire que le "modèle de récupération par défaut" est plein. Beaucoup de gens ne sont pas conscients de cela et ont leurs bases de données en cours d'exécution dans le modèle de récupération complète sans sauvegardes de journal, et donc un fichier journal des transactions beaucoup plus grand que nécessaire. C'est pourquoi il est important de modifier les valeurs par défaut lorsqu'elles ne conviennent pas à votre organisation et à ses besoins)

J'ai essayé de nombreuses façons, mais cela fonctionne.

L'exemple de code est disponible dans DBCC SHRINKFILE

USE DBName;  
GO  
-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE DBName  
SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (DBName_log, 1);  --File name SELECT * FROM sys.database_files; query to get the file name
GO  
-- Reset the database recovery model.  
ALTER DATABASE DBName  
SET RECOVERY FULL;  
GO

Merci à @ user2630576 et @ Ed.S.

Ce qui suit a fonctionné un régal:

BACKUP LOG [database] TO DISK = 'D:\database.bak'
GO

ALTER DATABASE [database] SET RECOVERY SIMPLE

use [database]

declare @log_File_Name varchar(200)

select @log_File_Name = name from sysfiles where filename like '%LDF'

declare @i int = FILE_IDEX ( @log_File_Name)

dbcc shrinkfile ( @i , 50)

ALTER DATABASE [database] SET RECOVERY FULL