0

We're running a full SQL backup each day, then differentials each hour. This has been working on multiple servers for a long time now. I'm not aware of any changes that have been made but, on one server, in particular, the differentials are just getting bigger and bigger, instead of resetting back to a smaller size after a full backup. Normally on other servers, the first differential backup is a few hundred MB, but we're getting a 6GB file every time.

Screenshot of the current backup files attached

The SQL commands being run to perform the backups are:

For full: BACKUP DATABASE <DBName> TO DISK = <Path> WITH INIT;

For diff: BACKUP DATABASE <DBName> TO DISK = <Path> WITH DIFFERENTIAL, INIT

Any help would be greatly appreciated.

Improve this question
6
  • What's about the database's workload?
    – Denis Rubashkin
    Commented Mar 11, 2020 at 10:12
  • It seems there's may be significant update activity between the full and first differential backup, such as index maintenance.
    – Dan Guzman
    Commented Mar 11, 2020 at 10:13
  • If the DB is in full recovery mode, are you backing up the transaction logs regularly? How often?
    – Tony Hinkle
    Commented Mar 11, 2020 at 10:48
  • 1
    You can use a query that periodically polls the modified_extent_change_count column in sys.dm_db_space_usage DMV. Schedule it every minute and store the value in a table with a date and time. Then you can use this to hunt down in time when these modifications occurred.
    – Tibor Karaszi
    Commented Mar 11, 2020 at 12:21
  • 1
    It's the first time I see such a backup strategy (full daily and diff hourly). I'm curious to know what is the reason for such a backup strategy ? P.S. The log backup are different backup then full backup (even if the full backup does backup some part of the log file). If you do diff backup in order to make sure you won't lose more then 1 hour of data, maybe you should look at setting the DB to the full recovery model and simply have log backup hourly.
    – Dominique Boucher
    Commented Mar 11, 2020 at 12:31

2 Answers 2

Reset to default
1

After further digging, it appears someone had left a transaction open, which was the underlying cause of this issue.

I managed to find there was a transaction open by running:

SELECT * FROM sys.sysprocesses WHERE open_tran = 1

I then used this article https://blog.sqlauthority.com/2010/10/01/sql-server-get-query-running-in-session/ which provided the below SQL to find what was being run.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = (YourSessionID)
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

It appeared to be an empty transaction that didn't have any active SQL running so I was able to restart the SQL process to resolve this.

Improve this answer
1
  • Good find! Can you elaborate on how you discovered that? Perhaps it will help someone coming across this question in the future.
    – Ben Thul
    Commented Mar 11, 2020 at 14:36
0

I agree with @Dan. It looks as if all the tables are being 'touched' leading to SQL believing they need to be backed up.

Check out your maintenance tasks to see if that is the cause.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.