March 27, 2018 at 9:19 am
Today one of our production SQL Servers started behaving strangely.
The daily full backup job which runs a "Maintenance Plan" task failed.
The backups are written to an onsite fileserver accesible to both the user running the job as the SQL Server Agent.
Running the t-sql stqatement for the backup worked fine.
But looking at the backup files of the last few days they are immense, their about 10 times the size of the full backups of last week.
The database did not grown with a factor of 10 or more over the past week.
The "Compress Backup" option on server level was turned off and no indictation in the logs it was turned off or on at all.
The maintenance plan backup task should do a database compress, as that's the setting.
Anyone else encounter this, or has a clue why the backup files are seemingly no longer compressed.
March 27, 2018 at 9:30 am
Resender - Tuesday, March 27, 2018 9:19 AMToday one of our production SQL Servers started behaving strangely.
The daily full backup job which runs a "Maintenance Plan" task failed.
The backups are written to an onsite fileserver accesible to both the user running the job as the SQL Server Agent.
Running the t-sql stqatement for the backup worked fine.
But looking at the backup files of the last few days they are immense, their about 10 times the size of the full backups of last week.The database did not grown with a factor of 10 or more over the past week.
The "Compress Backup" option on server level was turned off and no indictation in the logs it was turned off or on at all.
The maintenance plan backup task should do a database compress, as that's the setting.Anyone else encounter this, or has a clue why the backup files are seemingly no longer compressed.
check the default server setting
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 28, 2018 at 1:00 am
The "backup compression default" setting was 0.
I set it to 1.
Backup succeeded but file size still "unexplainably" large.
March 28, 2018 at 6:26 am
Resender - Wednesday, March 28, 2018 1:00 AMThe "backup compression default" setting was 0.
I set it to 1.
Backup succeeded but file size still "unexplainably" large.
Ok the anaomaly has been found/resolved out only to be replaced by bigger issue
March 28, 2018 at 11:58 am
Resender - Wednesday, March 28, 2018 6:26 AMResender - Wednesday, March 28, 2018 1:00 AMThe "backup compression default" setting was 0.
I set it to 1.
Backup succeeded but file size still "unexplainably" large.Ok the anaomaly has been found/resolved out only to be replaced by bigger issue
And that issue is?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 28, 2018 at 12:28 pm
You encrypted the databases 🙂 ?
March 29, 2018 at 12:17 am
no the reason I couldn't explain yesterday was that there was barely enough time for me to just make the quick post.
Apperantly the team that is responsible for that production enviroment decided to change backup strategies, they made a mistake in maintenance plan.
Apperantly they were making daily differential backups as well but the maintenace plan was not properly configured meaning that the diff backups ended at the same location as the full ones.
After a week all bak files older then a week were erased to keep enough space on the share.
Its a stupid coincidence that led to my team being allerted of the fact the full backup started failing.
The differential maintenance plan been adjusted so it writes to a diffrent folder now.
The share was given more space.
Using tips from https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/, I managed to track down the history and identify that the large backup sizes are correct.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply