Sharepoint databases logfile growing in size

Question

There are about 30 databases in the Sharepoint database server and transactional logfile disc increases in size may 3-4 time per week. DBCC SQLPERF(logspace) suggests that Tempdb has log space used 99%.
select * from sys.dm_db_task_space_usage
where internal_objects_alloc_page_count <> 0 helps me to determine the session that is causing the issue and I am able to kill the session and and shrink the log file.
Executing the expensive query script was able to download execution plan, query 16
/*
Missing Index Details from ExecutionPlan1.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 96.6518%.
*/

/*
USE [X]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[EventCache] ([SiteId],[WebId],[EventTime],[Id])

GO
*/

Something is probably not write with this procedure:

CREATE PROCEDURE dbo.proc_DeplGetIncrementalChangesForScope(
@SiteId uniqueidentifier,
@ChangeTimeEnd datetime,
@ChangeNumberEnd int,
@IncludeUsers tinyint,
@IncludeGroups tinyint,
@IsMajorFileOnly tinyint,
@MinChangeTime datetime out,
@MinChangeNumber int out)
AS
SET NOCOUNT ON
..........
SELECT TOP 1
1
FROM
#ExportObjects e
WHERE
e.Id = ev.WebId AND
e.IsDeleted = 1) AND
NOT EXISTS (
SELECT TOP 1
1
FROM
#ExportObjects e
WHERE
ev.ContentTypeId = e.ContentTypeId AND
e.IsDeleted = 1) ) as res

EXEC proc_DeplAddParentListItemsForLinks @SiteId, @IsMajorFileOnly

RETURN 0

Anyone who has seen this issue an advices how to correct it?

Answer 1

@Avyayah

Okay. Again, some performance issue and how to find/solve it will depend on the version of SQL Server as well. I assume that you have minimum SQL Server 2012R2, but maybe you are lucky and have 2016/2017. TempBD is a very live database. It can be small (zero at the moment of restart server, and after full backup), and can be big if you made backup only once per week (or never) and you never restart the server. If you have space for TempDB, unless it will go to 95-96% everything should be okay [that utilisation is only temporary, till the moment of commit]. But smaller utilisation of space is better of course.

Some extra places to look at:
:- TempDB database at MSDocs | https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=ve-server-ver15#:~:text=%20Starting%20with%20SQL%20Server%202016%20%2813.x%29%2C%20tempdb,a%20new%20instance%20installation.%20You%20can...%20More%20

• in the link above strongly recommended if video by Pam - she is a Goddess of TempDB (I mean really know the engine)
• "Three important things about TempDB" by my friend Pinal Dave | https://logicalread.com/3-things-sql-server-tempdb-performance-pd01/
• good article "Best Practice for SQL Server TempDB" by few my colleagues | https://logicalread.com/sql-server-tempdb-best-practices-multiple-files-w01/
• old but good article "TempDB Multiple Datafiles" | https://www.sqlservercentral.com/blogs/tempdb-multiple-datafiles

Did you looked for MSDocs article by Pedro Lopes: "DBCC SQLPERF (Transact-SQL)" https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-sqlperf-transact-sql?view=sql-server-ver15. I do not have actually SharePoint farm under my hands to check it in a real environment. Sorry.

Answer 2

@Avyayah I would not do this at first.
You (maybe you know) need to know that SQL Server Databases for SharePoint are not working in the same way as a standard Microsoft SQL Databases. The behaviour of that databases, performance and many settings need to be adapted to the best knowledge of how SharePoint App and this sites works, not how SQL Server works. In most cases, SharePoint Application works well with SQL Server on default settings. But depends on the activity on SharePoint App: site collections, sites, subsites, list libraries, document libraries, views, number of elements (items), number of users, size of items, versioning, IRM, labelling, settings on IIS and few other options - settings for SQL Server Databases for SharePoint will be different. And different settings you must apply for different databases listed in the second part of my post.

