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 (Deployment Server's) 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
Cause
When the Deployment Server's 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 Deployment Server's 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 and go to 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 .ldf file manually while the database is attached! If you do so it will corrupt the database and the data will be almost unrecoverable. If the customer is concerned about the size of the file, have them run the following SQL query in Query Analyzer.
use eXpress
Backup Log eXpress with truncate_only
DBCC Shrinkfile('eXpress_Log',truncateonly)
This script will basically simulate a SQL backup, but will only clear out the transaction log. Sometimes if a customer has very little free space on their hard drive (like only 1-2 GB free) you might have to run this script more than once. I would also recommend after clearing up that space to do a backup of the database immediately after running this script as your transaction log is now cleared out.
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 you can manually delete the .ldf file that has grown so large, and then you can reattach the database by rick clicking on "Databases", and then selecting "All Tasks" >> "Attach Database", and then browse out to the express.mdf file that was previously detached.
Feedback
