Backups twice as large from one day to the next

Question

My SQL Server 2008 R2 production database is running on a dedicated Windows server. It has been running for more than 20 years.

In the last period we have suffered some problems such as freezing without memory but with more than 100 GB RAM available according to the task manager. In addition, the auto increment indexes are skipped, example of 720 jumps to 723.

Every Sunday has an automatic task to rebuild indexes. Last time, the backup went from 22 GB to 37 GB. Between finishing the task for the indexes and completing backup took about 12 hours. Since then, it has grown 1.5 GB in less than 12 hours. I read a bit about rebuilding indexes could be the cause and also that the growth is not in % if not that it grows 1GB above what is needed.

An important point is that the tables and data are the same size; that is to say the data does not grow.

Could you help me to find the reason for this.

Answer 1

Rebuilding indexes is a logged operation and will generate a great deal of log and use space in the database while it runs. Stop blanket rebuilding indexes.

You'd need to update the post with wait stats and other metrics, along with hardware profile to get more help, but my money is on the index rebuilds. - Sean Gallardy

Answer 2

When you say the backup is growing in size, is this a file on the/a local disk where you are observing this behaviour? Are you backing up into a single *.bak file and possibly not using `WITH ... INIT' parameter?

{ NOINIT | INIT }
Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).

[...]

NOINIT
Indicates that the backup set is appended to the specified media set, preserving existing backup sets. If a media password is defined for the media set, the password must be supplied. NOINIT is the default.

[...]

INIT
Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set on that device is overwritten, if conditions permit. By default, BACKUP checks for the following conditions and does not overwrite the backup media if either condition exists:

• Any backup set has not yet expired. For more information, see the EXPIREDATE and RETAINDAYS options.
• The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. For more information, see the NAME option, earlier in this section.

[...]

_{Backup (Transact-SQL) (Microsoft Learn | SQL)}

Please add additional details to your question in order for the community to supply you with a possible solution.

1. Backup types (FULL | DIFF | TLOG)
2. Backup setup (3rd-party solution, SQL Server Maintenance Plans, ...)
3. Backup location (Tape, Disk, ....)