• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 48
  • Last Modified:

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
0
Kevin
Asked:
Kevin
  • 3
  • 2
2 Solutions
 
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
 
Jose TorresSenior SQL Server DBACommented:
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
 
KevinAuthor 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
KevinAuthor 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
 
KevinAuthor Commented:
Confirmed.

Thank you very much for your support.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now