I have a SQL Server 2005 instance that hosts a database for Microsoft Sharepoint used internaly by our company staff. The log file is approaching 11GB in size and appears to permanently be 99% full. The database is in simple recovery mode. Forcing a checkpoint appears to have no effect. There are no integrity errors and no open transactions. There are no file growth or size restrictions and plenty of available disk space for both files. At 99% full, I am unable to shrink the log file or find an explanation of why it never truncates. I would be happy if anyone has a suggestion to offer. A more detailed listing of my diagnostic queries and results are below: EXEC sp_helpdb SharepointContent GO Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Latin1_General_CI_AS_KS_WS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled File Type Size Max Size Growth Usage Data 13470912 KB Unlimited 1024000 KB data only Log 11000064 KB 2147483648 KB 102400 KB log only USE SharepointContent GO CHECKPOINT GO DBCC UPDATEUSAGE (0) GO DBCC SQLPERF (LOGSPACE) GO Name Log Size (MB) Log Space Used (%) Status SharepointContent 10742.242 99.397751 0 DBCC OPENTRAN GO Transaction information for database 'SharepointContent'. Replicated Transaction Information: Oldest distributed LSN : (0:0:0) Oldest non-distributed LSN : (21543:3418:1) DBCC CHECKDB GO CHECKDB found 0 allocation errors and 0 consistency errors in database 'SharepointContent'.
The reason for not shrinking as the Sharepoint process is using the tlog file all the times as the virtual log space is not truncated. In this case better to stop the application & then try shrinking. Also state why the database recovery model has been kept in SIMPLE?
Thanx for the suggestion, I will see what I can achieve with the application stopped. I dont really have a good answer why the simple recovery model was implemented, I could ascribe it to "company politics". A full backup is made every 24 hours affording us at least some measure of recovery. Admittedly it would be both easy and preferable to have a full recovery model....
Tell them that its a risk for having SIMPLE recovery model where the data recoverability is not possible with point in time restore, it can only achieve by performing periodic transaction log backups in this case. So unless you control the virtual log size it is hard to shrink or reduce the physical size, read through Books online for Transaction log architecture for more information.
I have been able to revisit this problem; The result from DBCC OPENTRAN below: Transaction information for database 'SharepointContent'. Replicated Transaction Information: Oldest distributed LSN : (0:0:0) Oldest non-distributed LSN : (21543:3418:1) This indicates that the database was previously configured for replication and that the log file will not truncate until outstanding transactions have been written to the distribution database. Since replication is not established or required on the current database and the distribution database does not exist; solving this dilemma required the following commands: Execute SP_ReplicationDbOption SharepointContent,Publish,true,1 GO Execute sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1 GO DBCC ShrinkFile(<SharepointLogFileName>,0) GO Execute SP_ReplicationDbOption SharepointContent,Publish,false,1 GO I hope the above solution may assist future members that encounter this problem.
Very helpful post David. It certainly solved my 6-week old problem, although I must say I know more about replication and fn_dblog than most of my friends. Mike
Hi, Check out the DBCC LOGINFO and find out is there any Virtual log file is active or not if active you can't shrink the log file at any time i,e you can see that output of dbcc loginf i,e you can see the status column,if the status cloumn is 2 then virtual log file is active and if 0 the virtual log file is inactive... Thanks, Your's