Troubleshooting
Problem
Customer would like advice on how to reduce their SQL database's transaction log (*.ldf) file, because it is getting very large (for example 10Gb+)
Symptom
If the transaction log file cannot grow (in other words, the transaction log file is full, or the hard drive where it is located is full) then the user may receive error messages. For examples, see separate IBM Technotes.
- As an example, the following message could be found in the log file of the batch job:
Error: The log file for database 'dbname' is full. Back up the transaction log for the database to free up some log space.
Cause
Although transaction log files can grow to be moderately large, it is abnormal for transaction log files to be extremely large.
- Instead, it is normal for the customer's I.T. department's SQL administrator to configure their SQL server to automatically backup and truncate their log files.
If the log files are relatively large (for example a similar size to the 'mdf' data file) then this is often a sign that there is something wrong with the Microsoft SQL server's configuration.
- In other words, a large transaction log file is often a sign of a poorly-optimised Microsoft SQL server.
For example, IBM Cognos recommend that (for performance reasons) the MS SQL server that hosts Controller database(s) has an automatic optimisation task scheduled, for regular re-indexing and statistic updating tasks.
- For more details, see separate IBM Technote #1396973.
It is easy to schedule useful tasks (such as backups and truncation of logs) to this maintenance routine.
- Therefore, the fact that the log file is so large should be taken as a warning for the SQL DBA to double-check that they have an appropriate Database Maintenance plan in place, and that it is working as expected.
Environment
These instructions are suitable for both SQL 2005 onwards.
Diagnosing The Problem
IBM Cognos products (for example Controller) can store their information in a variety of industry standard database formats, which are sold by Third-Party (non-IBM) vendors (such as Microsoft and Oracle).
IMPORTANT:
- IBM Cognos does not directly provide support for any third-party product (for example Microsoft SQL server) so the customer is advised to contact their I.T. department's SQL expert DBA
- The information inside this Technote is purely provided as general unofficial guidance to explain simple concepts.
- Official third party (Microsoft) documentation takes precedence over this article
- The customer's trained Microsoft SQL DBA should be very familiar with all of these subjects and is always the person who should be responsible for the implementation of any changes/best practices for the customer's particular environment.
Resolving The Problem
Long term fix:
Ask your I.T. department's SQL administrator (DBA) to implement a scheduled SQL maintenance job, which will automatically backup and truncate the transaction log(s) - typically every night.
- For more details, see separate IBM Technote #1396973.
Short term workaround:
Ask your I.T. department's SQL administrator (DBA) to manually shrink the transaction log(s) for your server's database(s).
- See below for steps.
PART ONE: Shrinking the Transaction Log (LDF):
1. Logon to the SQL server as an administrator
2. Launch 'SQL Server Management Studio' from the start menu
3. Expand the section 'databases' and locate the relevant database
4. Right-click on the database and choose 'Tasks - Shrink - Files'
5. Change the "File Type" to "Log"
6. Ensure that the option "Release unused space" is selected
7. Click "OK"
PART TWO (Optional): Shrinking the Database file (MDF):
After the above process has finished, you can release more unused data by following the steps below.
- NOTE: Shrinking the 'data' database file (mdf) can cause slower performance in the database afterwards.
- One reason for this is that it can cause fragmentation of the indexes (for more details see Microsoft TechNet article linked below).
- Your SQL server's hard drive is almost full and you desperately need to free space
- You have recently run a process (for example the one described in Technote #1624409) which has deleted a lot of information in the database, and you wish to reclaim this 'empty' space.
1. Right-click on the database and choose 'Tasks - Shrink - Files'
2. Ensure that the "File Type" is set to "Data"
3. Ensure that the option "Release unused space" is selected
4. Click "OK"
After this has finished (which is typically very quick) perform the following:
1. Right-click on the database and choose 'Tasks - Shrink - Database'
2. Click OK
TIP: This process can take some time.
Related Information
Historical Number
1041720
Document Information
Modified date:
15 June 2018
UID
swg21367388