SharePoint SQL 2005 database log expanding
Hello I am using SharePoint 2007 X64 SP2 I have a web application with a Site collection and a related SQL database. The SQL database recovery model is simple, the mdf file is about 11Gb and there is almost no current activity in the site. My problem is that the log file which is 6Mb during the day increases to 15Gb (max size) at night!!! I back up the SQL database each evening using a 3rd party and also using SQL. Does anyone have an idea what could be causing the log to expand to such an extent? regards DC
June 16th, 2010 10:50am

Hi, Actually this kind of problem occurs when Database is set to FULL RECOVERY model. As you have already set it to Simple Recovery Model, no clue as why this is happening. But I suggest you have a look at how to truncate the LOG Files: http://technet.microsoft.com/en-us/library/ee721075(office.12).aspx http://boris.gomiunik.net/2008/10/sharepoint-config-database-log-file-too-big-reduce-it/ Also, for monitoring your databases, check this link Some recommendations: http://blogs.catapultsystems.com/tlingenfelder/archive/2009/08/26/sharepoint-farm-performance-recommendations.aspx BR, PM
Free Windows Admin Tool Kit Click here and download it now
June 16th, 2010 2:38pm

Since you are using Simple recovery mode, after the log space is no longer needed for recovery from server failure, it is reused. To be more specific, a checkpoint operation in SQL server marks for reuse the space that precedes the MinLSN (see http://msdn.microsoft.com/en-us/library/ms189573.aspx ). And according to the simple recovery mode section in http://blogs.msdn.com/b/teamcenteronsql/archive/2010/06/03/backup-and-recovery-basics.aspx , the inactive transactions in the log are automatically truncated after a checkpoint. So, usually the log file won’t get large if you are using simple recovery mode. However, it can be exceptions if: 1. Automatic checkpoint operation does not occur. I think the probability is low, anyway, you can run T-SQL checkpoint manually, see http://msdn.microsoft.com/en-us/library/ms188748.aspx ; 2. Automatic truncate after checkpoint does not occur. I think this probability is also low, anyway, manually truncate the log file as Prasanjit Mandal mentioned in his reply; 3. Log file was truncated but was not shrinked. You can shrink the log file as described in the URL Prasanjit Mandal provided; 4. There are so many active transactions so the active portion of the transaction log is large, which cannot be marked for reuse and truncated. The probability is also low since you said that there is almost no activity in the site. Anyway, you can use the SQL Server DMV sys.dm_tran_active_transactions (http://technet.microsoft.com/en-us/library/ms174302.aspx ) to check active transactions on the SQL server; 5. There is transaction that involve large amount of database update, such as empty the SharePoint second stage recycle bin. Please take a look at this thread for detail: http://social.technet.microsoft.com/Forums/en-US/sharepointadmin/thread/0860d86a-2e11-4eab-a3ed-569b30f2e3fb/ Gu Yuming TechNet Subscriber Support in forum If you have any feedback on our support, please contact tngfb@microsoft.com
June 17th, 2010 5:47am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics