Log file fails to truncate | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Log file fails to truncate

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.

Glad to see this solution, thanks for sharing it.

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

Thanks davidfarr, had the same problem….great post!!

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

Rama
Appreciate your post here, but you see the solution has been posted by OP already.

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |