SQL 2005: Truncating Log Files and Recovering Space

In the SharePoint environment, it does happen from time to time that you will receive an error page highlighted with the message “HRESULT: 0x80040E14”. It is, in most cases, caused by the log file in one of the SharePoint databases being full. To resolve this problem, we need identify the error from the SharePoint log and truncate the log file of the affecting database. Here I compiled some quick steps to truncate log file in SQL 2005.

1. Check SharePoint log
The log is normally under the 12 hives path within the LOGS folder. You should find something like this in your log:

Unexpected query execution failure, error code 9002. Additional error information from SQL Server is included below. “The transaction log for database ‘WSS_Content‘ is full.

In this case the database that is casuing issue is “WSS_Content”.

2. Check the database file size

USE [database name];
EXEC sp_helpfile;

It will return a table with information of the database file (MDF) and the log file (LDF). In our example, [database name] is WSS_Content.

3. Truncate the log

USE [database name];
BACKUP LOG [database name] WITH TRUNCATE_ONLY;
DBCC SHRINKFILE ([log file name], 1);

Note: [log file name] is from the sp_helpfile result under the “name” column of the log file row.

4. Check file size again
After this do step 1 again to see the differences. The log file size should be reduced to the minimum (e.g. 1024).

Leave A Reply

Your email address will not be published. Required fields are marked *