Job View History is disappearing

I've created new maintenance plans and while I can view the maintenance plan
history, the job view history is deleted somehow. Where is this controlled?
The maintenance plan is not deleting job history and I don't see any other
jobs or maintenance plans that are deleting history.
This is SQL2005 SE SP2. I remember in SQL2000 you could limit the size of
the job history, but I don't see that in 2005. Is there a default somewhere
I'm missing?
Thanks
RonRon,
In SSMS, right-click on SQL Agent, select Properties, go to the History
page. This is where you can set the size of history.
RLF
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0EB1F324-BF2B-4F24-87AC-585FAB4071E3@.microsoft.com...
> I've created new maintenance plans and while I can view the maintenance
> plan
> history, the job view history is deleted somehow. Where is this
> controlled?
> The maintenance plan is not deleting job history and I don't see any
> other
> jobs or maintenance plans that are deleting history.
> This is SQL2005 SE SP2. I remember in SQL2000 you could limit the size of
> the job history, but I don't see that in 2005. Is there a default
> somewhere
> I'm missing?
> Thanks
> Ron|||Thanks - I'll bump it up and see if that's the issue.
"Russell Fields" wrote:
> Ron,
> In SSMS, right-click on SQL Agent, select Properties, go to the History
> page. This is where you can set the size of history.
> RLF
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:0EB1F324-BF2B-4F24-87AC-585FAB4071E3@.microsoft.com...
> > I've created new maintenance plans and while I can view the maintenance
> > plan
> > history, the job view history is deleted somehow. Where is this
> > controlled?
> > The maintenance plan is not deleting job history and I don't see any
> > other
> > jobs or maintenance plans that are deleting history.
> >
> > This is SQL2005 SE SP2. I remember in SQL2000 you could limit the size of
> > the job history, but I don't see that in 2005. Is there a default
> > somewhere
> > I'm missing?
> >
> > Thanks
> >
> > Ron
>
>

Job Scheduler Question - 2005

I have a scheduled job for a DTS package and was reviewing the history, but
it only keeps 50 events listed in the history. Is there any way to expand
this or a query that can be used to extract more history about the executed
jobs?
Yes I am talking about the history and know how to access it. I was asking if
there was a way to expand the number of executions or having a way to look
past the list of 50.
"Tibor Karaszi" wrote:

> Are you talking of how many executions it has history for? If so, Right-click Agent, Properties,
> History.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Big Ern" <BigErn@.discussions.microsoft.com> wrote in message
> news:9528FF37-5BD1-4CA5-AFC5-B7EC2F3D0AE0@.microsoft.com...
>
>

Job reports failure, but bak file created

The view history of a job created from a maintenance plan reports that it
failed, and the log says that it failed in the backup operation because
"A nonrecoverable I/O error occurred on file
"\\\\BIGPETE\\E\\data\\Backups\\Daily\\MCR_SFPMS\\MCR_SFPMS_backup_200707110001.bak:" 64(The specified network name is no longer available.).
And yet MCR_SFPMS_backup_200707110001.bak does exist, and moreover, it was a
valid backup, as I just confirmed by restoring it to a test database. So why
the error?
Also, this does not happen consistently. The reporting of this apparent
non-error has occured twice out of the last five runs.Does the backup process include a Verify? IF so the error is most likely
occurring on the verify process which is done after the backup is complete.
Backup & Verify is an intensive operation and if you are backing up across
the network there is plenty of time for the network to drop packets etc.
Sounds like you have an unstable network or networks equipment.
--
Andrew J. Kelly SQL MVP
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:AF302871-2972-420D-A932-408F42EA1EA9@.microsoft.com...
> The view history of a job created from a maintenance plan reports that it
> failed, and the log says that it failed in the backup operation because
> "A nonrecoverable I/O error occurred on file
> "\\\\BIGPETE\\E\\data\\Backups\\Daily\\MCR_SFPMS\\MCR_SFPMS_backup_200707110001.bak:"
> 64(The specified network name is no longer available.).
> And yet MCR_SFPMS_backup_200707110001.bak does exist, and moreover, it was
> a
> valid backup, as I just confirmed by restoring it to a test database. So
> why
> the error?
> Also, this does not happen consistently. The reporting of this apparent
> non-error has occured twice out of the last five runs.|||On Jul 11, 9:16 am, Bev Kaufman <BevKauf...@.discussions.microsoft.com>
wrote:
> The view history of a job created from a maintenance plan reports that it
> failed, and the log says that it failed in the backup operation because
> "A nonrecoverable I/O error occurred on file
> "\\\\BIGPETE\\E\\data\\Backups\\Daily\\MCR_SFPMS\\MCR_SFPMS_backup_200707=11=AD0001.bak:" 64(The specified network name is no longer available.).
> And yet MCR_SFPMS_backup_200707110001.bak does exist, and moreover, it wa=s a
> valid backup, as I just confirmed by restoring it to a test database. So= why
> the error?
> Also, this does not happen consistently. The reporting of this apparent
> non-error has occured twice out of the last five runs.
Are you utilizing a third party backup tool? We use RedGate in our
SQL Server 2000 (SP3a - don't ask) environment and have encountered
this on a daily basis. When we used normal backup procedures, no
errors were reported. I have the same issue though, but figured it
might be resolved with a newer service pack. The only thing I can
think of is possibly disk contention of you are utilizing a SAN
environment, but not sure.
Maybe if we put our heads together, we can figure it out. I haven't
put a whole lot of effort into it once I realized that the backups are
working properly (as verified by the restores). I know, terrible, but
sometimes other fires are hotter :)

