Freeing Space

The transaction log file (e.g. KF_SiteName.LDF) becomes enormous and continues to expand. Each time a record is changed in your Microsoft SQL Server database, it notes the full details in a transaction log. This is useful if an error corrupts your data, as it allows you to roll back. However, it also uses a great deal of space.

NOTE The guidance below is relevant for Microsoft SQL Server 2005. Although the same procedure is likely to work, for newer versions please check the Microsoft documentation for any changes. Also, ensure backups are taken before any remedial work is carried out as Omfax Systems cannot be responsible for any data loss incurred.

We recommend that you regularly backup your database and truncate your SQL Server transaction log in order to free the space for use by your database.

Before proceeding, we recommend these links to for further guidance:

• Full transaction logs can make your Microsoft SQL Server database unusable. This article describes how to truncate and shrink the transaction logs when they grow too large. This article also describes how to prevent the transaction logs from growing unexpectedly: click here.

• This article describes how to set up a Maintenance Plan: click here.

• This article answers the questions: What are some reasons the transaction log grows so large? Why is my log file so big? What are some ways to prevent this problem from occurring? What do I do when I get myself on track with the underlying cause and want to put my transaction log file to a healthy size? click here for more.

Truncating Logs

You need access to SQL Server Management Studio to truncate your logs.

Step 1: Connect to your database as an system administrator. Step 2: Make the database that you wish to use the current database by selecting from the dropdown list in the toolbar Step 3: Use the Query window to enter the following SQL code:

EXEC sp_helpfile 

Click the Execute button on the toolbar or press F5 to run the query.

Step 4: The size of your database and its log files will be shown in the Grids window. Make a note of the transaction log file size.

TIP If your database takes up the majority of the space, you should consider adding extra space in your control panel.

Step 5: Return to the Query window and replace the previous code with:

DBCC SHRINKDATABASE (database_name_here, TRUNCATEONLY) 
GO 
BACKUP LOG [database_name_here] 
WITH TRUNCATE_ONLY 

Replace database_name_here with the name of your database.

Step 6: Click the Execute button on the toolbar or press F5 to run the query.

Step 7: If your logs are successfully truncated, you will see a report in the Results window and the DBCC execution completed in the Messages window. Step 8: To check how much space you have saved, use the Query window to enter the following SQL code:

EXEC sp_helpfile 

Click the Execute button on the toolbar or press F5 to run the query.

Step 9: If the transaction log file size has decreased, you have successfully truncated your logs.

If your transaction log file size has not decreased, enter the following code into the Query window:

DBCC SHRINKDATABASE (database_name_here, TRUNCATEONLY) 
GO 
BACKUP LOG [database_name_here] 
WITH NO_LOG 

Click the Execute button on the toolbar or press F5 to run the query.