5

I have a database in Microsoft SQL server 2008 R2, in which I have configured the maintenance plan for backups (a full back on Tuesdays, Thursdays and Saturdays at 00, three transactional logs at 4, 5 and 6 am, and then at 7 am a differential, and so on, finishing with a differential backup at 11 PM).

+-----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+ | DAY | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 00 | +-----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+ | MON | - | - | - | L | L | L | D | L | L | L | D | L | L | L | D | L | L | L | D | L | L | L | D | - | | TUE | - | - | - | L | L | L | D | L | L | L | D | L | L | L | D | L | L | L | D | L | L | L | D | F | | WED | - | - | - | L | L | L | D | L | L | L | D | L | L | L | D | L | L | L | D | L | L | L | D | - | | THU | - | - | - | L | L | L | D | L | L | L | D | L | L | L | D | L | L | L | D | L | L | L | D | F | | FRI | - | - | - | L | L | L | D | L | L | L | D | L | L | L | D | L | L | L | D | L | L | L | D | - | | SAT | - | - | - | L | L | L | D | L | L | L | D | L | L | L | D | L | L | L | D | L | L | L | D | F | | SUN | - | - | - | L | L | L | D | L | L | L | D | L | L | L | D | L | L | L | D | L | L | L | D | - | +-----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+

At the beginning of July, there was a problem with a full backup that was not done, because of lack of space (this problem was July 5th), then the differentials began to increase in size, up to 10 GB, when the full was done, the differentials decreased in size to 1 GB (before this, the differentials had a maximum size of 800 MB).

The problem is that every day the differential backup increases in size, until today, when each differential weighs 6 GB. The full backup's size is about 96 GB because the database is too old.

I've seen some strange behavior, when the plan gets to the differentials, at 7:46 (for example), the file is finished creating, and it weighs 500mb, but then, 1 minute later, it's like the differential is overwritten, and there it increases in size up to 3GB.

What could be causing the error? Maybe it's due to something with the TRUNCATE of the full backup? How can I solve this?

I've already tried doing a new manual full backup (on a day that it's not done, at 00 am), and at the moment that it finished being done, a new manual differential, but that didn't solve it. The queries that I run:

FULL BACKUP:

BACKUP DATABASE [xxx] TO DISK = N'\xxx\SQLServerDatabases\Backups\full_reset.bak' WITH INIT, NAME = N'Full_Reset', SKIP, STATS = 10;

DIFFERENTIAL BACKUP:

BACKUP DATABASE [xxx] TO DISK = N'\xxx\SQLServerDatabases\Backups\diff_reset.dif' WITH DIFFERENTIAL, INIT, NAME = N'Diff_Reset', SKIP, STATS = 10;

EDIT: I decided to attach a image to clarify my situation . The numbers are the size of each file (which are compressed, the second column of the numbers), the size is in bytes, and as you can see, these files are the differential backups . E.G of sizes in this case (first column of the numbers); 645,088,768 -> 645 MB 10,498,634,240 -> 10 GB 6,005,724,672 -> 6GB

enter image description here

EDIT 2: I've been comparing the LSN from the Differential and Full backups, for a couple of weeks, and they are different. This is the reason of why I tried to do a FULL backup and a Differential backup manually. I will put the query that Im running for checking the LSN, maybe its wrong (its from ChatGPT tho) . Or maybe you can explain me how the LSN works

-- DECLARE @DatabaseName NVARCHAR(128) SET @DatabaseName = 'xxx' -- DECLARE @Full_LSN NUMERIC(25,0), @Full_BackupStart DATETIME, @Full_BackupFile NVARCHAR(260) SELECT TOP 1 @Full_LSN = database_backup_lsn, @Full_BackupStart = backup_start_date, @Full_BackupFile = bm.physical_device_name FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bm ON bs.media_set_id = bm.media_set_id WHERE bs.database_name = @DatabaseName AND bs.type = 'D' ORDER BY bs.backup_finish_date DESC -- DECLARE @Diff_LSN NUMERIC(25,0), @Diff_BackupStart DATETIME, @Diff_BackupFile NVARCHAR(260) SELECT TOP 1 @Diff_LSN = differential_base_lsn, @Diff_BackupStart = backup_start_date, @Diff_BackupFile = bm.physical_device_name FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bm ON bs.media_set_id = bm.media_set_id WHERE bs.database_name = @DatabaseName AND bs.type = 'I' ORDER BY bs.backup_finish_date DESC -- OUTPUT PRINT '---------------------------------------------' PRINT 'đź—‚ FULL BACKUP:' PRINT ' Archive: ' + ISNULL(@Full_BackupFile, 'NO DETECTED') PRINT ' Date: ' + CONVERT(VARCHAR, @Full_BackupStart, 120) PRINT ' LSN: ' + CONVERT(VARCHAR, @Full_LSN) PRINT '---------------------------------------------' PRINT 'đź—‚ DIFFERENTIAL BACKUP::' PRINT ' Archive: ' + ISNULL(@Diff_BackupFile, 'NO DETECTED') PRINT ' Date: ' + CONVERT(VARCHAR, @Diff_BackupStart, 120) PRINT ' LSN base: ' + CONVERT(VARCHAR, @Diff_LSN) PRINT '---------------------------------------------' IF @Full_LSN = @Diff_LSN PRINT ' SECUENCE OK.' ELSE PRINT ' SECUENCE WRONG.'

This is the output if I run the query now:

