I have to make some assumptions here, as there are some missing bits and pieces in your question.
If the backup plan I edited into your question is correct, and if you are performing other database maintenance tasks with the SQL Server Maintenance Plan tool, then you're all good.
Answering Some of Your Unasked Questions
Why have my DIFF database backups encountered a sudden growth?
In general, this is because of one or more of the following root causes:
- your database has grown in size
- your data is being updated/deleted/inserted more often
- your maintenance plans are performing large index reorganizations and/or index rebuilds
When your data changes in a database page, this is marked at the page level with the DIFF flag in the Allocation Status of pages information. Following an example output (DIFF flag towards the bottom of the example output):
PAGE: (1:64)
[...clipped...]
PAGE HEADER:
Page @0x000001E9ACA1E000
m_pageId = (1:64) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 3 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 196608
Metadata: PartitionId = 196608 Metadata: IndexId = 1 Metadata: ObjectId = 3
m_prevPage = (1:67) m_nextPage = (1:46) pminlen = 54
m_slotCnt = 47 m_freeCnt = 5276 m_freeData = 4330
m_reservedCnt = 0 m_lsn = (27147:223932:286) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 238741251
DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x40 ALLOCATED 0_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
As Sean pointed out in his post, all the pages that have changed since the last FULL backup are logged in DCM (Differential Change Map) pages, which logs which extents have changed pages. An example output looks like this:
(1:0) - (1:16) = CHANGED
(1:24) - (1:56) = NOT CHANGED
(1:64) - = CHANGED
(1:72) - = NOT CHANGED
(1:80) - = CHANGED
(1:88) - (1:112) = NOT CHANGED
[... clipped for brevity ...]
(1:509856) - = CHANGED
(1:509864) - (1:509936) = NOT CHANGED
(1:509944) - = CHANGED
(1:509952) - (1:511224) = NOT CHANGED
The more changes that happen, the bigger the DIFF backup becomes. In a worst case scenario the DIFF backup can be the size of the FULL backup, because the last FULL backup was taken too long ago.
A FULL backup will reset the DIFF flag at the page level and all the DCM pages. After the FULL backup has completed the size of the DIFF backup will be smaller again.
So in effect the observations you are making are normal and as-designed. There is currently no issue with the backups themselves.
Why are my DIFF backups 500 MB and then suddenly 3 GB in size?
When SQL Server is writing the DIFF backup file Windows Explorer will not update the size of the file every milli-second, but will refresh what you are seeing at irregular intervals. You might be thinking that the file is finished and only 500 MB is size, but SQL Server is still writing to the file.
You can see what is going on in the system when running a simple query against some DMVs. E.g.:
/*
-- =============================================================================
ADMIN_1ST_Aid_1_Running_Tasks_Extended.sql
Display a list of running tasks in a given SQL Server instance.
Comment out any of the various LEFT elements to reduce the amount
of informaiton displayed.
Copyright (C) 2020 hot2use / JohnKNess
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see <https://www.gnu.org/licenses/>.
-- =============================================================================
*/
/*
-- =============================================================================
Author......: JohnKNess / hot2use
Date........: 29.07.2020
Version.....: 0.9
Server......: localhost (first created for)
Database....: master
Owner.......: -
Table.......: -
Type........: Script
Name........: ADMIN_1ST_Aid_1_Running_Tasks_Extended.sql
Description.: This script retrieves currently running process on
............ a SQL Server instance, depending on which LEFT JOINS
............ are ommitted or not.
............ Please run on the target computer.
History.....: 11-May-2015 0.1 JN First created/adopted
............ 29-Jul-2020 0.9 JN Release to GitHub repository
Editors.....: UEStudio (IDM Computer Solutions, Inc.)
SQLAssistant (SoftTree Technologies Inc.)
-- =============================================================================
*/
SELECT /*
count(*)
*/
/*
count(des1.session_id) AS SessionCount
sdb.name AS DatabaseName,
ssp.name AS LoginUser,
des1.program_name AS Program_Name
*/
-- des1.client_interface_name AS ClientInterfaceName,
des1.session_id AS Session_ID_S,
-- des1.context_info AS Session_Contxt_Info,
-- dec1.session_id AS Session_ID_C,
-- dowt.session_id AS Session_ID_WT,
-- dowt.exec_context_id AS Exec_Contxt_ID,
sdb.name AS DatabaseName,
ssp.name AS SQL_Login_Name,
des1.nt_domain AS NT_Domain,
des1.nt_user_name AS NT_User_Name,
-- dowt.wait_duration_ms AS Wait_Duration_ms,
dowt.wait_type AS Wait_Type,
dowt.blocking_session_id AS Blocking_Session_ID,
dowt.resource_description AS Ressource_Description,
der.[status] AS Request_Status,
der.wait_type AS Request_WaitType,
RIGHT('00' + CAST(der.estimated_completion_time/1000/3600 AS VARCHAR), 2) + ':' + RIGHT('00' + CAST(der.estimated_completion_time/1000/60 AS VARCHAR), 2) + ':' + RIGHT('00' + CAST(der.estimated_completion_time/1000 % 60 AS VARCHAR) , 2) AS [HH:MM:SS],
--der.open_transaction_count AS Request_Open_Transaction_Count,
des1.open_transaction_count AS Session_Open_Transactions,
des1.host_name AS HostName,
des1.host_process_id AS HostProcessID,
des1.program_name AS Program_Name,
dest.[text] AS SQL_Text,
dest2.[text] AS Most_Recent_SQL,
deqp.query_plan AS Query_Plan,
--deps.query_plan AS Actual_Query_Plan, -- SQL 2019
-- deqsx.query_plan AS Current_Query_Plan, -- SQL 2016
des1.cpu_time AS CPU_Time,
des1.memory_usage AS RAM_Usage,
CASE WHEN dowt.blocking_session_id IS NOT NULL AND dowt.blocking_session_id != des1.session_id THEN '--kill ' + cast(dowt.blocking_session_id AS nvarchar(20)) ELSE ' ' END AS killcommand,
'EOR' AS EOR
FROM sys.dm_exec_sessions AS des1
LEFT
JOIN sys.dm_exec_connections AS dec1
ON des1.session_id = dec1.session_id
LEFT -- comment out LEFT to display only sessions that have gone parallel
JOIN sys.dm_os_waiting_tasks AS dowt
ON des1.session_id = dowt.session_id
LEFT -- comment out LEFT to display only sessions currently executing statements
JOIN sys.dm_exec_requests AS der
ON des1.session_id = der.session_id
LEFT -- comment out LEFT to ...... (I'm not telling)
JOIN sys.server_principals AS ssp
ON des1.login_name = ssp.name
/* ==================== This is for SQL Server 2012 + ===================*/
LEFT
JOIN sys.databases AS sdb
ON des1.database_id = sdb.database_id
/* ==================== This is for SQL Server 2012 + ===================*/
/* ==================== This is for SQL Server 2008 R2 ===================
LEFT
JOIN sys.sysprocesses as ss
ON ss.spid = des1.session_id
LEFT
JOIN sys.databases as sdb
ON sdb.database_id = ss.dbid
==================== This is for SQL Server 2008 R2 ===================*/
OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest -- Retrieve Actual SQL Text
OUTER APPLY sys.dm_exec_sql_text(dec1.most_recent_sql_handle) as dest2 -- Retrieve Most Recent SQL Text
OUTER APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp -- Retrieve Query Plan (XML)
-- OUTER APPLY sys.dm_exec_query_plan_stats(der.plan_handle) as deps -- Retrieve Most Recent Actual Query Plan (XML) SQL 2019
-- OUTER APPLY sys.dm_exec_query_statistics_xml(des1.session_id) as deqsx -- Retrieve Current Query Plan (XML) SQL 2019
WHERE 1=1
-- AND sdb.name in ('WinCredit_GLIB_P')
-- AND sdb.name LIKE 'AFU%'
-- AND des1.is_user_process = 1
/*
GROUP BY
sdb.name,
ssp.name,
des1.program_name
*/
ORDER BY
des1.session_id, dowt.exec_context_id;
As long as the Backup processes are running, then the size of the backup files might keep on growing. Following an example output when the BACKUP processes are still running. Please observer the Wait_Types BACKUPTHREAD and BACKUP_IO (you will have to manually Execute the script multiple times to see changs):
+--------------+--------------+---------------------+--------------+--------------+--------------+---------------------+-----------------------+----------------+------------------+----------+---------------------------+-------------+---------------+----------------+----------+-----------------+------------+----------+-----------+-------------+-----+
| Session_ID_S | DatabaseName | SQL_Login_Name | NT_Domain | NT_User_Name | Wait_Type | Blocking_Session_ID | Ressource_Description | Request_Status | Request_WaitType | HH:MM:SS | Session_Open_Transactions | HostName | HostProcessID | Program_Name | SQL_Text | Most_Recent_SQL | Query_Plan | CPU_Time | RAM_Usage | killcommand | EOR |
+--------------+--------------+---------------------+--------------+--------------+--------------+---------------------+-----------------------+----------------+------------------+----------+---------------------------+-------------+---------------+----------------+----------+-----------------+------------+----------+-----------+-------------+-----+
| 90 | YOUR_DB | NT AUTHORITY\SYSTEM | NT AUTHORITY | SYSTEM | BACKUPTHREAD | NULL | NULL | suspended | BACKUPTHREAD | 00:00:00 | 0 | SERVERNAME | 2676 | SQL Management | NULL | NULL | NULL | 0 | 0 | | EOR |
| 90 | YOUR_DB | NT AUTHORITY\SYSTEM | NT AUTHORITY | SYSTEM | BACKUPIO | NULL | NULL | suspended | BACKUPTHREAD | 00:00:00 | 0 | SERVERNAME | 2676 | SQL Management | NULL | NULL | NULL | 0 | 0 | | EOR |
| 90 | YOUR_DB | NT AUTHORITY\SYSTEM | NT AUTHORITY | SYSTEM | BACKUPIO | NULL | NULL | suspended | BACKUPTHREAD | 00:00:00 | 0 | SERVERNAME | 2676 | SQL Management | NULL | NULL | NULL | 0 | 0 | | EOR |
+--------------+--------------+---------------------+--------------+--------------+--------------+---------------------+-----------------------+----------------+------------------+----------+---------------------------+-------------+---------------+----------------+----------+-----------------+------------+----------+-----------+-------------+-----+
Are my SQL Server Maintenance Plans good?
This is something you would have to check. You might have inadvertently configured multiple FULL, DIFF and TLOG backups at the same time, but after a quick glance at your backup schedule, this does not seem to be the case.
However, please double-check your Maintenance Plan Schedules and Tasks.
Do you have better recommendations?
Opinionated Answers
1. Backup Schedule
I would consider implementing the following backup schedule:
- Hourly TLOG Backup (yes, even during the night)
- SO - FR DIFF Backup (23:00 / 11pm)
- SA FULL Backup (23:00 / 11pm)
Then if you have any index reorg/rebuild jobs, place them before 23:00 / 11pm depending on when you have a maintenance window.
With this schedule you will reduce the number of DIFF files lying around, but will still have the ability to restore to a point-in-time.
Your mileage may vary. If your database is really volatile, then you might want to keep the 3-hourly DIFF backups and perform a FULL Backup on a daily basis.
There is no one solution fits all when it comes to backups and restores.
2. Backup Tool
Instead of the SQL Server Maintenance Plans, consider having a look at Ola Hallengren's MaintenanceSolution.sql. I've used it many a time. There is learning curve, but I find it easier to configure and change than the SQL Server Maintenance Plans and have been working with SQL Server since version 6.5 and have used Ola's tool since SQL Server 2005.
How do I have a look at the LSN of my backups?
Have a look at my answer to the question Will non copy only full backup impact transaction log backups chain?, where I provide a script and a short description of the LSNs and what they mean.
RESTORE HEADERONLY FROM DISK = 'C:\backup\YourFullBackup.bkp';and checking theIsCopyOnlycolumn.