Job History report

Hello all,

I need to create a report on a certain Job on my server. It should look exactly like the "View History" window.

to do that, i need to find a way to group the logged steps by the job executions. The problem is that in the sysjobhistory table there's only a instance_id field that runs incrementally, with no way to identify the execution…

any ideas?

You'll need to join so sysjobs and sysoperators if you want the Job Name and Operators that were emailed, net sent or paged.

You might be able to group the run_date and run_time together and use this for your "key" to group the job steps together. Note: run_time is in a 24 hour format and is not left zero filled (i.e. 10001 is 1:00:01 am, 152531 is 15:25:31 pm).

convert(varchar(8), run_date) + right('000000' + convert(varchar(6), run_time), 6) -->> will give you the date and hhmmss the step executed (i.e. 20061225010001).

You could parse the results (maybe using hours and/or minutes) to group the steps together.

The above is a quick and dirty and depending upon how often and how long your jobs run, may not work.

Since the format of job history is consitent with step_id of 0 as the job completion step, you can write a stored procedure and use a cursor to group the steps (read all the steps and "break" each time a step_id of 0 is encountered).

|||

Thanks,

The first way you wrote will not work since you cannot group on Running Date & Time - this group will not identify all the steps in a certain run in the same group.

I also thought of the second way, but it seemed too costly.

How does the "view job history" window know how to separate the job executions?

|||

Liran, that is why I wrote that the first step "may not work". If your job ran once each hour, started at 1 minute past the hour and ran for less than 59 minutes every time, the first method would work since each step would have the same date and hour time stamp. Obviously this won't work if your job starts at 59 minutes after the hour and runs for more than one minute or runs for more than one hour.

Not sure how the "view job history" works but would imagine it does a similar activity as what I described for writing a stored procedure. Since you can't have the same job name executing at the same time on the same server, the starting date/time of the job and each of it's steps will be in chronological order; doesn't matter if the job runs more than one our or spans over multiple days.

Another option might be to add the duration (from step_id = 0) to the start date/time to get the ending date/time of the job (NOT the steps). You could then choose any of the rows in sysjobhistory that fall between the start date/time and the calculated ending date/time of the job.

You said you had an idea, why don't you describe it and we might be able to expand upon it...

|||

OK, Now I understood what you meant, but this method is not relevant to me since this job runs for more then 1 hour with 1 min between each run…

About the second Idea, I meant I also thought of the second idea you wrote (with the procedure and cursor) but it seems to me too costly to be executed every time the report is viewed…

Thanks, I just thought there's somewhere a "job running instance id" that I'm overlooking…

|||

Pls chek the following post..It may help you

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1033004&SiteID=1

Job History Logs

