Application Log Truncation

Download

Some applications (i.e. Microsoft Exchange) automatically truncate log files when a full VSS backup is completed. Other applications may require specific configuration settings in order to truncate log files or require the user to truncate logs which are no longer required.

MS SQL will not truncate log files by default for a user database when a full backup is completed unless the database is configured to use the simple recovery model (the default model for user databases is Full). System databases are configured for either full or simple on installation. Truncation takes place when a checkpoint occurs.

The recovery model for a database can be set using SQL server management studio:

1. Connect to the appropriate instance of the Microsoft SQL Server Database Engine in Object Explorer, expand the server tree;
2. Expand Databases and select the required database;
3. Right-click the database, select Properties;
4. In the Select a Page pane click Options;
5. The current recovery model is displayed in the Recovery model list control;
6. Set the recovery model to a required model.

To use T-SQL:

USE database_name
ALTER DATABASE database_name SET recovery simple
GO

SQL logs: database can be truncated using a T-SQL query and explicitly applying a checkpoint

USE database_name
ALTER DATABASE database_name SET recovery simple
GO
CHECKPOINT
GO
ALTER DATABASE database_name SET recovery FULL
GO

The SQL server recovery model can be set to required values using an AIMstor pre- or post- script SQL Server reuses truncated, inactive space in the transaction log instead of allowing the transaction log to continuing to grow and to use more space. When the log file is truncated it will not physically grow smaller.

The transaction log file can be shrunk by using the DBCC SHRINKFILE operation. The DBCC SHRINKFILE Transact-SQL statement can only shrink the inactive part inside the log file. See Microsoft documentation for details.

Alternatively the AUTO_SHRINK option can be set for a database.

MS SQL records backups completed in the msdb database dbo.backupset table, including the recovery model set for each database at the time of backup.

The history of backups can be viewed by listing this table in SQL Server Management Studio or using T-SQL.

Windows event logs application log will also list details of MS SQL database backups including log sequence numbers.

Manually resynchronizing an application causes a VSS snapshot to be created. This will if applicable cause the application to truncate log files.

After manually resynchronizing a repository store destination of an application policy it is advised to immediately create a user snapshot of the store, using the AIMstor statistics screen.

Ensure log files which are still required are not truncated.

Download