search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.91k stars 756 forks source link

Duplicate Backups using parallel backup feature #636

Closed sqlshark1 closed 2 years ago

sqlshark1 commented 2 years ago

I use the utility to backup about 11,000 databases in parallel and we have noticed some duplicate backups.

Code to perform abckups from the jobs, I use from 4-6 jobs at once. I have the 2020-12-31 bits.

EXECUTE [DMG_UTILITY].[dbo].[DatabaseBackup] @Databases = @DBName, @Directory = N'\FileServer\ProdBackup\', @BackupType = 'FULL', @CopyOnly='Y', @Verify = 'Y', @DatabasesInParallel = 'Y', @DatabaseOrder = 'DATABASE_SIZE_DESC', @CleanupTime = 2163, @CheckSum = 'Y',@LogToTable = 'Y'

sqlshark1 commented 2 years ago

Will you be developing a fix for the dupl;icate issue in the future? Thanks in advance, Ed

bwiggins10 commented 2 years ago

I use this across various environments with varying SQL Versions, and have not had this occur before and have used every single parameter you've used EXCEPT for @DatabaseOrder = 'DATABASE_SIZE_DESC' leading me to believe it's a bug with this parameter. I would try omitting this and see if it recurs.

sqlshark1 commented 2 years ago

Thanks for the advise, I will test the theory and let you know. Ed Pochinski

From: Ben @.> Sent: Wednesday, April 6, 2022 3:28 PM To: olahallengren/sql-server-maintenance-solution @.> Cc: sqlshark1 @.>; Author @.> Subject: Re: [olahallengren/sql-server-maintenance-solution] Duplicate Backups using parallel backup feature (Issue #636)

I use this across various environments with varying SQL Versions, and have not had this occur before and have used every single parameter you've used EXCEPT for @DatabaseOrder = 'DATABASE_SIZE_DESC' leading me to believe it's a bug with this parameter. I would try omitting this and see if it recurs.

— Reply to this email directly, view it on GitHubhttps://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Folahallengren%2Fsql-server-maintenance-solution%2Fissues%2F636%23issuecomment-1090677694&data=04%7C01%7C%7Cc2b464d429254b7b446a08da1803a570%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637848701186734273%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=9z09IgbMmNdnEk3p5y9GnuIHkDY9ItnCmy7iUI8v1A0%3D&reserved=0, or unsubscribehttps://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAOH22ZGTLYZ4IE74B4ESY5TVDXQWHANCNFSM5RLJWLGA&data=04%7C01%7C%7Cc2b464d429254b7b446a08da1803a570%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637848701186734273%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=MafmsGkw5xz4CaBerOoG5apB3hSzq4D3PY%2F4TWd8p%2FI%3D&reserved=0. You are receiving this because you authored the thread.Message ID: @.***>

sqlshark1 commented 2 years ago

We will be testing removal of the @DatabaseOrder parameter this week. Will be a good test, I backup over 11,000 databases across 7 Azure VM's. I will post the findings when testing is completed. The code is awesome, I use a modified backup procedure where it inserts all full backup names to a tracking table and I log ship over 11,000 db's with the code for backing up purposes, we developed some nice restore code on the DR end of things. Aside from the Azure VM disks not being very fast with IO it works well. I dual stream the tran logs each run and the setup streams 6 backups at once.

sqlshark1 commented 2 years ago

OK you are correct Ben, I removed that parameter [ @DatabaseOrder ] and the duplicate backups have vanished. Thanks very much for your time in testing and posting. Ed Pochinski

RobMark1 commented 2 years ago

Just an FYI, we have the same issue with a small amount of our databases being backed up twice. We also are using the @DatabaseOrder with a value of DATABASE_SIZE_DESC. This gives me something to go on and will dig in deeper to locate a fix to the DatabaseBackup SP or figure out a work around.