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 sur le serveur se réduit anormalement. Pour résoudre ce problème, il suffit parfois de vider le journal des transactions SQL Server et de réduire les fichiers de la base de données. 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 fichiers journaux, 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.

Table des matières

1. Comment vider le journal des transactions SQL Server ?

Pour éviter l’erreur : Le journal des transactions de la base de données est plein. Il faut réduire les fichiers, il est nécessaire d’exécuter deux commandes, shrinkdatabase et ensuite shrinkfile.

1.1 Exécuter la commande SQL SHRINKDATABASE pour vider le journal

En effet, exécuter la commande SHRINKDATABASE et remplacer bien sûr Expert-Only par le nom du fichier de la base de données. Ainsi La commande va réduire la taille de la base de données sur le disque. Adapter aussi le chemin avec le fichier à réduire, stocké dans un dossier de ce type, idéalement sur un disque ou une partition dédiée : D:\SQL_DATA\DB\DBFILE\. Dans cet exemple, par défaut le fichier base de données se nomme comme ceci DATABASE_EXEMPLE.mdf.

USE [Expert-Only];

DBCC SHRINKDATABASE(N'Expert-Only', 10, TRUNCATEONLY);

Exécuter la commande DBCC SHRINKDATABASE pour réduire le fichier de la base SQL Server

Le système de gestion de la base de données (SGBD) affiche ce message d’information :

DBCC SHRINKDATABASE: File ID 1 of database ID 6 was skipped because the file does not have enough free space to reclaim.

Dans ce cas précis, ce message s’affiche car la base de données [Expert-Only] continent des fichiers secondaires (LDF) et ne peut pas être réduite à hauteur de 10%.

1.2 Exécuter la commande SQL SHRINKFILE pour réduire la taille de la base

Ensuite, utiliser la commande SHRINKFILE remplacer Expert-Only_Log par le nom du fichier de logs SQL Server, par exemple dans un dossier dédié comme celui-ci: D:\SQL_DATA\DB\DBLOG\. Dans cet exemple, par défaut le fichier journal SQL Server se nomme DATABASE_EXEMPLE_log.ldf.

ALTER DATABASE [Expert-Only] SET RECOVERY SIMPLE;

DBCC SHRINKFILE('Expert-Only_log', 1);

ALTER DATABASE [Expert-Only] SET RECOVERY FULL;

DBCC SHRINKFILE pour vider le fichier des transactions de la base SQL Server avec SSMS

2. Consulter la colonne log_reuse_wait de sys.databases.

Il est possible qu’une erreur de ce type s’affiche en exécutant une des 2 commandes :

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

2.1 Exécuter la requête sur la table système sys.databases

Pour consulter la colonne log_reuse_wait, exécuter cette requête, toujours avec 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 question pour débloquer l’état et le ramener à NOTHING.

2.2 Exécuter à nouveau les scripts SHRINK

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. 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.

Autres tutoriels pour gérer les bases SQL Server

Comment vider le journal des transactions avec SQL Server avec un script ?

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.