Hi,
Maximum Job History Log size is set to 1000 and Maximum Job History Rows
Per Job is set to 100. There are some some jobs that are running every
minute. In less then five minute the Log Size grows to 45 from 0. So I guess
the jobs that are running every minute are filling up the History Log. When
other jobs that run once every night are not able to write in the History
Log. What should be the preferred setting in such scenario that will allow
the nightly jobs to write history in the History Log.
I can think of two options. I can either uncheck the Limit Size of Job
History Log option or change the value of the Maximum Job History Log Size
option. But then I don't know how large the History Log will grow. Can you
give me an idea on that? Also is there a way to automatically clear the
History Log?
Thanks in advance...
If you have n number of jobs then set history log size to (n * 100) +
100.
Set maximum number of rows per job to 100.
Regards
Amish Shah.

Job History Logs

Hi,
Maximum Job History Log size is set to 1000 and Maximum Job History Rows
Per Job is set to 100. There are some some jobs that are running every
minute. In less then five minute the Log Size grows to 45 from 0. So I guess
the jobs that are running every minute are filling up the History Log. When
other jobs that run once every night are not able to write in the History
Log. What should be the preferred setting in such scenario that will allow
the nightly jobs to write history in the History Log.
I can think of two options. I can either uncheck the Limit Size of Job
History Log option or change the value of the Maximum Job History Log Size
option. But then I don't know how large the History Log will grow. Can you
give me an idea on that? Also is there a way to automatically clear the
History Log?
Thanks in advance...If you have n number of jobs then set history log size to (n * 100) +
100.
Set maximum number of rows per job to 100.
Regards
Amish Shah.sql

Job History Logs

Hi,
Maximum Job History Log size is set to 1000 and Maximum Job History Rows
Per Job is set to 100. There are some some jobs that are running every
minute. In less then five minute the Log Size grows to 45 from 0. So I guess
the jobs that are running every minute are filling up the History Log. When
other jobs that run once every night are not able to write in the History
Log. What should be the preferred setting in such scenario that will allow
the nightly jobs to write history in the History Log.
I can think of two options. I can either uncheck the Limit Size of Job
History Log option or change the value of the Maximum Job History Log Size
option. But then I don't know how large the History Log will grow. Can you
give me an idea on that? Also is there a way to automatically clear the
History Log?
Thanks in advance...If you have n number of jobs then set history log size to (n * 100) +
100.
Set maximum number of rows per job to 100.
Regards
Amish Shah.

Job History Log overflow

Hi,
Maximum Job History Log size is set to 1000 and Maximum Job History Rows
Per Job is set to 100. There are some some jobs that are running every
minute. In less then five minute the Log Size grows to 45 from 0. So I guess
the jobs that are running every minute are filling up the History Log. When
other jobs that run once every night are not able to write in the History
Log. What should be the preferred setting in such scenario that will allow
the nightly jobs to write history in the History Log.
I can think of two options. I can either uncheck the Limit Size of Job
History Log option or change the value of the Maximum Job History Log Size
option. But then I don't know how large the History Log will grow. Can you
give me an idea on that? Also is there a way to automatically clear the
History Log?
Thanks in advance...You can set to the job history log size to unlimited. There are no known
issues associated with it -- though it might be a good idea to archive the
data from msdb..sysjobhistory for auditing purposes. Once you set a size
limit, the entire log rows outside the limit can be purged easily.
Anith|||> Also is there a way to automatically clear the History Log?
See sp_purge_jobhistory in BOL.
AMB
"helpful sql" wrote:

> Hi,
> Maximum Job History Log size is set to 1000 and Maximum Job History Row
s
> Per Job is set to 100. There are some some jobs that are running every
> minute. In less then five minute the Log Size grows to 45 from 0. So I gue
ss
> the jobs that are running every minute are filling up the History Log. Whe
n
> other jobs that run once every night are not able to write in the History
> Log. What should be the preferred setting in such scenario that will allow
> the nightly jobs to write history in the History Log.
> I can think of two options. I can either uncheck the Limit Size of Job
> History Log option or change the value of the Maximum Job History Log Size
> option. But then I don't know how large the History Log will grow. Can you
> give me an idea on that? Also is there a way to automatically clear the
> History Log?
> Thanks in advance...
>
>

