SQL Server High Availability and Sharepoint - log file grows when no database activity Just started as a dba with a company running SQL Server 2012 High Availability and SharePoint.

Short description: Database with no user activity still has growing log file.

Long description:

They have many, many SharePoint dbs where the logfile size dwarfs the datafile size.  They've just been throwing extra disk space at the issue.

They were in Full Recovery (mandatory for HA) but weren't taking Log backups.  Simply by taking a log backup and a DBCC Shrinkfile to a manageable size I've started to bring things under control.

However, on my trial database the logfile is growing rapidly again.  As far as I can ascertain, no user activity in the database has taken place (insert/update/deletes) since I did the log backup.

Running

select [Current LSN], Operation, Context, [Transaction ID]
    , SPID, [Begin Time], [Transaction Name] 
from fn_dblog(null,null)


shows hundreds of rows of GhostCleanup Task (looking for deletes) followed by a Begin Transaction and a Commit Transaction:



Can anyone explain what's causing this?  I can obviously set up Maintenance Plans to do Log backups much more frequently to keep the size in check, but I'd like to understand why there are all these transactions occurring in a 'static' database.

I've read about setting trace flag 661 to disable ghost cleanup, but don't want to do a server-wide change at this proof of concept stage.

I'm not sure if this is a HA issue, SharePoint issue, neither or both!  I've read lots of posts about SharePoint causing runaway logs but no real explanation.

It's the primary AOAG server. There are two synchronous secondaries and an asynchronous DR secondary.

dbcc opentran(EXT_Metadata_DB)


reports:


  No active open transactions.


Log file is still growing despite no user activity. There were 5 Autogrowth events overnight.  No transactional replication on this db, though HA obviously needs to write the log to the secondary servers. No blocked processes visible.

select d.name, d.log_reuse_wait_desc 
from sys.databases d 
where d.database_id = 15 


gives:


EXT_Metadata_DB    LOG_BACKUP


This seems to imply that all transactions are in the current VLF which can't be cleared. Puzzling thing is, I haven't done any log backups since I did the Shrinkfile. I'm using a tiny database for this investigation.  Data file is only 14MB and I shrank the log file down to 4MB (13% full).  A day or so later, data file remains at 14MB, but log file has now grown to 69MB and is 92% full.  The particular SQL Server instance I'm looking at has 675GB of log files.

select name, size 
from sys.database_files 
where type = 1


gives:


EXT_Metadata_DB_log 8872


before a checkpoint.  Ran checkpoint but size remains at 8872.  Server recovery interval is the default 0.

If I run:

SELECT context AS Context, [Transaction Name] as Description, COUNT(context) AS Count
from fn_dblog(null,null) 
GROUP BY context, [Transaction Name] 
order by count desc


I get 19 rows:


LCX_NULL            NULL                        12681
LCX_NULL            GhostCleanupTask            12326
LCX_BOOT_PAGE       NULL                        12185
LCX_CLUSTERED       NULL                        451
LCX_NULL            UpdateQPStats               81
LCX_FILE_HEADER     NULL                        35
LCX_NULL            SetFileSize                 33
LCX_PFS NULL                                    12
LCX_TEXT_MIX        NULL                        10
LCX_BOOT_PAGE_CKPT  NULL                        10
LCX_DIFF_MAP        NULL                        7
LCX_NULL            DeallocUnlinkedReorgPage    6


No user activity, but 37847 transaction entries in 24 hours. Is this to be expected? I've not modified any data.  It's a static database of metadata.
The cleanup is normal behind the curtain SQL server functionality.  Never ever ever disable this.  I would increase the transaction log backup frequency.  I like mine set to every 15 minutes to keep the backups small and fast.