Freeing MS SQL space by truncating logs
Symptoms
The transaction log file (e.g. KF_Model_32.LDF) becomes enormous and continues to expand.
Cause
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.
Resolution
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.
[This article describes the problem http://support.microsoft.com/kb/873235 and this describes setting up a Maintenance Plan¨ http://www.databasedesign-resource.com/sql-server-maintenance-plan.html]
Note: You need Microsoft Enterprise Manager and Query Analyser to truncate your logs.
Step 1: Connect to your database using Query Analyser and the db_owner user. This is the user that you created when you added the database to your account.
Step 2: Use the Query window to enter the following SQL code:
EXEC sp_helpfile
Click the green triangle on the Query Analyser toolbar or press F5 to run the query.
Step 3: 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.
Quick tip: If your database takes up the majority of the space, you should consider adding extra space in your control panel.
Step 4: 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 5: Click the green triangle on the Query Analyser toolbar or press F5 to run the query.
Step 6: If your logs are successfully truncated, you will see a report in the Grids window and the DBCC execution completed in the Messages window.
Step 7: To check how much space you have saved, use the Query window to enter the following SQL code:
EXEC sp_helpfile
Click the green triangle on the Query Analyser toolbar or press F5 to run the query.
Step 8: 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 green triangle on the Query Analyser toolbar or press F5 to run the query.
Status
This behaviour is by design.