Hi,
I am trying to run a VB Script step on A SQL Server 2000
Developer SP3a - OS Win 2000 Professional SP2 and the job
is hanging.
If the same script I am executing from a DTS ActiveX task
it runs without any issue.
Here is the script
Dim fso, Folder, Files, File, datecreated
Set fso = CreateObject("Scripting.FileSystemObject")
'Get handles to folder current location.
Set Folder = fso.GetFolder("E:\Backup_History")
Set Files = Folder.Files
If Files.Count > 0 Then
For Each File In Files
'Get files creation date
MsgBox ( file.DateLastModified)
If file.DateLastModified < date -
7 Then
' Delete the files.
File.Delete
End If
Next
End If
Set fso = Nothing
Set Folder = Nothing
Set Files = Nothing
Set File = Nothing
It meant to delete files older then a week.
Any help is very appreciated.
IonelGet rid of the MsgBox ( file.DateLastModified) :-)
There is no visible desktop to display this message box on so it can never
be acknowledged thus the job hangs
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ionel" <anonymous@.discussions.microsoft.com> wrote in message
news:016401c3c3fd$a96226b0$a301280a@.phx.gbl...
> Hi,
> I am trying to run a VB Script step on A SQL Server 2000
> Developer SP3a - OS Win 2000 Professional SP2 and the job
> is hanging.
> If the same script I am executing from a DTS ActiveX task
> it runs without any issue.
> Here is the script
> Dim fso, Folder, Files, File, datecreated
> Set fso = CreateObject("Scripting.FileSystemObject")
> 'Get handles to folder current location.
> Set Folder = fso.GetFolder("E:\Backup_History")
> Set Files = Folder.Files
> If Files.Count > 0 Then
> For Each File In Files
> 'Get files creation date
> MsgBox ( file.DateLastModified)
> If file.DateLastModified < date -
> 7 Then
> ' Delete the files.
> File.Delete
> End If
> Next
> End If
>
> Set fso = Nothing
> Set Folder = Nothing
> Set Files = Nothing
> Set File = Nothing
> It meant to delete files older then a week.
> Any help is very appreciated.
> Ionel
>|||Thanks,
I had several MsgBox that I used for debugging the script.
This one just escape my attention.
Thanks again.
Ionel
>--Original Message--
>Get rid of the MsgBox ( file.DateLastModified) :-)
>There is no visible desktop to display this message box
on so it can never
>be acknowledged thus the job hangs
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Ionel" <anonymous@.discussions.microsoft.com> wrote in
message
>news:016401c3c3fd$a96226b0$a301280a@.phx.gbl...
>> Hi,
>> I am trying to run a VB Script step on A SQL Server 2000
>> Developer SP3a - OS Win 2000 Professional SP2 and the
job
>> is hanging.
>> If the same script I am executing from a DTS ActiveX
task
>> it runs without any issue.
>> Here is the script
>> Dim fso, Folder, Files, File, datecreated
>> Set fso = CreateObject("Scripting.FileSystemObject")
>> 'Get handles to folder current location.
>> Set Folder = fso.GetFolder("E:\Backup_History")
>> Set Files = Folder.Files
>> If Files.Count > 0 Then
>> For Each File In Files
>> 'Get files creation date
>> MsgBox ( file.DateLastModified)
>> If file.DateLastModified < date -
>> 7 Then
>> ' Delete the files.
>> File.Delete
>> End If
>> Next
>> End If
>>
>> Set fso = Nothing
>> Set Folder = Nothing
>> Set Files = Nothing
>> Set File = Nothing
>> It meant to delete files older then a week.
>> Any help is very appreciated.
>> Ionel
>>
>
>.
>|||I've done the same thing myself :-)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ionel" <anonymous@.discussions.microsoft.com> wrote in message
news:07e701c3c402$e33f6ff0$a001280a@.phx.gbl...
> Thanks,
> I had several MsgBox that I used for debugging the script.
> This one just escape my attention.
> Thanks again.
> Ionel
> >--Original Message--
> >Get rid of the MsgBox ( file.DateLastModified) :-)
> >There is no visible desktop to display this message box
> on so it can never
> >be acknowledged thus the job hangs
> >
> >--
> >HTH
> >
> >Jasper Smith (SQL Server MVP)
> >
> >I support PASS - the definitive, global
> >community for SQL Server professionals -
> >http://www.sqlpass.org
> >
> >
> >"Ionel" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:016401c3c3fd$a96226b0$a301280a@.phx.gbl...
> >> Hi,
> >>
> >> I am trying to run a VB Script step on A SQL Server 2000
> >> Developer SP3a - OS Win 2000 Professional SP2 and the
> job
> >> is hanging.
> >> If the same script I am executing from a DTS ActiveX
> task
> >> it runs without any issue.
> >>
> >> Here is the script
> >>
> >> Dim fso, Folder, Files, File, datecreated
> >>
> >> Set fso = CreateObject("Scripting.FileSystemObject")
> >>
> >> 'Get handles to folder current location.
> >> Set Folder = fso.GetFolder("E:\Backup_History")
> >>
> >> Set Files = Folder.Files
> >> If Files.Count > 0 Then
> >> For Each File In Files
> >> 'Get files creation date
> >> MsgBox ( file.DateLastModified)
> >> If file.DateLastModified < date -
> >> 7 Then
> >> ' Delete the files.
> >> File.Delete
> >> End If
> >> Next
> >> End If
> >>
> >>
> >> Set fso = Nothing
> >> Set Folder = Nothing
> >> Set Files = Nothing
> >> Set File = Nothing
> >>
> >> It meant to delete files older then a week.
> >>
> >> Any help is very appreciated.
> >>
> >> Ionel
> >>
> >>
> >
> >
> >.
> >
Showing posts with label sp2. Show all posts
Showing posts with label sp2. Show all posts
Wednesday, March 28, 2012
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...
Monday, February 20, 2012
JDBC performance on Windows Xp SP2
I wrote a conversion program to convert some dbf data to an sql server 2000.
When I run the program on a Windows 2000 SP4 everything works fine. When
I run the same program on a Windows XP SP2 the program is really slow.
Haven't tried the program on a xp without sp 2. Maybe SP is the problem.
Anyone had the same problem?
On a 2000 machine the program copies up to 100 records a second. The same
program running on a xp machine copies only one record a second!
I tried the latest ms jdbc driver as wel as the open source driver.
No difference, same problem.
It's a sql server 2000 Sp3.
I disabled the xp firewall, but same result. Still to slow.
Both machines (2000 and xp) have the same hardware: pentium IV 2.4 GHz with
512MB ram.
I'm using the sun java 1.5.0_01.
Any idea?
Thanks Davy
Davy,
Have you been able to narrow down at all what is slower? Is it reading
the data from dbf or inserting into SQL Server?
Sue Purkis
DataDirect Technologies
"Davy" <Davy@.discussions.microsoft.com> wrote in message
news:5D7E84B6-DF43-41C3-98FA-6C9AF13E34C8@.microsoft.com...
> I wrote a conversion program to convert some dbf data to an sql server
2000.
> When I run the program on a Windows 2000 SP4 everything works fine. When
> I run the same program on a Windows XP SP2 the program is really slow.
> Haven't tried the program on a xp without sp 2. Maybe SP is the problem.
> Anyone had the same problem?
> On a 2000 machine the program copies up to 100 records a second. The same
> program running on a xp machine copies only one record a second!
> I tried the latest ms jdbc driver as wel as the open source driver.
> No difference, same problem.
> It's a sql server 2000 Sp3.
> I disabled the xp firewall, but same result. Still to slow.
> Both machines (2000 and xp) have the same hardware: pentium IV 2.4 GHz
with
> 512MB ram.
> I'm using the sun java 1.5.0_01.
>
> Any idea?
>
> Thanks Davy
|||Inserting the records into SQL Server.
"Sue Purkis" wrote:
> Davy,
> Have you been able to narrow down at all what is slower? Is it reading
> the data from dbf or inserting into SQL Server?
> Sue Purkis
> DataDirect Technologies
> "Davy" <Davy@.discussions.microsoft.com> wrote in message
> news:5D7E84B6-DF43-41C3-98FA-6C9AF13E34C8@.microsoft.com...
> 2000.
> with
>
>
When I run the program on a Windows 2000 SP4 everything works fine. When
I run the same program on a Windows XP SP2 the program is really slow.
Haven't tried the program on a xp without sp 2. Maybe SP is the problem.
Anyone had the same problem?
On a 2000 machine the program copies up to 100 records a second. The same
program running on a xp machine copies only one record a second!
I tried the latest ms jdbc driver as wel as the open source driver.
No difference, same problem.
It's a sql server 2000 Sp3.
I disabled the xp firewall, but same result. Still to slow.
Both machines (2000 and xp) have the same hardware: pentium IV 2.4 GHz with
512MB ram.
I'm using the sun java 1.5.0_01.
Any idea?
Thanks Davy
Davy,
Have you been able to narrow down at all what is slower? Is it reading
the data from dbf or inserting into SQL Server?
Sue Purkis
DataDirect Technologies
"Davy" <Davy@.discussions.microsoft.com> wrote in message
news:5D7E84B6-DF43-41C3-98FA-6C9AF13E34C8@.microsoft.com...
> I wrote a conversion program to convert some dbf data to an sql server
2000.
> When I run the program on a Windows 2000 SP4 everything works fine. When
> I run the same program on a Windows XP SP2 the program is really slow.
> Haven't tried the program on a xp without sp 2. Maybe SP is the problem.
> Anyone had the same problem?
> On a 2000 machine the program copies up to 100 records a second. The same
> program running on a xp machine copies only one record a second!
> I tried the latest ms jdbc driver as wel as the open source driver.
> No difference, same problem.
> It's a sql server 2000 Sp3.
> I disabled the xp firewall, but same result. Still to slow.
> Both machines (2000 and xp) have the same hardware: pentium IV 2.4 GHz
with
> 512MB ram.
> I'm using the sun java 1.5.0_01.
>
> Any idea?
>
> Thanks Davy
|||Inserting the records into SQL Server.
"Sue Purkis" wrote:
> Davy,
> Have you been able to narrow down at all what is slower? Is it reading
> the data from dbf or inserting into SQL Server?
> Sue Purkis
> DataDirect Technologies
> "Davy" <Davy@.discussions.microsoft.com> wrote in message
> news:5D7E84B6-DF43-41C3-98FA-6C9AF13E34C8@.microsoft.com...
> 2000.
> with
>
>
Subscribe to:
Posts (Atom)