April 15, 2022 at 11:32 am
I have a SQL 2012 database with simple recovery that backs up every night. Normally the backup file is around 87,000 MB and grows proportionality as data is added. Last week I noticed the backup file was around 182,000 MB. Couldn't see anything unusual in the database so I ignored it and the backup size returned to normal the next night. Last night it did the same thing. The back up file was 182,950 MB but the night before it was 87,150 MB. The backup runs through SQL Server Agent job so same options are repeated every night. Any ideas what might be going on and where I should start looking?
April 15, 2022 at 12:57 pm
Are you doing index maintenance before the backup?
April 15, 2022 at 3:24 pm
I have a SQL 2012 database with simple recovery that backs up every night. Normally the backup file is around 87,000 MB and grows proportionality as data is added. Last week I noticed the backup file was around 182,000 MB. Couldn't see anything unusual in the database so I ignored it and the backup size returned to normal the next night. Last night it did the same thing. The back up file was 182,950 MB but the night before it was 87,150 MB. The backup runs through SQL Server Agent job so same options are repeated every night. Any ideas what might be going on and where I should start looking?
What type of backup are you performing? Like Ratback said, are you doing maintenance on the database?
What is the size of the database itself during this period?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 15, 2022 at 4:33 pm
I have a SQL 2012 database with simple recovery that backs up every night. Normally the backup file is around 87,000 MB and grows proportionality as data is added. Last week I noticed the backup file was around 182,000 MB. Couldn't see anything unusual in the database so I ignored it and the backup size returned to normal the next night. Last night it did the same thing. The back up file was 182,950 MB but the night before it was 87,150 MB. The backup runs through SQL Server Agent job so same options are repeated every night. Any ideas what might be going on and where I should start looking?
It sounds to me that you're making backups to the same file all the time and the backup sets are stacking up in the same file. Either assign different names to the backups files (I append dates using some dynamic SQL) or initialize the file so it will overwrite. I DON'T actually recommend the latter because you won't have any history.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 16, 2022 at 7:18 pm
Thanks for all your responses.
No, not doing any maintenance or rebuilding of indexes before backups. Its been about a month since I did some index maintenance.
The database is currently 670,847.75 MB. Doing a Full backup.
The date is appended to the filename automatically so as far as I know they are not stacking up. But its been years since I made the backup job in SSMS and I cant remember if I checked off Append or Overwrite when I did it.
I have attached a screen shot of the folder where the backup goes so you can see what happened on 4/14. The data base in question is ntegracheck. On 4/15 its back to the normal size and there was absolutely no maintenance done during that period. I'm not sure if this means anything but the backup job is set to run at 10:00 p.m. I notice the regular size backups complete a bit after 11:00 but the big back up completed at 10:01 p.m. Was that even enough time? Does that suggest anything?
April 16, 2022 at 7:33 pm
I'm almost out of "off the top of my head questions" but, just to be sure, are you taking DIF backups and mistakenly naming them BAK like your FULL backups are?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2022 at 2:34 pm
If you have the space, you could restore one of the large backups to a test server, with a new name like "ntegracheck_BigBAK" , and see what it looks like.
April 18, 2022 at 3:24 pm
I'm almost out of "off the top of my head questions" but, just to be sure, are you taking DIF backups and mistakenly naming them BAK like your FULL backups are?
I'm with Jeff on this one.
Have you ruled out:
"Normal" database activity? In other words, on certain days there is a large import of data that is purged (or something like that)
What is the size of the database itself? Does this grow at the same time that the backups also grow?
Maintenance.
Appending to the backup file, and somehow it's set to overwrite after a certain size?
What are you using for your backup and maintenance strategy? Can you provide details of all of the related processes?
Is this something new, or has it always been occurring?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 18, 2022 at 4:10 pm
Homebrew01 - I have deleted the last "big" file but can try that if it does it again.
Micheal L John - no normal database activity. Nothing is purged unless its done manually and I am the only one with access to do that. Its been months since I did anything like that.
The database grows daily as do the backups. This reminded me that I do track this database size in a table. I have attached a screen shot of that table. With that said it appears my problem is related to the backup more so than related to the database. But I don't have the experience you guys do so please do hesitate to say I'm wrong.
When I set up the back up I used SSMS, right clicked on database, selected tasks, backup, in the pop up backup type is Full, backup to disk, Its been so long, I cant remember if I checked off append or overwrite on the media option page. I think I would have checked overwrite but I cant be 100% sure. Do you think it would be wise to start the backup job from scratch again? If so should I overwrite all existing backup sets OR backup to a new media set and erase all existing backups?
There is nothing I can see in the backup settings to overwrite at a certain size.
Like I have previously the back up is run nightly by a SQL job. It uses a stored procedure in the master data base and I have included the code.
Last week was the second time I noticed this in a period of 2 weeks. I do look at that folder regularly as I have to delete older files or it fills up the drives. So I would say that it is new.
April 18, 2022 at 4:52 pm
Run this when the file is large. It will list the number of backups in the file.
RESTORE HEADERONLY FROM DISK = 'YourDatabaseBackupFile';
GO
Do yourself a favor. Make your backup strategy more robust. And learn EXACTLY what your code, and jobs are doing. Most importantly, get into the habit of restoring your backups on a regular basis.
You should probably take a look at this:
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 18, 2022 at 6:44 pm
Thanks Michael, I will take a look at that.
Thank you homebrew01, I didn't know I could do that but will when I revise the backup plan.
April 18, 2022 at 6:59 pm
Thanks Michael, I will take a look at that.
Thank you homebrew01, I didn't know I could do that but will when I revise the backup plan.
Hence my previous statement:
Do yourself a favor. Make your backup strategy more robust. And learn EXACTLY what your code, and jobs are doing. Most importantly, get into the habit of restoring your backups on a regular basis.
You should probably take a look at this:
You need to invest in yourself. It really scares me that you do not have the ability to tell us the exact configuration of your backups, and exactly what they do and when they do it.
I'm willing to bet that your databases are in simple recovery because your transaction log files grew out of control and filled the drives?
Yes, I am being harsh. But if you are responsible for your companies databases, then having a backup and restore plan that is tested regularly is critical, and should be your top priority.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 18, 2022 at 7:21 pm
You can go into SQL Server Agent, open the job and see if the backup code is there, or perhaps points to a maintenance plan that you can look at, to see what it's doing.
April 19, 2022 at 8:30 pm
It sounds to me that you're making backups to the same file all the time and the backup sets are stacking up in the same file. Either assign different names to the backups files (I append dates using some dynamic SQL) or initialize the file so it will overwrite. I DON'T actually recommend the latter because you won't have any history.
It is much worse than just not having history - if you are not doing anything else, the second you initialize that file you no longer have the ability to restore prior to that moment in time. And if that backup happens to fail - you no longer have the ability to restore - at all. Definitely should not backup to the same file - but if you are doing that then make sure you copy that file off prior to initializing it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply