The eXpress database transaction log is using too much disk space
Article ID: 151524
Updated On:
Products
Issue/Introduction
The database transaction log for the eXpress (Ghost Solution Suite) database has grown very large and is using up too much disk space.
The file eXpress_log.ldf will sometimes be several GB in size, and will continually grow.
How can this file be reduced in size, and how can it be prevented from growing so large again?
Environment
GSS 3.x.x
Cause
When the Ghost Solution Suite database (eXpress) is created during the installation the recovery model type is set to "Bulk Logged" which stores a lot of information into the database transaction log. This file is used if there is a crash of the database server and the database needs to be rebuilt with as little data loss as possible.
Normally every time the database is backed up with SQL server this transaction log is automatically truncated. There are times however when either the transaction log gets too large, and SQL server cannot automatically truncate the file, or a backup maintenance plan is not set up, so the file never gets flagged to be truncated. When either of these happen the eXpress_log.ldf file can get so large that it fills the entire drive. When this happens there are many other types of errors that can occur in the operating system and in the SQL server.
Resolution
The first thing that should be done is the recovery model of the database should be evaluated. In most cases and environments Bulk-Logged is saving more information than what is needed for a GSS database. It is usually suggested to set the database recovery model to Simple. To change the recovery model you can right-click on the database in the SQL server (In the "Enterprise Manager" program for SQL server), select Properties > Options. Here you can set it to Simple, Full, or Bulk-Logged.
The following Microsoft KB article explains these recovery models in more detail:
Do NOT delete the .idf file manually while the database is attached! Doing so will corrupt the database and the data will be almost unrecoverable. If file size is a concern, running the SQL sample query below may help:
*Note: This query will simulate a SQL backup but will only clear out the transaction log. In cases where hard drive space is very limited (1-2 GB or less free), the query may need to be executed more than once. It is best practices to perform a backup of the database immediately after running this script as the transaction log will then be cleared out. The following query is provided as an example only and should be verified with a DBA who has appropriate access to the database before executing.
use eXpress
Backup Log eXpress with truncate_only
DBCC Shrinkfile('eXpress_Log',truncateonly)
If for some reason the above SQL query will not shrink the size of the eXpress transaction log after running it multiple times, another method that can be used is to detach the eXpress database temporarily (by right-clicking on the database in 'Enterprise Manager' and selecting 'All Tasks' > 'Detach Database'). Next, manually delete the large .idf file and reattach the database by right-clicking on 'Databases' > select 'All Tasks' > 'Attach Database', then browse to and select the express.mdf file that was previously detached.
Feedback