Job History is not showing

Using Sql 2005, I have a job that will not display job history when it runs
at the schedule time. It will show job history if you manually run the job.
Any thoughts?
Are you sure that the history settings are not such that it gets purged by
the time you view it?
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
> Using Sql 2005, I have a job that will not display job history when it
> runs
> at the schedule time. It will show job history if you manually run the
> job.
> Any thoughts?
|||The Limit Size of Job History Log is checked with Max Log size of 1000 and
Max Job history is 105. When I looked at the job history for all it showed
only 212 entries.
This particular server is the distributor. The only jobs on the server that
do show history deal with the replication.
"Andrew J. Kelly" wrote:

> Are you sure that the history settings are not such that it gets purged by
> the time you view it?
> --
> Andrew J. Kelly SQL MVP
> "Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
> news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
>
>
|||OK my guess is that the history limit is beeing exceeded by one of the jobs
that runs say every minute or so. You can try bumping up the history limits
but it depends on how often the other jobs run.
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:A1B7DE7A-B3CD-4C05-B971-46A00161624B@.microsoft.com...[vbcol=seagreen]
> The Limit Size of Job History Log is checked with Max Log size of 1000 and
> Max Job history is 105. When I looked at the job history for all it
> showed
> only 212 entries.
> This particular server is the distributor. The only jobs on the server
> that
> do show history deal with the replication.
> "Andrew J. Kelly" wrote:

Job History is not showing

Using Sql 2005, I have a job that will not display job history when it runs
at the schedule time. It will show job history if you manually run the job.
Any thoughts?Are you sure that the history settings are not such that it gets purged by
the time you view it?
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
> Using Sql 2005, I have a job that will not display job history when it
> runs
> at the schedule time. It will show job history if you manually run the
> job.
> Any thoughts?|||The Limit Size of Job History Log is checked with Max Log size of 1000 and
Max Job history is 105. When I looked at the job history for all it showed
only 212 entries.
This particular server is the distributor. The only jobs on the server that
do show history deal with the replication.
"Andrew J. Kelly" wrote:

> Are you sure that the history settings are not such that it gets purged by
> the time you view it?
> --
> Andrew J. Kelly SQL MVP
> "Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
> news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
>
>|||OK my guess is that the history limit is beeing exceeded by one of the jobs
that runs say every minute or so. You can try bumping up the history limits
but it depends on how often the other jobs run.
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:A1B7DE7A-B3CD-4C05-B971-46A00161624B@.microsoft.com...[vbcol=seagreen]
> The Limit Size of Job History Log is checked with Max Log size of 1000 and
> Max Job history is 105. When I looked at the job history for all it
> showed
> only 212 entries.
> This particular server is the distributor. The only jobs on the server
> that
> do show history deal with the replication.
> "Andrew J. Kelly" wrote:
>

Job History is not showing

Using Sql 2005, I have a job that will not display job history when it runs
at the schedule time. It will show job history if you manually run the job.
Any thoughts?Are you sure that the history settings are not such that it gets purged by
the time you view it?
--
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
> Using Sql 2005, I have a job that will not display job history when it
> runs
> at the schedule time. It will show job history if you manually run the
> job.
> Any thoughts?|||The Limit Size of Job History Log is checked with Max Log size of 1000 and
Max Job history is 105. When I looked at the job history for all it showed
only 212 entries.
This particular server is the distributor. The only jobs on the server that
do show history deal with the replication.
"Andrew J. Kelly" wrote:
> Are you sure that the history settings are not such that it gets purged by
> the time you view it?
> --
> Andrew J. Kelly SQL MVP
> "Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
> news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
> > Using Sql 2005, I have a job that will not display job history when it
> > runs
> > at the schedule time. It will show job history if you manually run the
> > job.
> >
> > Any thoughts?
>
>|||OK my guess is that the history limit is beeing exceeded by one of the jobs
that runs say every minute or so. You can try bumping up the history limits
but it depends on how often the other jobs run.
--
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:A1B7DE7A-B3CD-4C05-B971-46A00161624B@.microsoft.com...
> The Limit Size of Job History Log is checked with Max Log size of 1000 and
> Max Job history is 105. When I looked at the job history for all it
> showed
> only 212 entries.
> This particular server is the distributor. The only jobs on the server
> that
> do show history deal with the replication.
> "Andrew J. Kelly" wrote:
>> Are you sure that the history settings are not such that it gets purged
>> by
>> the time you view it?
>> --
>> Andrew J. Kelly SQL MVP
>> "Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
>> news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
>> > Using Sql 2005, I have a job that will not display job history when it
>> > runs
>> > at the schedule time. It will show job history if you manually run the
>> > job.
>> >
>> > Any thoughts?
>>sql

Job History is missing

I've come into a new SQL server 200o environment and for some reason, none of
the jobs keeps a history past one day. The current production DBA says he has
no idea what's causing it. Does anyone have any experience with this issue?
Thanks in advance.See if the size of the job history has been limited. This can be found in
Enterprise Manager by selecting your server, going to Management, then right
click on SQL Server Agent and select properties. The Job System tab has
options for limiting the size of the job history log.
--
Ben Lucas
Lead Developer
Solien Technology, Inc.
www.solien.com
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:881853B8-2F60-4017-8F01-44FBF863BFAF@.microsoft.com...
> I've come into a new SQL server 200o environment and for some reason, none
> of
> the jobs keeps a history past one day. The current production DBA says he
> has
> no idea what's causing it. Does anyone have any experience with this
> issue?
> Thanks in advance.|||Thanks Ben, that was fast. I think that will do it. Thanks again.
Ed
"Ben Lucas" wrote:
> See if the size of the job history has been limited. This can be found in
> Enterprise Manager by selecting your server, going to Management, then right
> click on SQL Server Agent and select properties. The Job System tab has
> options for limiting the size of the job history log.
> --
> Ben Lucas
> Lead Developer
> Solien Technology, Inc.
> www.solien.com
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:881853B8-2F60-4017-8F01-44FBF863BFAF@.microsoft.com...
> > I've come into a new SQL server 200o environment and for some reason, none
> > of
> > the jobs keeps a history past one day. The current production DBA says he
> > has
> > no idea what's causing it. Does anyone have any experience with this
> > issue?
> >
> > Thanks in advance.
>
>

Job History is missing

I've come into a new SQL server 200o environment and for some reason, none of
the jobs keeps a history past one day. The current production DBA says he has
no idea what's causing it. Does anyone have any experience with this issue?
Thanks in advance.
See if the size of the job history has been limited. This can be found in
Enterprise Manager by selecting your server, going to Management, then right
click on SQL Server Agent and select properties. The Job System tab has
options for limiting the size of the job history log.
Ben Lucas
Lead Developer
Solien Technology, Inc.
www.solien.com
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:881853B8-2F60-4017-8F01-44FBF863BFAF@.microsoft.com...
> I've come into a new SQL server 200o environment and for some reason, none
> of
> the jobs keeps a history past one day. The current production DBA says he
> has
> no idea what's causing it. Does anyone have any experience with this
> issue?
> Thanks in advance.
|||Thanks Ben, that was fast. I think that will do it. Thanks again.
Ed
"Ben Lucas" wrote:

> See if the size of the job history has been limited. This can be found in
> Enterprise Manager by selecting your server, going to Management, then right
> click on SQL Server Agent and select properties. The Job System tab has
> options for limiting the size of the job history log.
> --
> Ben Lucas
> Lead Developer
> Solien Technology, Inc.
> www.solien.com
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:881853B8-2F60-4017-8F01-44FBF863BFAF@.microsoft.com...
>
>

Job History hangs Enterprise Manager

Hi
I have Enterprise Manager on my local machine and administer a server from
it. When I go to jobs and right click and choose view job history I get an
hour glass and then EM Hangs and also pretty much kills my machine.
Has this happened to anyone else? Is there a reason and/or fix for it?
It only seems to happen on one server and I administer several others from
my machine with no problem. I am running SQL Server 2000 SP4
I have google but couldn't find anything helpfull.
ThanksHi
I am not sure about your particular problem you may want to use SQL Profiler
to check out what SQL it is running and/or run sp_help_jobhistory from Query
Analyser. If you have a very long job history you could run
sp_purge_jobhistory to remove it.
Other things you may want to do is to run DBCC CHECKDB on MSDB.
John
"Bri" wrote:
> Hi
> I have Enterprise Manager on my local machine and administer a server from
> it. When I go to jobs and right click and choose view job history I get an
> hour glass and then EM Hangs and also pretty much kills my machine.
> Has this happened to anyone else? Is there a reason and/or fix for it?
> It only seems to happen on one server and I administer several others from
> my machine with no problem. I am running SQL Server 2000 SP4
> I have google but couldn't find anything helpfull.
> Thanks
>|||Bri wrote:
> Hi
> I have Enterprise Manager on my local machine and administer a server from
> it. When I go to jobs and right click and choose view job history I get an
> hour glass and then EM Hangs and also pretty much kills my machine.
> Has this happened to anyone else? Is there a reason and/or fix for it?
> It only seems to happen on one server and I administer several others from
> my machine with no problem. I am running SQL Server 2000 SP4
> I have google but couldn't find anything helpfull.
> Thanks
>
When it "hangs", open up Query Analyzer and look at sysprocesses on the
server, see if there is something blocking you, or if there is a
waittype specified for your spid. You could just have an exceptionally
long history for the given job.

Job History hangs Enterprise Manager

Hi
I am not sure about your particular problem you may want to use SQL Profiler
to check out what SQL it is running and/or run sp_help_jobhistory from Query
Analyser. If you have a very long job history you could run
sp_purge_jobhistory to remove it.
Other things you may want to do is to run DBCC CHECKDB on MSDB.
John
"Bri" wrote:

> Hi
> I have Enterprise Manager on my local machine and administer a server from
> it. When I go to jobs and right click and choose view job history I get an
> hour glass and then EM Hangs and also pretty much kills my machine.
> Has this happened to anyone else? Is there a reason and/or fix for it?
> It only seems to happen on one server and I administer several others from
> my machine with no problem. I am running SQL Server 2000 SP4
> I have google but couldn't find anything helpfull.
> Thanks
>Hi
I have Enterprise Manager on my local machine and administer a server from
it. When I go to jobs and right click and choose view job history I get an
hour glass and then EM Hangs and also pretty much kills my machine.
Has this happened to anyone else? Is there a reason and/or fix for it?
It only seems to happen on one server and I administer several others from
my machine with no problem. I am running SQL Server 2000 SP4
I have google but couldn't find anything helpfull.
Thanks|||Hi
I am not sure about your particular problem you may want to use SQL Profiler
to check out what SQL it is running and/or run sp_help_jobhistory from Query
Analyser. If you have a very long job history you could run
sp_purge_jobhistory to remove it.
Other things you may want to do is to run DBCC CHECKDB on MSDB.
John
"Bri" wrote:

> Hi
> I have Enterprise Manager on my local machine and administer a server from
> it. When I go to jobs and right click and choose view job history I get an
> hour glass and then EM Hangs and also pretty much kills my machine.
> Has this happened to anyone else? Is there a reason and/or fix for it?
> It only seems to happen on one server and I administer several others from
> my machine with no problem. I am running SQL Server 2000 SP4
> I have google but couldn't find anything helpfull.
> Thanks
>|||Bri wrote:
> Hi
> I have Enterprise Manager on my local machine and administer a server from
> it. When I go to jobs and right click and choose view job history I get an
> hour glass and then EM Hangs and also pretty much kills my machine.
> Has this happened to anyone else? Is there a reason and/or fix for it?
> It only seems to happen on one server and I administer several others from
> my machine with no problem. I am running SQL Server 2000 SP4
> I have google but couldn't find anything helpfull.
> Thanks
>
When it "hangs", open up Query Analyzer and look at sysprocesses on the
server, see if there is something blocking you, or if there is a
waittype specified for your spid. You could just have an exceptionally
long history for the given job.|||Bri wrote:
> Hi
> I have Enterprise Manager on my local machine and administer a server from
> it. When I go to jobs and right click and choose view job history I get an
> hour glass and then EM Hangs and also pretty much kills my machine.
> Has this happened to anyone else? Is there a reason and/or fix for it?
> It only seems to happen on one server and I administer several others from
> my machine with no problem. I am running SQL Server 2000 SP4
> I have google but couldn't find anything helpfull.
> Thanks
>
When it "hangs", open up Query Analyzer and look at sysprocesses on the
server, see if there is something blocking you, or if there is a
waittype specified for your spid. You could just have an exceptionally
long history for the given job.

Job history - After the limitation 0f 1000 entries

Hi,
Can some tell me what the behaviour on the refreshment on the job history
when it reaches the limitation of 1000 entries?
Is it flushing all ( and start over from scratch ) or does it use FIFO (
first in first out ).
Guus Kramer
FIFO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Guus Kramer" <Guus Kramer@.discussions.microsoft.com> wrote in message
news:C24E8EF8-BCEE-4E44-94F7-8A06578504A9@.microsoft.com...
> Hi,
> Can some tell me what the behaviour on the refreshment on the job history
> when it reaches the limitation of 1000 entries?
> Is it flushing all ( and start over from scratch ) or does it use FIFO (
> first in first out ).
> Guus Kramer
|||Just wondering why the log history has vanished when no other actions have
occured. This happens on a random server and is not prodictable.
Any idea?
Guus
"Tibor Karaszi" wrote:

> FIFO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Guus Kramer" <Guus Kramer@.discussions.microsoft.com> wrote in message
> news:C24E8EF8-BCEE-4E44-94F7-8A06578504A9@.microsoft.com...
>
|||Are you telling me that you get 0 from below query?
SELECT COUNT(*) FROM msdb.dbo.sysjobhistory
If so, you should find out who it emptying the job history. If not, then you have some job which is
executing frequently enough to hit the limit in a way that the other job's history is all aged out.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
news:F9DC2715-4F0A-44C8-B8C9-84A8D054A819@.microsoft.com...[vbcol=seagreen]
> Just wondering why the log history has vanished when no other actions have
> occured. This happens on a random server and is not prodictable.
> Any idea?
> Guus
> "Tibor Karaszi" wrote:
|||The job history table is also limited by space in msdb. Expand the msdb
database manually and you will hold a longer job history.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
news:F9DC2715-4F0A-44C8-B8C9-84A8D054A819@.microsoft.com...[vbcol=seagreen]
> Just wondering why the log history has vanished when no other actions have
> occured. This happens on a random server and is not prodictable.
> Any idea?
> Guus
> "Tibor Karaszi" wrote:
|||Tibor / Geoff,
You're both right...
SELECT COUNT(*) FROM msdb.dbo.sysjobhistory
contains 1000 entries but this is serverwide. on some servers 40 to 50 jobs
are running and some of them each 5 minutes. So the job history can be aged
out ( and dissapear ) for some jobs when logging other history.
Expanding the msdb database seems to be the only option on this.
Thank you guys for giving me input on this.
Guus Kramer
The Netherlands
"Guus Kramer" wrote:
[vbcol=seagreen]
> Just wondering why the log history has vanished when no other actions have
> occured. This happens on a random server and is not prodictable.
> Any idea?
> Guus
> "Tibor Karaszi" wrote:
|||... and configure Agent to keep > 1000 rows totally...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
news:8F7E2067-45C6-48B0-85C0-00297BCD078E@.microsoft.com...[vbcol=seagreen]
> Tibor / Geoff,
> You're both right...
> SELECT COUNT(*) FROM msdb.dbo.sysjobhistory
> contains 1000 entries but this is serverwide. on some servers 40 to 50 jobs
> are running and some of them each 5 minutes. So the job history can be aged
> out ( and dissapear ) for some jobs when logging other history.
> Expanding the msdb database seems to be the only option on this.
> Thank you guys for giving me input on this.
> Guus Kramer
> The Netherlands
> "Guus Kramer" wrote:

Job History