You have thirty databases, but what databases you have. Are you talking about the total number of content databases or total number of databases for SharePoint, which are (but not limited to): types of SP DB Configuration​, Central Administration content​, Content (one or more)​, App Management Service​, Business Data Connectivity, Search service application:​ Search administration​, Analytics Reporting (one or more)​, Crawl (one or more)​, Link (one or more)​, Secure Store Service​, Subscription Settings Service Application, Usage and Health Data Collection Service​, Word Conversion Service, Machine Translation Services​, Managed Metadata Service​, PerformancePoint Services​, PowerPivot Service (Power Pivot for SharePoint 2013)​, Project Server Service​, State Service​, User Profile Service application:​ Profile​, Synchronization, Social tagging​, Word Automation Services, App Management Service, Managed Metadata Service, PerformancePoint Service, Secure Store Service, SharePoint Translation Service,

You are talking about TempDB issue (we could spend a week here just for this database), Do you have one or many servers? How TempDB been created, where you store it, how it is configured (location, disk type, sector size, auto-growth, the cache for disk, initial size, minimum size, maximum size, and same question both for .mdf and .ldf file). bad configuration of other DB for SP will affect the performance and size of TempDB and their log files.

There is no easy answer without analysis of the details. But the last thing I will do is to run this procedure without analysis of the SharePoint Farm.

PS. Which version of SharePoint, SQL Server, and Windows are we talking about?

Answer 3

@KoprowskiT we are using Sharepoint 2013, and Windows Server 2012R2. Temp Db has 4 datafile 20 GB each and temp log is 8 GB and the files are on separate drive. Among the 30 databases there are sites, documents, WSS_logging, Profile, search services, Translation service, Tempdb was created in one server and replication is not enabled in the server. It is 2 processor 32 GB RAM.

One of the site has this configuration:

Not sure how it was setup with log autogrowth to 2 TB. Yesterday on live instance I changed the data file autogrowth from 1 MB to 1024 MB logfile to 256.
Following this document.
https://sharepointhelpmm.wordpress.com/2016/03/25/improving-your-sharepoint-performance-using-sql-server-settings/

Answer 4

@Avyayah
Well, you know it looks more like consulting than simply help. Few things which I would change.

1. You should not have in any case Maxsize set to unlimited. That settings telling the SQL Server, that the last sector on the disk is unknown, so performance will be slower. Set up the max size of the TempDB on some fixed value when sum of four TempDB files will be less than 90% of the disk size.
2. You never should set up any databases in SharePoint to unlimited size. Some databases are relatively small and they don't need to be bigger than 1GB even. Some databases require more space.
3. depends on the type of SharePoint Database (profile, search, translation, content) they should have implemented different cache for files in SQL [read; write; read/write (mixed)], then naturally you have to have different physical disks because you cannot successfully implement different allocation for cash on the same disk.
4. Where physically are partition G & L: is the same physical disk? The idea is good and proper, but if it is one and the same disk it is not good.
5. 2TB auto-growth for log files is a standard default setting by SQL Server. As an admin, the next step after installing SQL is to change all the default settings in SQL Server.
6. TempDB ALWAYS is possible should be on separate fastest disk. There is no backup needed, redundancy is not too important as well, so push this (.mds and .ldf) to separate SSD or ultra SSD (nVME disk works very well with it).
7. All your databases (both system and user) SHOULD NOT be installed on C drive. None of them. Never. I would separate system db form user db if possible as well.
8. Check this article: what is your sector allocation (must be 64K for performance)
9. Check FillFactor on databases
10. After all reindex your SharePoint databases.
11. and many, many more

All of the above is a basic of administration and architecture for SharePoint and SQL Server.

Answer 5

The normal behaviour of TempDB is consuming space and growing, as this is mostly extra cache for SharePoint DBs (not only). Ut should be bigger, on the fastest possible disk, and limited ins size and growth. TempDB can be 10% 20% and 78% if the SQL servers need it. The query is expensive mostly because for Sharepoint Parrarellism (MAXDEG) =1 always. A lot of work needs to be done with the initial set-up.