--------------------------------------------- FULL BACKUP: Archive: N:\SQLServerDatabases\Backups\xxx.bak Date: 2025-08-14 00:47:17 LSN: 172092000000659700182 --------------------------------------------- DIFFERENTIAL BACKUP:: Archive: D:\SQLServerDatabases\Backups\xxx.dif Date: 2025-08-14 11:51:01 LSN base: 172168000000478000210 --------------------------------------------- SECUENCE WRONG
Improve this question
8
  • 1
    This is a very irregular backup strategy, IMO. How big is the database? In general, how often does data change? I think you can likely simplify your backup strategy ergo simplifying your problems. Commented Aug 13 at 18:42
  • Hi, thanks for answering. The full backups size is about 90 GB, I think the database is running from 2012 or some. Its from a big hospital , so changing the maintenance plan isn't a very worth solution Commented Aug 14 at 1:06
  • 1
    Which tool(s)/solution are you using to perform your backups? The SQL Server Maintenance Plans or a 3-rd party solution? Does your maintenance plan / other solution include database maintnance (DBCC CHECKD, Indes Reorganize, ....)? Why am I asking? Well all these points could impact how big your differential backups is. Please edit your question and add the details to your question. (On a side note: SQL Server 2008 R2 is no longer supported; I would migrate to a newer version) Commented Aug 14 at 8:13
  • Why did you run out of space in the full backup? One possibility could be a change in data access patterns resulting in both a growth in the full size and increased growth of the differentials? Commented Aug 14 at 12:18
  • 1
    Are you by any chance taking full backups using the WITH COPY_ONLY option? That would not be used as a full backup base for the subsequent differential backups. You can verify the latest full backup using something like RESTORE HEADERONLY FROM DISK = 'C:\backup\YourFullBackup.bkp'; and checking the IsCopyOnly column. Commented Aug 14 at 15:14

3 Answers 3

Reset to default
6

[...] the problem is that every day it increases in size, until today, when each differential weighs 6 GB [...]

Differential backups read the Differential Change Map (DCM) pages and backup full extents based on the DCM, which itself has the bits set based on changes to extents in the database. The DCM is only reset during a regular full backup, so what you're seeing is entirely expected (that differential backup sizes change until a full backup is run).

This means that there are changes happening to the database.

In June E.G, the Differential's size were around 300MB - 600MB, but since the day that the full backup wasn't performed, the differential growed until 10GB, and when a full backup was performed from the maintenance plan, their size lowed to around 1GB, but they just keep growing, now they are in 6GB.

See what I wrote above. Differentials are changes to extents in the database. If your differential backups are large, it's because there are changes to the database. This can be because you're doing odd things like rebuilding all of the indexes, doing daily processing of data or tables, etc., anything that changes data.

Thank you for the answer, I edited the post, adding a query about LSNs, maybe you could help me with that?

FULL BACKUP: 172092000000659700182 DIFFERENTIAL BACKUP: 172168000000478000210

The LSNs themselves mean nothing, they represent some opaque change. You obviously have changes, the size of which is unknown, as the VLF numbers are moving higher. I'm not sure what exactly you're attempting to get out of this... database changes are happening, this makes the diff larger as I've explained multiple times above.

Improve this answer
2
  • Hi, thanks for answering. Maybe I should to attach images to clarify what is the situation . In June E.G, the Differential's size were around 300MB - 600MB, but since the day that the full backup wasn't performed, the differential growed until 10GB, and when a full backup was performed from the maintenance plan, their size lowed to around 1GB, but they just keep growing, now they are in 6GB. With "What could be causing the error?" Im meaning to this situation Commented Aug 14 at 1:21
  • Thank you for the answer, I edited the post, adding a query about LSNs, maybe you could help me with that? Commented Aug 14 at 16:10
3

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.

Improve this answer
2
  • Hi, thanks for answering and explaining me these thing with details. Maybe im not being concrete with what I want, I want to "return" to beggining of June, where the Differentials where around 500MB, and I will try a solution in this weeks; Assuming that all of this is because a full backup wasnt performed, I will run all the secuence of the backups (which I already confirmed that is working) in an other server, then I will make a script to "simulate" traffic to the database, and then I will make the same maintenance plan. If all of this work how I want, I will make a "migration" in production Commented Aug 16 at 19:52
  • How big is your database? If it was 20 GB (June) and has grown to 50 GB (now); then depending on the database modifications the size of the DIFFs will grow too. Another line of thought: If the way the clients interact with the database change (i.e. they modify more data) then the size of the DIFFs will grow too. If you implemented or changed an INDEX REORGANIZE / REBUILD Maintenance Plan in June, then the size of the DIFF database backups could grow in size. Why? Because an Index modification is a page change (DIFF flag). Commented Aug 18 at 8:07
0

Finally, I solved the problem. It was a clumsy situation. Actually, I have two servers running the problematic database, one is the principal server, and the other is the Mirror.

I broke the Mirroring because I was having troubles with Kerberos (SSPI context) and Active Directory replication, so I decided to break the Mirroring state, and at the Tuesday 08/19, when the AD replication problems were solved.

I tried to re-do the Mirror of the databases; it seems like the Mirroring wasn't fully broke, when I was trying to redo the Mirror. So I ran the "SET PARTNER OFF" query, and when a Differential backup from the SQL Maintenance Plan was performed, the size was about 400MB.

Maybe that was what were causing this problem, or maybe not, but anyways thank you for all the answers.

enter image description here

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.