Hello all,
Is there a way to define how much history will be logged in the Job History
window?
I need to save history from the whole last week's run, but it keeps been
deleted.
Thanks
Liran
Take a look at sysjobhistory system table
"Liran" <Liran@.discussions.microsoft.com> wrote in message
news:C03C14C4-43ED-4E97-BE97-E71FD083F331@.microsoft.com...
> Hello all,
> Is there a way to define how much history will be logged in the Job
> History
> window?
> I need to save history from the whole last week's run, but it keeps been
> deleted.
> Thanks
|||Actually, I did, but the solution is to change the dafault log size in the
SQL Server Agent Property window.
"Uri Dimant" wrote:

> Liran
> Take a look at sysjobhistory system table
> "Liran" <Liran@.discussions.microsoft.com> wrote in message
> news:C03C14C4-43ED-4E97-BE97-E71FD083F331@.microsoft.com...
>
>
|||Liran
To modify the maximum number of records maintained in sysjobhistory open
Enterprise Manager and expand the Server to modify. Expand Managment and
Right Click SQL Server Agent and select Properties.
Under the Job System tab there is two text boxes. Maximum job history log
size (rows) and Maximum job hisory rows per job.
-rows per job is the maximum number of rows in sysjobhistory for a
partilcuar job
-log size (rows) is the maximum number rows in sysjobhistory for all jobs
Updating these values will modify how many rows are maintained.
-Peter Ward
WARDY IT Solutions
"Liran" wrote:
[vbcol=seagreen]
> Actually, I did, but the solution is to change the dafault log size in the
> SQL Server Agent Property window.
>
> "Uri Dimant" wrote:

Job History

If you right click on job (in 2000) and do View History, Ent Mgr displays
(from what i can tell) only a certain number of lines. But I have a job that
runs every couple of minutes and I need to see much more history than that.
Is there somewhere this information is kept and is there a way I can change
the default on the number of history lines? Thx...
You should probably just query the DB. See MSDB.dbo.sysjobhistory
Ryan
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:BEC60862-A013-4509-80CD-2259731B5DEC@.microsoft.com...
> If you right click on job (in 2000) and do View History, Ent Mgr displays
> (from what i can tell) only a certain number of lines. But I have a job
that
> runs every couple of minutes and I need to see much more history than
that.
> Is there somewhere this information is kept and is there a way I can
change
> the default on the number of history lines? Thx...
|||Right click on the SQL Agent folder and choose properties. Then have a look
at the tabs and you will see a place to change the history retention
settings.
Andrew J. Kelly SQL MVP
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:BEC60862-A013-4509-80CD-2259731B5DEC@.microsoft.com...
> If you right click on job (in 2000) and do View History, Ent Mgr displays
> (from what i can tell) only a certain number of lines. But I have a job
> that
> runs every couple of minutes and I need to see much more history than
> that.
> Is there somewhere this information is kept and is there a way I can
> change
> the default on the number of history lines? Thx...

Job History

Is there a limit to the number of rows that are stored in job history?
I see where the Maintenance Plan history can be set, but not for the
individual jobs. I have set up a test job and run it over 100 times, but
there seems to be a limit of 100 entries in the sysjobhistory table.
Is this correct and if it is, where is that documented?
Thanks,
Will
Never Mind - I see that the setting is in the SQL Server Agent properties.
"Will Winn" wrote:

> Is there a limit to the number of rows that are stored in job history?
> I see where the Maintenance Plan history can be set, but not for the
> individual jobs. I have set up a test job and run it over 100 times, but
> there seems to be a limit of 100 entries in the sysjobhistory table.
> Is this correct and if it is, where is that documented?
> Thanks,
> Will

Job History

Is there a limit to the number of rows that are stored in job history?
I see where the Maintenance Plan history can be set, but not for the
individual jobs. I have set up a test job and run it over 100 times, but
there seems to be a limit of 100 entries in the sysjobhistory table.
Is this correct and if it is, where is that documented?
Thanks,
WillNever Mind - I see that the setting is in the SQL Server Agent properties.
"Will Winn" wrote:

> Is there a limit to the number of rows that are stored in job history?
> I see where the Maintenance Plan history can be set, but not for the
> individual jobs. I have set up a test job and run it over 100 times, but
> there seems to be a limit of 100 entries in the sysjobhistory table.
> Is this correct and if it is, where is that documented?
> Thanks,
> Will