May 14, 2014 at 3:06 pm
Hi All,
I have a very strange situation, we have created a Maintenance Plan for Daily Native Backups and it worked perfectly for few weeks. The backups are taken and transferred to another server to make sure they are going into Tapes. Now we have an issue that the SQL Agent Job for the maintenance plan is keep running and no backups being taken. The Job never stopped either. Also I do not see anything related to the Job with "sp_who2 active" while the Job is running. Also I tried backing up into Local SQL Server drive and no luck with that either. The maintenance plan was deleted and re-created multiple times but no luck. Also I do not see anything on SQL Agent Error Log or SQL Server Error Log.
Any advice much appreciated.
May 14, 2014 at 6:02 pm
ola.hallengren.com
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 14, 2014 at 6:09 pm
Thanks for the information. The maintenance plan worked perfectly and I have done the steps and followed the steps as mentioned. There must be an issue somewhere that I cannot figure out.
May 14, 2014 at 7:22 pm
I'm a little confused. Let's verify that backups work. Can you run something like this from your T-SQL window in SQL Server Management Studio:
BACKUP DATABASE yourdbname
TO DISK = 'x:\yourdriveandlocation.bak'
WITH INIT, REPLACE;
You'll need to substitute one of your own database names for 'yourdbname' and provide a valid drive and path for 'x:\yourdriveandlocation.bak'.
Let's see if backups are working to start with. After that, you can at least focus on understanding what's up with the agent jobs (it's frequently security settings by the way).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 15, 2014 at 1:02 am
Yes backups are working whenever they are taken manually. that is what our On-Call team member keep doing every single day ever since the job failed. there are over 20 Databases in this particular server and it is a huge headache to take them manually.
May 15, 2014 at 3:49 am
can you screenshot the pertinent parts of the maintenance plan and post as attachment? What do you see in SQL management studio activity monitor while the job is running? How long does the job take?
May 15, 2014 at 2:02 pm
Here is the Maintenance plan and the Job running
May 16, 2014 at 2:55 am
What if you change the backup job to backup to a new file instead of append to existing?
May 16, 2014 at 6:48 am
nimalatissa (5/15/2014)
Here is the Maintenance plan and the Job running
can you post details of the maintenance plan and job history
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 16, 2014 at 7:59 am
I see some of your problem. Why do you have your DBCC CHECKDB in with your full nightly backup? These jobs should be separate and DBCC CHECKDB should only be ran once a week and hours before or after your full backup or it will loop. Also put your clean up task in it's own plan as well. I have come to find that having separate plans shows you what to fix. Good Luck.
MCSE SQL Server 2012\2014\2016
May 16, 2014 at 10:48 am
Thanks all for you kind reply and spending time on this
1. I did create the Maintenance Plan multiple times without DBCC CHEKDB and also without maintenance cleanup task before I post this to the forum. Tried Again NO LUCK
2. I changed the Maintenance plan to remove append to Existing. NO LUCK
3. Here is the more descriptive screenshot of the maintenance plan
4. There is no Jon History- The Job keeps running and no history recorded.
Also Here is the underlying T-SQL on This Maintenance plan. If I execute that manually it works perfectly
EXECUTE master.dbo.xp_create_subdir N'\\LAXBACK003\evol\dbbackup\LAXSQL046\firelands_rets_01_72_00001'
GO
BACKUP DATABASE [firelands_rets_01_72_00001] TO DISK = N'\\LAXBACK003\evol\dbbackup\LAXSQL046\firelands_rets_01_72_00001\firelands_rets_01_72_00001_backup_2014_05_16_092151_2489395.bak' WITH NOFORMAT, NOINIT, NAME = N'firelands_rets_01_72_00001_backup_2014_05_16_092151_2489395', SKIP, REWIND, NOUNLOAD, STATS = 10
This is increasingly frustrating now for me. :angry:
May 16, 2014 at 11:56 am
My suggestion is to not use maintenance plans. What do they do for you, besides cause headaches? 🙂 Create your own job with the proper steps as scripts. Then you won't have these issues.
Jared
CE - Microsoft
May 16, 2014 at 11:59 am
Oh also... No schedule for your job? I didn't see one in your script.
Jared
CE - Microsoft
May 16, 2014 at 12:12 pm
Thanks for you reply.
Yeah, we thought about adding the Scripts to a Job and run it. My Manager did not like that idea at all.
Also there is no schedules, because I re-created this for testing and never scheduled to run it.
May 16, 2014 at 12:19 pm
nimalatissa (5/16/2014)
Thanks for you reply.Yeah, we thought about adding the Scripts to a Job and run it. My Manager did not like that idea at all.
Also there is no schedules, because I re-created this for testing and never scheduled to run it.
HHMM, This is a head thinker. Have you ran a scripted backup in query analyzer? This is where you will need to look at every angel. Write a script to do the backup. Backup the smallest database first on the SQL server, since this is theory we don't want to blow it out. If the small database backs up, try another database. If they continue to work, backup each one by one to find the database that is in trouble.
Also,
Check the drop zone for your backups and make sure you have permissions to write to the disk.
MCSE SQL Server 2012\2014\2016
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply