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 to delete the old baks

How do I write a job to delete the old backup files. I do not want to use
Maintenance plan. I would like to write a sql job to perform this activity.
Is there any procedure for this
ThanksHi,
Have a look into the old post:-
http://groups.google.co.in/group/microsoft.public.sqlserver.server/browse_thread/thread/49bf865107573571/93d87002b75a5a0f?q=sql+server+script+to+delete+old+backup+files&rnum=6&hl=en#93d87002b75a5a0f
Thanks
Hari
SQL Server MVP
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:256D7D14-D04F-4195-A065-CA920A1F9AC6@.microsoft.com...
> How do I write a job to delete the old backup files. I do not want to use
> Maintenance plan. I would like to write a sql job to perform this
> activity.
> Is there any procedure for this
> Thanks

job to delete the old baks

How do I write a job to delete the old backup files. I do not want to use
Maintenance plan. I would like to write a sql job to perform this activity.
Is there any procedure for this
ThanksHi,
Have a look into the old post:-
http://groups.google.co.in/group/mi...3d87002b75a5a0f
Thanks
Hari
SQL Server MVP
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:256D7D14-D04F-4195-A065-CA920A1F9AC6@.microsoft.com...
> How do I write a job to delete the old backup files. I do not want to use
> Maintenance plan. I would like to write a sql job to perform this
> activity.
> Is there any procedure for this
> Thanks

job to delete the old baks

How do I write a job to delete the old backup files. I do not want to use
Maintenance plan. I would like to write a sql job to perform this activity.
Is there any procedure for this
Thanks
Hi,
Have a look into the old post:-
http://groups.google.co.in/group/mic...d87002b75a5a0f
Thanks
Hari
SQL Server MVP
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:256D7D14-D04F-4195-A065-CA920A1F9AC6@.microsoft.com...
> How do I write a job to delete the old backup files. I do not want to use
> Maintenance plan. I would like to write a sql job to perform this
> activity.
> Is there any procedure for this
> Thanks

Job status is not updating

