Is this a good SharePoint SQL Backup and Maintenance Plan Strategy?
Hi all Looking for some guidance. Setting up SharePoint SQL Backups for the first time and maintenance plans. Is this a good strategy in general even if there is no company policy yet defined? Environment has 2 SQLs VMs on a Microsoft Failover Cluster hosted split over 2 ESXi hosts connected to a SAN. (Cluster Shared Volume in use for SQL Data, SQl Log and SQL Backup volume). General DB Maintenance Task Check Database Integrity (All databases include indexes) Rebuild indexes (All databases, tables, views, orig amount of free space) Update Statistics Task (Full scan all DB and tables and views, existing statistics) History Clean Up Task (Backup Job and Maintenance plan. Age older than 4 weeks) Maintenance Cleanup of Reports (Age older than 4 weeks) 12:01AM Sunday Full Backup - (compress, backup set expires after 14 days). Databases (All) Weekly - 1AM Sunday Differential Backup - (compress, backup set expires after 7 days) Databases (All except Master and Tempdb) Daily - 2AM excl Sunday Transaction Backup - (compress, backup set expires after 1 day) Databases (Content, SharePoint_Config and SharePoint_AdminContent) Daily - Every 15 Minutes Full Backup cleanup Weekly - 2:30AM Sunday Diff Backup cleanup Weekly - 3AM Sunday Log backup cleanup Daily - 4 AM
Differential backups have increased in size.
So in short our differential backups have gone wild. Normally starting from few hundred megabytes and last ones of the day ending somewhere between 10-12 Gigs. That's all normal for us. But we recently have had issues with the next full backup being about 80G or something like when the full backup is normally 50-60G. And when the full backup is about 80G then differentials start at 14G. So needless to say this pretty much eats all the space available on the backup disk. Of course we can add more disk to the machine but that really isn't the best solution since this issue seems to intermittent. Image to clarify what I mean. So then I started looking at the logs. And noticed that there is no log mentioning a successful backup from the 25th, but there is from 24th when the backup is normal regarding size. So my question would be if this is an issue with the full backup? To me it seems that the full backup from 25th isn't sort of registered and and differentials are made against the previous full backup. Logs from full backup 24th day: (successful 56g.) 2022-01-24 01:05:44.09 Backup Database backed up. Database: DB_NAME, creation date(time): 2018/08/14(08:33:06), pages dumped: 775956, first LSN: 9744:300161:237, last LSN: 9744:302244:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'F:\DB_NAME_full_backup_2022_01_24_010002_2306929.bak'}). This is an informational message only. No user action is required. 2022-01-24 01:05:44.11 Backup BACKUP DATABASE successfully processed 775827 pages in 340.326 seconds (17.809 MB/sec). Logs from 25th. No mentions about succesful backup even though it creates a backup with the size of 88g. 2022-01-25 00:09:00.80 spid14s FlushCache: cleaned up 6292 bufs with 1275 writes in 65929 ms (avoided 3489 new dirty bufs) for db 6:0 2022-01-25 00:09:00.80 spid14s average writes per second: 19.34 writes/sec average throughput: 0.74 MB/sec, I/O saturation: 2547, context switches 4073 2022-01-25 00:09:00.80 spid14s last target outstanding: 26, avgWriteLatency 20 2022-01-25 01:07:17.17 spid14s FlushCache: cleaned up 5534 bufs with 1042 writes in 74320 ms (avoided 1200 new dirty bufs) for db 6:0 2022-01-25 01:07:17.17 spid14s average writes per second: 14.02 writes/sec average throughput: 0.58 MB/sec, I/O saturation: 2783, context switches 8284 2022-01-25 01:07:17.17 spid14s last target outstanding: 2, avgWriteLatency 160 We are using MSSQL version 13.0.5888.11 with WS 2k16 build number 14393 Is this a known issue? Are there any known fixes? Thanks in advance
