January 5, 2017 at 2:29 pm
Hi,
We have SQL job scheduled to run in the midnight. Due to the space issue it failed 2 days. After that we added more space but we noticed that we are getting backups two times for each database. It is taking each database backup twice.
There are no change in the backup job but why it is running two times.
January 5, 2017 at 4:39 pm
ramana3327 (1/5/2017)
Hi,We have SQL job scheduled to run in the midnight. Due to the space issue it failed 2 days. After that we added more space but we noticed that we are getting backups two times for each database. It is taking each database backup twice.
There are no change in the backup job but why it is running two times.
Make sure it's the job running two times by checking the job history. Also check the message in the history as that can tell you how the job was invoked (by a schedule and what schedule or by a login and which login)
If it is running two times, check the schedule or schedules. One may have been added or the schedule you used could have been modified. You might also see two different schedules invoking the job when you check the job history.
You also want to look at the time the two backups get created as that can give you some clues on what is going on.
If you are running a script for the backups, you could also run the script manually to see if you get two backups. I've seen many scripts break the the beginning of a new year due to different coding errors.
You could also check the SQL logs but a lot of servers have the trace flag to suppress the backup messages so that may not tell you anything.
Guess I should have asked this first but do you have two backup files for the backups in your normal location or are you seeing the two backups in the msdb backup tables?
Sue
January 5, 2017 at 7:30 pm
First thing is there is no change in backup job either schedule or any other change.
I can see there are two backup files in the backup folder for each database with few min gap (Ex for one database backup time stamp 2017_01_05_010001_8177013 and the second backup file for the same database is 2017_01_05_010005_6623077)
There is only one schedule. It is running the maintenance plan. It is running under agent service account.
From the maintenance plan history, looks the maintenance plan ran twice. How it is running twice with same schedule without doing any modifications on the server.
Will the agent restart fix the issue?
One more question
We have a SQL Alert which gives alert that if a database has initial full back and it doesn't have next backup within 24 hrs.
From the alert looks the backup was taken almost 10 days ago but the backup job is running fine. This alert didn't raise last wk. The alert started after we are getting multiple times backups only!
January 5, 2017 at 11:10 pm
Without having access to either the maintenance plan or the job, it's not likely we'll be able to help you find the problem.
Are both the files pretty close to being the same size?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 6, 2017 at 6:00 am
Yes. Almost same size just some kb difference for some databases.
January 6, 2017 at 6:03 am
In the msdb backup tables can you see if both backups were invoked by the same user, etc? Under username.
January 6, 2017 at 7:44 am
It's probably best at this point to try to drop and recreate your maintenance plan. They aren't necessarily the best thing to use in my opinion. You can end up spending more time troubleshooting the issue than it takes to just create a new maintenance plan.
Sue
January 6, 2017 at 8:06 am
That's a very good point.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply