Phantom Backup job on SQL Server 2008 - Want to locate and kill it.

Good Morning,

We have a server running 'Microsoft SQL Server 2008 R2, SP3 (v10.50.6220.0)'.

We noticed a few days ago that the drive space was filling up more than usual for our database backups.

We have a maintenance plan that does a cleanup of old backups and then backs-up our databases. This subplan starts at 8pm Monday-Friday. This plan has been working fine and without issue for the last 10 months.

When we look at the database backup files on the drive we can see that the database was backed up twice. Once at 8pm and again at 12am.

The second run of backups are not duplicates, as they have a different sequence number and the size is also different. Example below:

DB1_backup_2018_03_15_220013_1630288.bak – Time Stamp: 3/15/2018 8:05PM – Size: 21,726,335 KB
DB1_backup_2018_03_15_220013_6675138.bak – Time Stamp: 3/16/2018 12:10AM – Size: 29,145,220 KB

We recreated the Maintenance plan, thinking it could have gotten corrupt, but experienced the same issue.

Yesterday we disabled the maintenance plan completely to see what would happen. This morning we checked and could see that the phantom backup job ran again at 12am. But only one backup was there, since we disabled our maintenance plan completely.

We do not understand where this phantom job is coming from. The logs are showing that this phantom job is running a maintenance cleanup task and backing up the database. It shows the user that is running it (which is the same account that runs our current maintenance plan job), but the logs do not show from what server it’s coming from etc.

Kindly advise how we can find out where this backup job is coming from and kill it, as I said previously this has only started happening a few days ago.

Regards,
N
LVL 1
KevinInformation TechnologyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You must have another backup job running. Mind that you don't need to set the backup jobs within a maintenance plan. That can be directly done in SQL Agent or even a Windows Task that runs a script to perform the backups.
You might find more information about the jobs that are running by checking in the SQL Server logs or even in Windows Event Viewer.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jose TorresCertified Database AdministratorCommented:
Run the query below.  Look at the server_name this is where the backup originated from
USE msdb;
SELECT backupset.user_name
      ,backupset.backup_start_date
      ,backupset.backup_finish_date
      ,backupset.type
      ,backupset.database_name
      ,backupset.server_name
      ,backupset.machine_name
FROM dbo.backupset

Open in new window

0
KevinInformation TechnologyAuthor Commented:
@Vitor - I've checked the task scheduler and its not coming from there. Now you did say it could be done directly from the SQL Agent. Can you please explain how I can check that area? As for the SQL Server logs, they aren't very helpful, I can see that the backups are being done twice in the logs, but I cannot see where they are being initiated from (like what is causing the second batch to fire).

@Jose - Lovely little script you have there. Very very helpful, however looks to be that the job is being started from the local server. Just need to find out where. Thank you very much for sharing it though.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Vitor MontalvãoMSSQL Senior EngineerCommented:
If you have a non SQL Express Edition then check if SQL Server Agent service is up is running. If it is, then you can check the SQL Agent jobs inside SSMS. Just open the SQL Server Agent option in the SSMS Object Explorer to get the list of available jobs.

You can post here the SQL Server Log if you don't know how to read it. We can try to help you out.
0
KevinInformation TechnologyAuthor Commented:
@Vitor & @Jose - So I reviewed the SQL Server logs with a colleague and also reran the script Jose sent and we think we have identified the culprit.

We have a BackupExec Server for all of our server backups, we've noticed from the logs and the script that the BackupExec account is being used in some instances. We had a look at what is being backed up for that particular BackupExec job and noticed that the "Microsoft SQL Server Instance" is enabled for that server on the backup job.

So we've disabled it. We are pretty sure that this is the problem, but will see what happens tonight just to be sure.

I will advise in the morning.

Kind Regards,
N
0
KevinInformation TechnologyAuthor Commented:
Confirmed.

Thank you very much for your support.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.