Hello all,
I have a strange and annoying problem with SQL Server 2000 SP3
(I can't upgrade to SP4 at this moment.)
I have a maintenance plan consisting of three scheduled jobs.
Starting couple of weeks ago the Agent fails to update the
last execution time and job outcome for these jobs.
I thought the problem was with the maintenance palns - as I added
extra steps - but recreating the plans did not help.
The jobs are executed - I can see the job outcomes in a form of
created backup files. The Maintenance Plan History also confirms
the successful execution.
But the last run date/time is 0 and the job outcome is 5 - unknown.
Both the Server and the Agent services are running under Local System
account. Agent connects with Windows integrated security.
Any ideas why and what needs to be done to fix?
TIA,
Y.S.Hello,
We found that similar issue is caused by that the TCP/IP Port had a
different values in the Client and the Server Network utilities.
I suggest that you make same port settings for server/client netowrk
utility and restart the SQL Server /Agent services to test the situation.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Peter Yang [MSFT] wrote:
> Hello,
> We found that similar issue is caused by that the TCP/IP Port had a
> different values in the Client and the Server Network utilities.
> I suggest that you make same port settings for server/client netowrk
> utility and restart the SQL Server /Agent services to test the situation.
In fact, the ports were exactly the same, that is 1433, but
thank you anyway - I will be aware of such problem.
However, I was able to fix the problem by connecting the Agent with
'sa' account (it was using integrated security running under Local System.)
Strange, as I never before (and we have 40+ SQL servers in the
datacenter, many of which use integrated security) experienced
anything like this.
Again, the problem was only in status update - the job itself was
executed successfully.
YuraS

> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hello,
You mean you connect to SQL server from EM as sa and and the issue does not
exist? Or you mean you start SQL agent service as a domain account with
system admin role?
Based on my further research, I found the following known issue about job
status
831366 PRB: Job Status is Not Refreshed When You Connect Using Incorrect
Case for the Login Name
http://support.microsoft.com/defaul...kb;EN-US;831366
Please check if this apply to your situation
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.sql

Job status is not updating

Hello all,
I have a strange and annoying problem with SQL Server 2000 SP3
(I can't upgrade to SP4 at this moment.)
I have a maintenance plan consisting of three scheduled jobs.
Starting couple of weeks ago the Agent fails to update the
last execution time and job outcome for these jobs.
I thought the problem was with the maintenance palns - as I added
extra steps - but recreating the plans did not help.
The jobs are executed - I can see the job outcomes in a form of
created backup files. The Maintenance Plan History also confirms
the successful execution.
But the last run date/time is 0 and the job outcome is 5 - unknown.
Both the Server and the Agent services are running under Local System
account. Agent connects with Windows integrated security.
Any ideas why and what needs to be done to fix?
TIA,
Y.S.Hello,
We found that similar issue is caused by that the TCP/IP Port had a
different values in the Client and the Server Network utilities.
I suggest that you make same port settings for server/client netowrk
utility and restart the SQL Server /Agent services to test the situation.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Peter Yang [MSFT] wrote:
> Hello,
> We found that similar issue is caused by that the TCP/IP Port had a
> different values in the Client and the Server Network utilities.
> I suggest that you make same port settings for server/client netowrk
> utility and restart the SQL Server /Agent services to test the situation.
In fact, the ports were exactly the same, that is 1433, but
thank you anyway - I will be aware of such problem.
However, I was able to fix the problem by connecting the Agent with
'sa' account (it was using integrated security running under Local System.)
Strange, as I never before (and we have 40+ SQL servers in the
datacenter, many of which use integrated security) experienced
anything like this.
Again, the problem was only in status update - the job itself was
executed successfully.
YuraS
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello,
You mean you connect to SQL server from EM as sa and and the issue does not
exist? Or you mean you start SQL agent service as a domain account with
system admin role?
Based on my further research, I found the following known issue about job
status
831366 PRB: Job Status is Not Refreshed When You Connect Using Incorrect
Case for the Login Name
http://support.microsoft.com/default.aspx?scid=kb;EN-US;831366
Please check if this apply to your situation
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Job status is not updating

Hello all,
I have a strange and annoying problem with SQL Server 2000 SP3
(I can't upgrade to SP4 at this moment.)
I have a maintenance plan consisting of three scheduled jobs.
Starting couple of weeks ago the Agent fails to update the
last execution time and job outcome for these jobs.
I thought the problem was with the maintenance palns - as I added
extra steps - but recreating the plans did not help.
The jobs are executed - I can see the job outcomes in a form of
created backup files. The Maintenance Plan History also confirms
the successful execution.
But the last run date/time is 0 and the job outcome is 5 - unknown.
Both the Server and the Agent services are running under Local System
account. Agent connects with Windows integrated security.
Any ideas why and what needs to be done to fix?
TIA,
Y.S.
Hello,
We found that similar issue is caused by that the TCP/IP Port had a
different values in the Client and the Server Network utilities.
I suggest that you make same port settings for server/client netowrk
utility and restart the SQL Server /Agent services to test the situation.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Peter Yang [MSFT] wrote:
> Hello,
> We found that similar issue is caused by that the TCP/IP Port had a
> different values in the Client and the Server Network utilities.
> I suggest that you make same port settings for server/client netowrk
> utility and restart the SQL Server /Agent services to test the situation.
In fact, the ports were exactly the same, that is 1433, but
thank you anyway - I will be aware of such problem.
However, I was able to fix the problem by connecting the Agent with
'sa' account (it was using integrated security running under Local System.)
Strange, as I never before (and we have 40+ SQL servers in the
datacenter, many of which use integrated security) experienced
anything like this.
Again, the problem was only in status update - the job itself was
executed successfully.
YuraS

> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hello,
You mean you connect to SQL server from EM as sa and and the issue does not
exist? Or you mean you start SQL agent service as a domain account with
system admin role?
Based on my further research, I found the following known issue about job
status
831366PRB: Job Status is Not Refreshed When You Connect Using Incorrect
Case for the Login Name
http://support.microsoft.com/default...b;EN-US;831366
Please check if this apply to your situation
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

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

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

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

Job fails but no info on why

I'm running a maintenance plan and the job the does the integrity checks
fails, but in the job history log it gives no useful information as to why.
I can guess that there's something wrong with a relationship, index, or
missing data for the relationship, but this is a big db and it would be nice
if its going to fail, it could at least tell me why. For example, when
running a DTS, it there's an integrity issue, it will fail and tell me why.
Is there a stored procedure I can run that would list any issues that would
make this job fail? Any other advise?
Thanks.
moondaddy@.nospam.nospamIn Enterprise Manager, right click on the Maintenance plan (not the job) and
select 'Maintenance plan history' from the context menu.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"moondaddy" <moondaddy@.nospam.nospam> wrote in message
news:OsYC7GHuFHA.1252@.TK2MSFTNGP09.phx.gbl...
I'm running a maintenance plan and the job the does the integrity checks
fails, but in the job history log it gives no useful information as to why.
I can guess that there's something wrong with a relationship, index, or
missing data for the relationship, but this is a big db and it would be nice
if its going to fail, it could at least tell me why. For example, when
running a DTS, it there's an integrity issue, it will fail and tell me why.
Is there a stored procedure I can run that would list any issues that would
make this job fail? Any other advise?
Thanks.
moondaddy@.nospam.nospam|||Look on the job steps under the advanced tab, you are able to put a log
file directory / file name in here to monitor the activity performed by
the job process.
HTH, Jens Suessmeyer.|||Thanks.
moondaddy@.nospam.nospam
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1126623715.659637.295900@.z14g2000cwz.googlegroups.com...
> Look on the job steps under the advanced tab, you are able to put a log
> file directory / file name in here to monitor the activity performed by
> the job process.
>
> HTH, Jens Suessmeyer.
>|||Thanks. that gave me the clue I needed.
moondaddy@.nospam.nospam
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:u7OpsOHuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> In Enterprise Manager, right click on the Maintenance plan (not the job)
> and
> select 'Maintenance plan history' from the context menu.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "moondaddy" <moondaddy@.nospam.nospam> wrote in message
> news:OsYC7GHuFHA.1252@.TK2MSFTNGP09.phx.gbl...
> I'm running a maintenance plan and the job the does the integrity checks
> fails, but in the job history log it gives no useful information as to
> why.
> I can guess that there's something wrong with a relationship, index, or
> missing data for the relationship, but this is a big db and it would be
> nice
> if its going to fail, it could at least tell me why. For example, when
> running a DTS, it there's an integrity issue, it will fail and tell me
> why.
> Is there a stored procedure I can run that would list any issues that
> would
> make this job fail? Any other advise?
> Thanks.
> --
> moondaddy@.nospam.nospam
>
>

job failed after domain IP address changed

I've create a maintenance plan using the SQLserver Wizard.

The jobs work correctly until we change the IP address of the server with the following trace :

The job failed. Unable to determine if the owner (DOMAIN\Login) of job Transaction Log Backup Job for DB Maintenance Plan 'BASE' has server access (reason: Could not obtain information about Windows NT group/user 'DOMAIN\login'. [SQLSTATE 42000] (Error 8198)).

Can somebpody explain me what occured ?Howdy

I have seen this a lot on SQL 7 and it seems the NT authentication/ SQL interface neve rreally worked properly.

Easiest way ( and usually the best way of going anyway ) is get sa to own all jobs, then unless its a very secure server, all will run fine and if you change the IP adderess of the server, as sa is local, it wont make any difference.

Cheers

SG.

Job Change History

Anyone know how to tell who is changing a db maintenance plan? I found
the system tables in msdb but I don't see how to tell who is doing it.
Only when.
On Feb 26, 2:29Xam, songstre <songs...@.gmail.com> wrote:
> Anyone know how to tell who is changing a db maintenance plan? I found
> the system tables in msdb but I don't see how to tell who is doing it.
> Only when.
Please check who has invoked the job last time if somebody is
modifying the job the he may want to execute also . With the help of
job history you can find who has invoked the job last time.
Assuming the person doesn't have direct access to server.
Thanks
Ajay Rengunthwar
MCTS
|||On Feb 25, 11:03Xpm, Ajay Rengunthwar <aju...@.gmail.com> wrote:
> On Feb 26, 2:29Xam, songstre <songs...@.gmail.com> wrote:
>
> Please check who has invoked the job last time if somebody is
> modifying the job the he may want to execute also . With the help of
> job history you can find who has invoked the job last time.
> Assuming the person doesn't have direct access to server.
> Thanks
> Ajay Rengunthwar
> MCTS
It was last executed by NT AUTHORITY\SYSTEM on a schedule. So this
doesn't help much.
|||Hi,
I'm always wondering is it possible to check?
Regards,
anxcomp

Job Change History

Anyone know how to tell who is changing a db maintenance plan? I found
the system tables in msdb but I don't see how to tell who is doing it.
Only when.On Feb 26, 2:29=A0am, songstre <songs...@.gmail.com> wrote:
> Anyone know how to tell who is changing a db maintenance plan? I found
> the system tables in msdb but I don't see how to tell who is doing it.
> Only when.
Please check who has invoked the job last time if somebody is
modifying the job the he may want to execute also . With the help of
job history you can find who has invoked the job last time.
Assuming the person doesn't have direct access to server.
Thanks
Ajay Rengunthwar
MCTS|||On Feb 25, 11:03=A0pm, Ajay Rengunthwar <aju...@.gmail.com> wrote:
> On Feb 26, 2:29=A0am, songstre <songs...@.gmail.com> wrote:
> > Anyone know how to tell who is changing a db maintenance plan? I found
> > the system tables in msdb but I don't see how to tell who is doing it.
> > Only when.
> Please check who has invoked the job last time if somebody is
> modifying the job the he may want to execute also . With the help of
> job history you can find who has invoked the job last time.
> Assuming the person doesn't have direct access to server.
> Thanks
> Ajay Rengunthwar
> MCTS
It was last executed by NT AUTHORITY\SYSTEM on a schedule. So this
doesn't help much.|||Hi,
I'm always wondering is it possible to check?
--
Regards,
anxcomp