Shrink Database Log File

Reference Number: AA-00684 Views: 6146 Last Updated: 10-07-2020 06:44 AM 0 Rating/ Voters
Shrink Database Log File

This article describes how to shrink the Netaphor SiteAuditâ„¢ database log file. Shrinking the log file should be part of regular maintenance. The scripts below can be run from the SiteAudit Diagnostics > SQL Query tool or from Microsoft SQL Management Studio.  It is recommended to shrink the SiteAudit database log file as part of regular database maintenance.

SQL Scripts

The SQL scripts provided are examples and may not be suitable for all situations. Executing queries directly against a database can be potentially destructive to data. It is important to be careful to specify the correct database in the script. Netaphor is not responsible for any loss of data that may occur as the result of executing queries against the database.

In order to shrink the database log file, the database must be set to Simple recovery model. Simple recovery model is configured by default if using SQL Express from the SiteAudit prerequisites. 

Determining the Database Recovery Model

The following script can be used to determine the recovery model

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'model'

Changing the Recovery Model to Simple

If it is necessary to change the recovery model to Simple, run the script below:


Determining the Log File Name

It is necessary to know the log file name in order to shrink it. Run the script below, which will show the log file name, the full path, and the size of the log file:

SELECT name, physical_name, size FROM sys.database_files WHERE type = 1

Shrinking the Database Log File

Run the following script to shrink the database log file. The log file will be shrunk as much as possible using this script. Note that the log file name must be replaced in this script with the correct log file name for your database:

DBCC SHRINKFILE(logfilename_log, 10)