July 29, 2010 at 3:14 pm
I'm stumped. Here's what I did.
I had a few SQL Agent Jobs. All had SSIS packages 2 are maintenance backup jobs. They all ran fine as one job running one SSIS package each. What I did was put all of the jobs under one SQL Agent Job as tasks within that job. The next day (today), I checked the history and they reported they all ran with no errors. The problem is, each step ran for ~40-50 seconds and I KNOW the backups take longer than 15 minutes. I checked the backup folders and the backup files were not present. Just the day before's file and it was not modified at all.
I checked logs and I couldn't find any errors. History says all ran fine and every step had the green check.
What gives? :w00t:
Any ideas? 😀
July 29, 2010 at 11:38 pm
Please add an output file to the job (under Job Properties --> Job Step --> Advanced). This will give you the entire job run history
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
July 30, 2010 at 2:19 pm
I'll try that tonight. I did however set a feew stps to log to a file and here's one of the results....
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.4035.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:08:13 PM
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 11:08:13 PM
Finished: 11:08:58 PM
Elapsed: 45.547 seconds
Just so you know, this DB is 75GB and the indexes are very large and there are quite a few. Knowing that, I think you'll all agree that there is no way re-indexing took 45seconds to complete.
July 30, 2010 at 4:45 pm
Just out of curiosity, what happens when you run the job manually?
July 30, 2010 at 5:04 pm
blackbird (7/30/2010)
Just out of curiosity, what happens when you run the job manually?
I think i'll try a manual start tonight after it finishes tonight. I already know it won't actuallrun them all but i'll give it one more chance, then manually start the job.
July 30, 2010 at 5:17 pm
In your new job - are you starting the other jobs, or did you setup new job steps for each package and set that step to run the SSIS package directly?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 30, 2010 at 5:39 pm
Here's a quick example of what I did.
I already had each job as their own SQL agent job. Some were SSIS packages and some were T-SQL execution.
So what I did was moved them into the first job as a "Step" within that job. I setup each step and told it what SSIS package to run. I disabled the actual SQL Agent jobs so they don't run.
Hmmm....
Maybe if I enable the old jobs but removed the schedule?
What do you think?
July 31, 2010 at 8:55 am
No - you wouldn't want to do that. I was thinking you might have just been starting the other jobs instead of running them from the new agent job.
Each step should have taken as long as the step in the original job. Since these are SSIS packages, you can look in the SSIS log to see what they logged. Connect to integration services on that system and view the log activity to see what messages were logged there.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 31, 2010 at 12:54 pm
Here's the log from SSIS starting one of the steps...
Date7/30/2010 11:09:05 PM
LogWindows NT (NT Events)
SourceSQLISPackage
Category(0)
Event1073819648
UserMPAPSDB\localsqlservices
ComputerMPAPSDB
Message
Package "Daily ReBuild Indexes" started.
Here's the log that says it finished the step...
Date7/30/2010 11:09:05 PM
LogWindows NT (NT Events)
SourceSQLISPackage
Category(0)
Event1073819649
UserMPAPSDB\localsqlservices
ComputerMPAPSDB
Message
Package "Daily ReBuild Indexes" finished successfully.
Notice the times on each entry. Their the same. Thats impossible doing a index rebuild on a 75GB DB.
Scratching my head on this.
July 31, 2010 at 1:25 pm
You did not post the job step output log. Does it have any information?
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
July 31, 2010 at 5:40 pm
Here's the log output of the one step in the job...
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.4035.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:08:13 PM
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 11:08:13 PM
Finished: 11:08:58 PM
Elapsed: 45.547 seconds
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.4035.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:08:19 PM
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 11:08:19 PM
Finished: 11:09:05 PM
Elapsed: 45.578 seconds
This is from the log/step Daily "ReBuild Indexes.log". Again, no way it took just 45seconds.....lol.
August 1, 2010 at 12:50 am
I strongly feel that the content of the job has some problems. Since you have merged two maintenance plans, something has gone wrong during the process.
Could you please open the SSIS referred to in the job step in BIDS and check if the package has any issues?
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 1, 2010 at 12:27 pm
If it means anything, when I run the original SQL Agent Job (which is a step in the job I am having issues with), it runs with no problems.
I'll try opening the SSIS package in BID's....
August 1, 2010 at 10:16 pm
OK, I opened just that step in BID's, no issues. I even exported it to a file then ran it, it finished OK. The standalone SQL Agent job runs fine. Just when it is a "Step" in another job is the issue. Logs indicate the "Step" executed fine (green check) yet only ran for 46seconds.
I think i'm going to call MS on this one. I'll replicate first on the Dev/test server.
August 1, 2010 at 10:22 pm
Warren Peace (8/1/2010)
OK, I opened just that step in BID's, no issues. I even exported it to a file then ran it, it finished OK.
How long did it take to execute when you ran this SSIS through BIDS? More than 45 seconds?
If it has not re-indexed as expected, then you may want to check what exactly the Reindex task trying to accomplish.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply