What am I missing?
ThanksFully qualifying the share fixed the problem. Sure would like to understand why.
Showing posts with label written. Show all posts
Showing posts with label written. Show all posts
Monday, March 12, 2012
Job Fails accessing mapped drive
I built a package in SSIS that exports some data in a database and writes it to a flat file. The flat file is written to a mapped drive on another server (shared folder). The package runs without failure in SSIS and when I deploy it on the SQL Server. The problem comes when I try to run the package as a job; it fails. If I change the file location to the local c: drive, the job runs to sucess. The id that starts the SQL Server Agent is a domain id that is included in both the domain admin group and the local admin group. All the job log indicates is that the job failed; no details.
Friday, March 9, 2012
Job Execution Information not written to system tables
SS 2005 64Bit SP2
Hello Chaps
Intermittent problem with the SQL Agent job history not getting written to the history table. Background:
Today we noticed the account SQL Agent runs under cropping up in sp_who2. A quick check of the activity monitor said nothing was running. We ran a trace and, based on the SQL being executed, had a word with one of the developers who confirmed they had manually executed one of the jobs.
There was no record anywhere that the job had run. There has been an issue with this particular job, when executed by this user, not showing up in history before but, as mentioned, this had been intermittent and we thought that a restart of the service had sorted it.
Stuff run to try to track the job:
EXEC sp_help_jobactivity @.job_name = 'MyJob'
EXEC sp_help_jobhistory @.job_name = 'MyJob'
SELECT *
FROM dbo.sysjobhistory
WHERE job_id = 'MyJob GUID'
The first returned a row with no details in the columns indicating activity (e.g. last_executed_step_date and other columns were null).
sp_help_jobhistory had some historical records but nothing since mid last month.
sysjobhistory correlated with sp_help_jobhistory as you would expect.
Right clicking the job in SSMS and viewing history correlated with sp_help_jobhistory (i.e. some records but nothing since mid-June).
We edited the SQL in the job step and got the developer to rerun the job and, typically, everything appeared as it should in all the above result sets.
Obviously this is tricky to track down since it has been intermittent but does anyone recognise anything that I have described above? I have of course googled but there doesn't really seem to be anything about it.
We have considered there may be a problem in MSDB and may try running CHECKDB to see if anything comes up but somehow I doubt it will.
Ta!I have never seen this.
perhaps you dev was trying to clean up after himself? ;)|||I have never seen this.
perhaps you dev was trying to clean up after himself? ;)I'll take this opportunity to rebuke you for posting totally irrelevent information. Boo! :p
.....
and coincidently bump my post ;)|||I'll take this opportunity to rebuke you for posting totally irrelevent information. Boo! :p
.....
and coincidently bump my post ;)
What are your job history retention settings? What's the maximum number of rows of history total and per job?
By default, I believe that the settings for job history retention are 1000 rows total and 100 rows per job. It's one of the first things I change when setting up a new server. My "default" settings are 10,000 rows total and 1,000 rows per job. Sometimes I feel that even this is not enough.
Also, are there other jobs (such as replication) that run with a great deal of frequency? Like once per minute or so? This would have the tendency of "hogging" the history table.
Ta (or whatever it is you Brits say),
hmscott|||Thanks for responding :)
Yeah - our settings were at the default. However we don't run many jobs (no replication, no "sniffers" running every minute). I checked the number of records in the tables when we had the problem and neither the total nor per job numbers had quite hit the limit. I did up them just to be sure. AFAIK (and I would be shocked if it was otherwise) these work on a LIFO basis though anyway.
Whoop whooop (or whatever you Yanks say :D)|||Thanks for responding :)
You're welcome; I didn't see it the first time around or I would have said something.
AFAIK (and I would be shocked if it was otherwise) these work on a LIFO basis though anyway.
You are correct. I have, however, seen some situations where the history gets overwritten rather quickly. I wasn't certain how long after job execution you were looking in the history tables.
The only other thing I can suggest is to run a trace on a job that completes normally (and writes history to the history tables) and then run another trace on the job with which you are having issues.
Whoop whooop (or whatever you Yanks say :D)
No, no, you obviously have no understanding of us d@.mn Yankees. We say things like, "Reach for the sky, partner!" or "This here forum ain't big enough for the two of us" or, sometimes, "Make mine a double latte soy skinny harmless, and my friend Serge here will have a half-caf grande with legs. Oooo, is that diamond in your ear real?"
:D
Regards,
hmscott|||By the way poots, I wiki'd Pootle Flump and came across the following entry:
The Flumps (http://en.wikipedia.org/wiki/The_Flumps) :shocked:
Now I am afraid I will be having nightmares for the next month.
[shudder /]
:D
hmscott|||By the way poots, I wiki'd Pootle Flump and came across the following entry:
The Flumps (http://en.wikipedia.org/wiki/The_Flumps) :shocked:
Now I am afraid I will be having nightmares for the next month.
[shudder /]To help your nightmares along, I am the one in the white hat :)
Ta re trace. It is tricky catching it from the beginning since it is intermittent. We did run profiler whilst it was running this time but there seemed to be nothing unusual going on...
Hello Chaps
Intermittent problem with the SQL Agent job history not getting written to the history table. Background:
Today we noticed the account SQL Agent runs under cropping up in sp_who2. A quick check of the activity monitor said nothing was running. We ran a trace and, based on the SQL being executed, had a word with one of the developers who confirmed they had manually executed one of the jobs.
There was no record anywhere that the job had run. There has been an issue with this particular job, when executed by this user, not showing up in history before but, as mentioned, this had been intermittent and we thought that a restart of the service had sorted it.
Stuff run to try to track the job:
EXEC sp_help_jobactivity @.job_name = 'MyJob'
EXEC sp_help_jobhistory @.job_name = 'MyJob'
SELECT *
FROM dbo.sysjobhistory
WHERE job_id = 'MyJob GUID'
The first returned a row with no details in the columns indicating activity (e.g. last_executed_step_date and other columns were null).
sp_help_jobhistory had some historical records but nothing since mid last month.
sysjobhistory correlated with sp_help_jobhistory as you would expect.
Right clicking the job in SSMS and viewing history correlated with sp_help_jobhistory (i.e. some records but nothing since mid-June).
We edited the SQL in the job step and got the developer to rerun the job and, typically, everything appeared as it should in all the above result sets.
Obviously this is tricky to track down since it has been intermittent but does anyone recognise anything that I have described above? I have of course googled but there doesn't really seem to be anything about it.
We have considered there may be a problem in MSDB and may try running CHECKDB to see if anything comes up but somehow I doubt it will.
Ta!I have never seen this.
perhaps you dev was trying to clean up after himself? ;)|||I have never seen this.
perhaps you dev was trying to clean up after himself? ;)I'll take this opportunity to rebuke you for posting totally irrelevent information. Boo! :p
.....
and coincidently bump my post ;)|||I'll take this opportunity to rebuke you for posting totally irrelevent information. Boo! :p
.....
and coincidently bump my post ;)
What are your job history retention settings? What's the maximum number of rows of history total and per job?
By default, I believe that the settings for job history retention are 1000 rows total and 100 rows per job. It's one of the first things I change when setting up a new server. My "default" settings are 10,000 rows total and 1,000 rows per job. Sometimes I feel that even this is not enough.
Also, are there other jobs (such as replication) that run with a great deal of frequency? Like once per minute or so? This would have the tendency of "hogging" the history table.
Ta (or whatever it is you Brits say),
hmscott|||Thanks for responding :)
Yeah - our settings were at the default. However we don't run many jobs (no replication, no "sniffers" running every minute). I checked the number of records in the tables when we had the problem and neither the total nor per job numbers had quite hit the limit. I did up them just to be sure. AFAIK (and I would be shocked if it was otherwise) these work on a LIFO basis though anyway.
Whoop whooop (or whatever you Yanks say :D)|||Thanks for responding :)
You're welcome; I didn't see it the first time around or I would have said something.
AFAIK (and I would be shocked if it was otherwise) these work on a LIFO basis though anyway.
You are correct. I have, however, seen some situations where the history gets overwritten rather quickly. I wasn't certain how long after job execution you were looking in the history tables.
The only other thing I can suggest is to run a trace on a job that completes normally (and writes history to the history tables) and then run another trace on the job with which you are having issues.
Whoop whooop (or whatever you Yanks say :D)
No, no, you obviously have no understanding of us d@.mn Yankees. We say things like, "Reach for the sky, partner!" or "This here forum ain't big enough for the two of us" or, sometimes, "Make mine a double latte soy skinny harmless, and my friend Serge here will have a half-caf grande with legs. Oooo, is that diamond in your ear real?"
:D
Regards,
hmscott|||By the way poots, I wiki'd Pootle Flump and came across the following entry:
The Flumps (http://en.wikipedia.org/wiki/The_Flumps) :shocked:
Now I am afraid I will be having nightmares for the next month.
[shudder /]
:D
hmscott|||By the way poots, I wiki'd Pootle Flump and came across the following entry:
The Flumps (http://en.wikipedia.org/wiki/The_Flumps) :shocked:
Now I am afraid I will be having nightmares for the next month.
[shudder /]To help your nightmares along, I am the one in the white hat :)
Ta re trace. It is tricky catching it from the beginning since it is intermittent. We did run profiler whilst it was running this time but there seemed to be nothing unusual going on...
Wednesday, March 7, 2012
Job and Stored Procedure Problems
I've written 2 stored procedures.
Proc 1, cycles through the system table and pulls out the names of all DB's.
Proc 2 is called by Proc 1, after it grabs 1 of the database names. It then
performs a type of backup based on a flag:
F = Full, D = Differential, and T = Transaction Logs.
I capture any errors and return them to Proc 1, so I can formulate an email
giving status.
At the end of Proc 1, I generate an email and ship off, giving the status of
each of the backups.
The process works just fine, if you do the TSQL statement through a query
window, reporting the status of the backup or error message. The problem I
am having, is when i schedule the job. To test it, I took one of my DB's
offline. When I run it through the Query Window, all errors and successes
report just fine.
When I run it through the Job, it fails at the offline database and kills
the entire job. Since the email isn't sent till the end of proc 1, the
email is never being generated, because upon failure of proc 2, it aborts
the entire job with an error.
It still backs up all the databases, just doesn't finish the email portion
of the process.
I've played with the job options, to continue to next step, even though one
doesn't exist, exit with success, doesn't do it, I've even played with SET
XACT_ABORT OFF
Thanks.Hi Kevin
You should be checking the status of the database in sysdatabases to exclude
offline one.
John
"Kevin Antel" wrote:
> I've written 2 stored procedures.
> Proc 1, cycles through the system table and pulls out the names of all DB'
s.
> Proc 2 is called by Proc 1, after it grabs 1 of the database names. It th
en
> performs a type of backup based on a flag:
> F = Full, D = Differential, and T = Transaction Logs.
> I capture any errors and return them to Proc 1, so I can formulate an emai
l
> giving status.
> At the end of Proc 1, I generate an email and ship off, giving the status
of
> each of the backups.
> The process works just fine, if you do the TSQL statement through a query
> window, reporting the status of the backup or error message. The problem
I
> am having, is when i schedule the job. To test it, I took one of my DB's
> offline. When I run it through the Query Window, all errors and successes
> report just fine.
> When I run it through the Job, it fails at the offline database and kills
> the entire job. Since the email isn't sent till the end of proc 1, the
> email is never being generated, because upon failure of proc 2, it aborts
> the entire job with an error.
> It still backs up all the databases, just doesn't finish the email portion
> of the process.
> I've played with the job options, to continue to next step, even though on
e
> doesn't exist, exit with success, doesn't do it, I've even played with SET
> XACT_ABORT OFF
> Thanks.
>
>
Proc 1, cycles through the system table and pulls out the names of all DB's.
Proc 2 is called by Proc 1, after it grabs 1 of the database names. It then
performs a type of backup based on a flag:
F = Full, D = Differential, and T = Transaction Logs.
I capture any errors and return them to Proc 1, so I can formulate an email
giving status.
At the end of Proc 1, I generate an email and ship off, giving the status of
each of the backups.
The process works just fine, if you do the TSQL statement through a query
window, reporting the status of the backup or error message. The problem I
am having, is when i schedule the job. To test it, I took one of my DB's
offline. When I run it through the Query Window, all errors and successes
report just fine.
When I run it through the Job, it fails at the offline database and kills
the entire job. Since the email isn't sent till the end of proc 1, the
email is never being generated, because upon failure of proc 2, it aborts
the entire job with an error.
It still backs up all the databases, just doesn't finish the email portion
of the process.
I've played with the job options, to continue to next step, even though one
doesn't exist, exit with success, doesn't do it, I've even played with SET
XACT_ABORT OFF
Thanks.Hi Kevin
You should be checking the status of the database in sysdatabases to exclude
offline one.
John
"Kevin Antel" wrote:
> I've written 2 stored procedures.
> Proc 1, cycles through the system table and pulls out the names of all DB'
s.
> Proc 2 is called by Proc 1, after it grabs 1 of the database names. It th
en
> performs a type of backup based on a flag:
> F = Full, D = Differential, and T = Transaction Logs.
> I capture any errors and return them to Proc 1, so I can formulate an emai
l
> giving status.
> At the end of Proc 1, I generate an email and ship off, giving the status
of
> each of the backups.
> The process works just fine, if you do the TSQL statement through a query
> window, reporting the status of the backup or error message. The problem
I
> am having, is when i schedule the job. To test it, I took one of my DB's
> offline. When I run it through the Query Window, all errors and successes
> report just fine.
> When I run it through the Job, it fails at the offline database and kills
> the entire job. Since the email isn't sent till the end of proc 1, the
> email is never being generated, because upon failure of proc 2, it aborts
> the entire job with an error.
> It still backs up all the databases, just doesn't finish the email portion
> of the process.
> I've played with the job options, to continue to next step, even though on
e
> doesn't exist, exit with success, doesn't do it, I've even played with SET
> XACT_ABORT OFF
> Thanks.
>
>
Job activity = slow, non job = fast
I have created an integration services package with a script-source reading data from Active directory. Pretty much data is read and written into a sql2005 database.
I have notised that when I run the package via right-clicking the package (Under stored packages\MSDB) and selecting "Run Package" it takes about 45 minutes for it to complete successfully. The same goes if I run the package from inside Visual Studio. However, when I create a job and put this package as one of it's tasks the task takes about 1h 40min. This is more then double the time! In neither case the server is occupied with soething else. And I have tried it several times so it wasn't just an "accident".
Any ida of what this depends on?
regards Andreas
I don't know the answer but I have exactly the same happening for a package I've just created that just copies data from an Ingres database to SQL Server.
Visual studio 45 mins, scheduled job 2 hrs 5 mins
Shocking!
|||You both have conflicting results, so here are some points to think about.
Visual Studio includes debugging, which is slower. You can run without, Ctrl+F5. This should be faster than the default F5 execution behaviour. Debugging can have a high overhead due to all the extra messages. Excessive logging can also have an impact, this is similar to debugging in many resects due to the extra chatter going on to give progress information.
Unless the Visual Studio session and the SQL Server Agent used for the job are on the same local machine, there is a change in execution location between the two methods. This can have a significant impact on performance depending on your source and destination locations. Data may have to travel over the network to the server or workstation, and this can vary greatly.
|||Wicket wrote:
I have created an integration services package with a script-source reading data from Active directory. Pretty much data is read and written into a sql2005 database.
I have notised that when I run the package via right-clicking the package (Under stored packages\MSDB) and selecting "Run Package" it takes about 45 minutes for it to complete successfully. The same goes if I run the package from inside Visual Studio. However, when I create a job and put this package as one of it's tasks the task takes about 1h 40min. This is more then double the time! In neither case the server is occupied with soething else. And I have tried it several times so it wasn't just an "accident".
Any ida of what this depends on?
regards Andreas
That sounds strange. What happens if you execute the package using dtexec.exe?
If it works quicker using dtexec (which I would expect) then use a command-line sub-step in the Agent job rather than the SSIS sub-step. In fact you should be doing this anyway because you get more debugging information. There's an article about this on wiki.sqlis.com but that site is down at the moment so I can't link to it.
-Jamie
:
|||
Is this the article you mean-
Comparing Overhead On The Execution Methods
(http://www.sqlis.com/default.aspx?84)
I think the main site and wiki are both up, although with my internet connection quality today they may was well not be.
|||I'm running Visual Studio on the server itself, not a workstation, so I'm not hopping round the network when running the package from VS but that's beside the point isn't it?
I've stored the package in msdb, scheduled it to run using an agent job using the SQL Server service acount and it takes nearly 3 times as long, I'd expected it to run slow in VS but not as a scheduled job.
Is this the article you mean-
Comparing Overhead On The Execution Methods
(http://www.sqlis.com/default.aspx?84)I think the main site and wiki are both up, although with my internet connection quality today they may was well not be.
No. I mean this one: http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html (site is up now)
-Jamie
|||
I'm running Visual Studio on the server itself, not a workstation, so I'm not hopping round the network when running the package from VS but that's beside the point isn't it?
I've stored the package in msdb, scheduled it to run using an agent job using the SQL Server service acount and it takes nearly 3 times as long, I'd expected it to run slow in VS but not as a scheduled job.
Did you try running it using dtexec?
Is SQL Agent running as a different user?
-Jamie
|||
Is SQL Agent running as a different user?
-Jamie
No, I don't know how to but I'll find out and give it a try.
Yes it is.
|||I'm running the package on the server in each scenario, so no extra networktraffic should take place in any case I think. And the same package (non debug) is used all the time. However, I do run the job as a different user. Should this have any effect?
I'd love to run the package as a command line step, but when I try to do that, I can't seem to get it to read the config-file I'm giving in the command line. Instead an error message complains about that the configuration file cannot be found at the original location (the location given in the settings inside the package). Shouldn't this setting be overridden by the one on the command-line?
regards Andreas
Subscribe to:
Posts (Atom)