Vider le journal des transactions SQL Server

Comment vider le journal des transactions SQL Server et réduire la taille de la base sur le disque ? La base de données Microsoft SQL Server est lente ou l’espace disque disponible se réduit anormalement ? Pour résoudre ce problème, il suffit parfois de vider le journal des transactions SQL Server et réduire les fichiers de la base SQL Server. Souvent, pendant cette opération, ce message s’affiche « Le journal des transactions de la base de données est plein ».

Ou encore « Consultez la colonne log_reuse_wait de sys.databases. ». Avant de vider les logs, inspecter les données de la vue système sys.databases de la base de données et repérer la table qui n’est pas dans l’état NOTHING.

Comment vider le journal des transactions SQL Server ? 

Pour éviter cette erreur « Le journal des transactions de la base de données est plein. », et réduire les fichiers, il est nécessaire d’exécuter deux commandes, shrink database et ensuite shrink file.

Exécuter la commande SHRINKDATABASE pour vider le journal des transactions

De plus, si vous tenter un lancement de script de type DBCC SHRINKDATABASE comme ceci :

Ainsi il faut exécuter la commande SHRINKDATABASE et remplacer DATABASE_EXEMPLE par le nom du fichier de votre base de données. Ainsi La commande va réduire la taille de cotre base de données sur le disque. Adapter le chemin avec votre fichier, stocké dans un dossier de ce type, idéalement sur un disque dédié: D:\SQL_DATA\DB\DBFILE\

Dans mon exemple, par défaut le fichier base de donnée se nomme comme ceci  « DATABASE_EXEMPLE.mdf« .

USE BASE_EXEMPLE;
DBCC SHRINKDATABASE(N'BASE_EXEMPLE', 10, TRUNCATEONLY);

Exécuter la commande SHRINKFILE pour réduire la taille d’une base Sql Server

Ensuite, utiliser la commande SHRINKFILE remplacer DATABASE_EXEMPLE par le nom du fichier de logs SQL Server, par example dans un dossier dédié comme celui-ci: D:\SQL_DATA\DB\DBLOG\

Dans mon exemple, par défaut le fichier journal se nomme comme ceci « DATABASE_EXEMPLE_log.ldf ». 

ALTER DATABAS BASE_EXEMPLE SET RECOVERY SIMPLE;
DBCC SHRINKFILE('BASE_EXEMPLE_log', 1);
ALTER DATABASE BASE_EXEMPLE SET RECOVERY FULL;

Une erreur de ce type est affichée en exécutant une des commandes.

Le journal des transactions de la base de données est plein. Consultez la colonne log_reuse_wait de sys.databases.

Pour consulter la colonne log_reuse_wait, exécuter cette requête sous SQL Server Management Studio :

SELECT [name]
,[database_id]
,[source_database_id]
,[owner_sid]
,[create_date]
,[compatibility_level]
,[collation_name]
,[user_access]
,[user_access_desc]
,[is_read_only]
,[is_auto_close_on]
,[is_auto_shrink_on]
,[state]
,[state_desc]
,[is_in_standby]
,[is_cleanly_shutdown]
,[is_supplemental_logging_enabled]
,[snapshot_isolation_state]
,[snapshot_isolation_state_desc]
,[is_read_committed_snapshot_on]
,[recovery_model]
,[recovery_model_desc]
,[page_verify_option]
,[page_verify_option_desc]
,[is_auto_create_stats_on]
,[is_auto_update_stats_on]
,[is_auto_update_stats_async_on]
,[is_ansi_null_default_on]
,[is_ansi_nulls_on]
,[is_ansi_padding_on]
,[is_ansi_warnings_on]
,[is_arithabort_on]
,[is_concat_null_yields_null_on]
,[is_numeric_roundabort_on]
,[is_quoted_identifier_on]
,[is_recursive_triggers_on]
,[is_cursor_close_on_commit_on]
,[is_local_cursor_default]
,[is_fulltext_enabled]
,[is_trustworthy_on]
,[is_db_chaining_on]
,[is_parameterization_forced]
,[is_master_key_encrypted_by_server]
,[is_published]
,[is_subscribed]
,[is_merge_published]
,[is_distributor]
,[is_sync_with_backup]
,[service_broker_guid]
,[is_broker_enabled]
,[log_reuse_wait]
,[log_reuse_wait_desc]
,[is_date_correlation_on]
,[is_cdc_enabled]
,[is_encrypted]
,[is_honor_broker_priority_on]
FROM [tempdb].[sys].[databases];

Inspecter ensuite la ou les bases correspondantes aux lignes avec l’état « ACTIVE_TRANSACTION » dans la colonne log_reuse_wait_desc.

De plus, il est nécessaire d’exécuter ensuite le script SHRINKFILE et SHRINKDATABASE sur la ou les bases en questions pour débloquer l’état et le ramener à NOTHING.

Enfin, après avoir vider le journal des transactions SQL Server, il est possible de réduire votre base de données sans problème.

Ainsi, cet article montre la marche à suivre pour supprimer complètement une base de données SQL Server inutile.

Note: La réduction des journaux n’est pas toujours une bonne idée pour des raisons de performances. En effet, les données sont fragmentées après la réduction de la base de données. Et à la reconstruction des index ils reprendront toute la place nécessaire.

Comment vider le journal des transactions avec SQL Server ?

Pour vider le journal des transactions SQL Server et éviter l’erreur 9002, utiliser les commandes DBCC SHRINKDATABASE et DBCC SHRINKFILE. Pour réduire respectivement la base de données et les fichiers associés.

Soyez le premier à commenter

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée.


*