msdb's sysjobs tables

Please list the cases when the contents of sysjobhistory table would be
deleted.
thanks
--
Cathy BOne way
from SQL server agent--> Properties-->Job System click on Clear Log
http://sqlservercode.blogspot.com/|||Yes,
But I am looking as to why there's no history on a particular job.
Any other ways?
--
Cathy B
"SQL" wrote:

> One way
> from SQL server agent--> Properties-->Job System click on Clear Log
> http://sqlservercode.blogspot.com/
>|||yes
right click on a job-->view job history-->clear all
http://sqlservercode.blogspot.com/|||Thank you.
I am not looking to clear the table.
I am looking for job history that I can't find.
The job was scheduled twice a week to run for a year.
There appears to be no history on it.
And I don't believe anybody here would go and clear the log.
Although anything is possible.
--
Cathy B
"SQL" wrote:

> yes
> right click on a job-->view job history-->clear all
> http://sqlservercode.blogspot.com/
>|||Is your maximum job history log size (rows) set to 1000 and you have
reached that number a year ago?|||when somebody does "delete from sysjobhistory"
Cathy Boehm wrote:
> Please list the cases when the contents of sysjobhistory table would be
> deleted.
> thanks
> --
> Cathy B

msdb's sysjobs tables

Please list the cases when the contents of sysjobhistory table would be
deleted.
thanks
Cathy B
One way
from SQL server agent--> Properties-->Job System click on Clear Log
http://sqlservercode.blogspot.com/
|||Yes,
But I am looking as to why there's no history on a particular job.
Any other ways?
Cathy B
"SQL" wrote:

> One way
> from SQL server agent--> Properties-->Job System click on Clear Log
> http://sqlservercode.blogspot.com/
>
|||yes
right click on a job-->view job history-->clear all
http://sqlservercode.blogspot.com/
|||Thank you.
I am not looking to clear the table.
I am looking for job history that I can't find.
The job was scheduled twice a week to run for a year.
There appears to be no history on it.
And I don't believe anybody here would go and clear the log.
Although anything is possible.
Cathy B
"SQL" wrote:

> yes
> right click on a job-->view job history-->clear all
> http://sqlservercode.blogspot.com/
>
|||Is your maximum job history log size (rows) set to 1000 and you have
reached that number a year ago?
|||when somebody does "delete from sysjobhistory"
Cathy Boehm wrote:
> Please list the cases when the contents of sysjobhistory table would be
> deleted.
> thanks
> --
> Cathy B

msdb's sysjobs tables

Please list the cases when the contents of sysjobhistory table would be
deleted.
thanks
--
Cathy BOne way
from SQL server agent--> Properties-->Job System click on Clear Log
http://sqlservercode.blogspot.com/|||Yes,
But I am looking as to why there's no history on a particular job.
Any other ways?
--
Cathy B
"SQL" wrote:
> One way
> from SQL server agent--> Properties-->Job System click on Clear Log
> http://sqlservercode.blogspot.com/
>|||yes
right click on a job-->view job history-->clear all
http://sqlservercode.blogspot.com/|||Thank you.
I am not looking to clear the table.
I am looking for job history that I can't find.
The job was scheduled twice a week to run for a year.
There appears to be no history on it.
And I don't believe anybody here would go and clear the log.
Although anything is possible.
--
Cathy B
"SQL" wrote:
> yes
> right click on a job-->view job history-->clear all
> http://sqlservercode.blogspot.com/
>|||Is your maximum job history log size (rows) set to 1000 and you have
reached that number a year ago?|||when somebody does "delete from sysjobhistory"
Cathy Boehm wrote:
> Please list the cases when the contents of sysjobhistory table would be
> deleted.
> thanks
> --
> Cathy B

msdbdata.mdf file is over 5 GB, cannot shrink it

I have problem with my sql server developer edition.

I began reciving alerts regarding disk space, and found out that the msdb files are 5 GB!!! the data file is full, so I cannot shrink it, but the table usage shows only a few MB used by tables.

its not advisable to shrink the system dbs except tempdb,but in your case just check if any user tables are created in msdb if so just delete the same to free diskspace.... then try shrinking msdb but b4 shrinking ensure you have the latest backup of all the dbs....
|||

There are no user tables on this server.

It a local developer version, I use for development and small checks, there is no back up nither.

msdb.dbo.sysjobschedules less columns

msdb.dbo.sysjobschedules shows columns
scheduke_id,job_id,next_rundate,next_run
_time in MS Sqlserver 2005 beta3.
but the document (help) tells "name","enabled","freq_type","freq_interval"
too.
These columns were present in MS sqlserver 2000.
1. Is it that, some configuration is missing in my sql server 2005 beta3 DB?
2. or Do I need to do join with some other view/table to get these details.
Best regards,
Amit Kumar.Dear Amit,
I've got the CTP June for Sql Server 2005 and it happens the same.
""This topic is pre-release documentation and is subject to change in future
releases. Blank topics are included as placeholders.]""
Best regards,
"Amit Kumar" wrote:

> msdb.dbo.sysjobschedules shows columns
> scheduke_id,job_id,next_rundate,next_run
_time in MS Sqlserver 2005 beta3.
> but the document (help) tells "name","enabled","freq_type","freq_interval"
> too.
> These columns were present in MS sqlserver 2000.
> 1. Is it that, some configuration is missing in my sql server 2005 beta3 D
B?
> 2. or Do I need to do join with some other view/table to get these details
.
> Best regards,
> Amit Kumar.
>

msdb.dbo.sp_sqlagent_get_perf_counters using tons of cpu

msdb.dbo.sp_sqlagent_get_perf_counters is using almost all of the cpu usage
on our server. We have hardly anything running in sqlagent. Can you give me
some ideas on how to fix this?
Thanks so much,
Jenna
Open Enterprise Manager and expand the Management>SQL Server Agent folder
and click on Alerts.You should see 9 alerts whose names all start with Demo.
Select them all and Delete. If you don't use alerts there is no point in
sp_sqlagent_get_perf_counters running. If there are no Alerts defined it
won't run.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jenna S." <JennaS@.discussions.microsoft.com> wrote in message
news:8C0811F4-49A9-428C-BFDE-DA060470CAAD@.microsoft.com...
> msdb.dbo.sp_sqlagent_get_perf_counters is using almost all of the cpu
> usage
> on our server. We have hardly anything running in sqlagent. Can you give
> me
> some ideas on how to fix this?
> Thanks so much,
> Jenna

msdb.dbo.sp_sqlagent_get_perf_counters using tons of cpu

msdb.dbo.sp_sqlagent_get_perf_counters is using almost all of the cpu usage
on our server. We have hardly anything running in sqlagent. Can you give me
some ideas on how to fix this?
Thanks so much,
JennaOpen Enterprise Manager and expand the Management>SQL Server Agent folder
and click on Alerts.You should see 9 alerts whose names all start with Demo.
Select them all and Delete. If you don't use alerts there is no point in
sp_sqlagent_get_perf_counters running. If there are no Alerts defined it
won't run.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jenna S." <JennaS@.discussions.microsoft.com> wrote in message
news:8C0811F4-49A9-428C-BFDE-DA060470CAAD@.microsoft.com...
> msdb.dbo.sp_sqlagent_get_perf_counters is using almost all of the cpu
> usage
> on our server. We have hardly anything running in sqlagent. Can you give
> me
> some ideas on how to fix this?
> Thanks so much,
> Jenna

msdb.dbo.sp_sqlagent_get_perf_counters using tons of cpu

msdb.dbo.sp_sqlagent_get_perf_counters is using almost all of the cpu usage
on our server. We have hardly anything running in sqlagent. Can you give m
e
some ideas on how to fix this?
Thanks so much,
JennaOpen Enterprise Manager and expand the Management>SQL Server Agent folder
and click on Alerts.You should see 9 alerts whose names all start with Demo.
Select them all and Delete. If you don't use alerts there is no point in
sp_sqlagent_get_perf_counters running. If there are no Alerts defined it
won't run.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jenna S." <JennaS@.discussions.microsoft.com> wrote in message
news:8C0811F4-49A9-428C-BFDE-DA060470CAAD@.microsoft.com...
> msdb.dbo.sp_sqlagent_get_perf_counters is using almost all of the cpu
> usage
> on our server. We have hardly anything running in sqlagent. Can you give
> me
> some ideas on how to fix this?
> Thanks so much,
> Jenna

msdb.dbo.sp_send_dbmail error

I make PROCEDURE to send email useing db msdb and this PROCEDURE dbo.sp_send_dbmail like this

EXEC msdb.dbo.sp_send_dbmail

@.profile_name = 'saly',

@.recipients = @.email,

@.subject = @.subject,

@.body = @.body;

Go

And when EXEC it gives me mail qeue

and mail don't arrive where it goes i don't know please tell me what error

thankx very much

Check the mail log.

Have you start the smtp service

|||

hi

have the same promlem

and I have tried so many solutions.If u foud how to do so please tell me .

thanks

|||Its very likely an error with your SMTP server setup. Check to make sure that the machine you are sending the message from (sql server) is allowed to send smtp messages on your smtp server.
Tim

msdb.dbo.sp_send_dbmail error

I make PROCEDURE to send email useing db msdb and this PROCEDURE dbo.sp_send_dbmail like this

EXEC msdb.dbo.sp_send_dbmail

@.profile_name = 'saly',

@.recipients = @.email,

@.subject = @.subject,

@.body = @.body;

Go

And when EXEC it gives me mail qeue

and mail don't arrive where it goes i don't know please tell me what error

thankx very much

Check the mail log.

Have you start the smtp service

|||

hi

have the same promlem

and I have tried so many solutions.If u foud how to do so please tell me .

thanks

|||Its very likely an error with your SMTP server setup. Check to make sure that the machine you are sending the message from (sql server) is allowed to send smtp messages on your smtp server.
Tim

msdb.dbo.sp_send_dbmail error

I make PROCEDURE to send email useing db msdb and this PROCEDURE dbo.sp_send_dbmail like this

EXEC msdb.dbo.sp_send_dbmail

@.profile_name = 'saly',

@.recipients = @.email,

@.subject = @.subject,

@.body = @.body;

Go

And when EXEC it gives me mail qeue

and mail don't arrive where it goes i don't know please tell me what error

thankx very much

Check the mail log.

Have you start the smtp service

|||

hi

have the same promlem

and I have tried so many solutions.If u foud how to do so please tell me .

thanks

|||Its very likely an error with your SMTP server setup. Check to make sure that the machine you are sending the message from (sql server) is allowed to send smtp messages on your smtp server.
Tim

msdb..backupset.backup_finish_date -- changes from SQL2000?

It appears that the behavior has changed for the backupset table in the MSDB database between versions.

In SQL2000 backup_start_date & backup_finish_date were populated correctly. In 2005, backup_finish_date is the same as the backup_start_date value. Is this a bug or should I be looking for my backup timing elsewhere in 2005?

Thank you.

I can't repro the problem. It works fine for me.

I use this query to get a quick look at timings.

select top 100 database_name, backup_set_id, type, backup_finish_date, backup_start_date,
datediff (second,backup_start_date, backup_finish_date) secondsToComplete
,convert (bigint, backup_size / 1048576 ) sizeInMB

from msdb..backupset
where type = 'D'
order by backup_finish_date desc

If you can describe a repro, please let us know.

|||

It appears to work fine for database backups, I see timing differences in them. However for log backups (type = 'L') it appears that it's not changing, but I'm still investigating.

I was originally pursuing a long running t-log backup time and that's how I noticed this behavior. I've since discovered that if a backup device has a large # of backups appended to it, it dramaticly effects the time it takes to perform the backup. I was suprised to find that the finish time was being reported as the same as the start time when it clearly was taking much longer to perform the backup...

Currently (and with a small # of appends), my backups are averaging around 1/10th second so I'll have to try to dummy up some logspace and see if it's just that my backups are running quicker than the process can account for, or if it is indeed a bug.

I'll have more in a day or so once I can get some testing done. Thank you for looking into this.

|||

Your info helps.

Our start/finish times do NOT include the time it takes to open the media set and prepare it for writing.

The time only includes the time that is actually spent transferring data. This is the same logic as exists in sql2000.

For example, it may take many minutes to open a tape drive and seek to the end when appending data. That time is not included.

So if you want to also include that time, you'll need to add your own start/stop time.

This might already work if you use sqlagent jobs, and look up the start/top time in the job history.

Are you backing up to tape? That will always be relatively slow, and gets worse when appending backups.

If you have a lot of backups to append in a batch, such as a nightly job, you can use BACKUP WITH NOREWIND which avoids all the time to REWIND/SEEK to end that happens in such a scenario.

If you are backing up to disk, then having a lot of backups in the file shouldn't really matter...to a point.

1000's of backups WILL result in significant delay. The reason is that when setting up for the backup, we perform a synchronous read of all the marks before/after each backup set.

For disk backups, my recommendation is to prefer the use of a single filesystem directory, then write each backup into a separate disk file. That gives you better control over retention and space management. But I'd agree that it might be slightly more work to wrap this in your backup jobs. If you are using our management tools, they already do this by naming the log backups as ***.TRN where the *** includes the database name and timestamp of the backup.

Hope that helps.

msdb(Suspect)

My Sql server msdb database demaged.
I did not backuped it before.
I copy the msdbdata.mdf and msdblog.ldf from other SQL database.
But not working.
How can I fix it
ThankHi Chen,
Search for << msdb database marked as "suspect" >> in this forum. And you
should be able to get the answer.
Also check this out: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33914
Thanks
GYK

msdb(Suspect)

My Sql server msdb database demaged.
I did not backuped it before.
I copy the msdbdata.mdf and msdblog.ldf from other SQL database.
But not working.
How can I fix it
Thank
Hi Chen,
Search for << msdb database marked as "suspect" >> in this forum. And you
should be able to get the answer.
Also check this out: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33914
Thanks
GYK

msdb(Suspect)

My Sql server msdb database demaged.
I did not backuped it before.
I copy the msdbdata.mdf and msdblog.ldf from other SQL database.
But not working.
How can I fix it
ThankHi Chen,
Search for << msdb database marked as "suspect" >> in this forum. And you
should be able to get the answer.
Also check this out: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33914
--
Thanks
GYK

MSDB up to 2GB due to DTA

I noticed my MSDB database is up over 2GB. Most of the data is in
DTA_tuninglog. Is there a process to purge this data? Can I simply delete
all the rows or will that cause an inconsistency with another table?
Thanks
Ron
Hi Ron,
I am not sure what version of the server you have running, however check
this link out as I think it may help you clean up the database:
http://support.microsoft.com/kb/899634
Take care.
~lb
"Ron" wrote:

> I noticed my MSDB database is up over 2GB. Most of the data is in
> DTA_tuninglog. Is there a process to purge this data? Can I simply delete
> all the rows or will that cause an inconsistency with another table?
> Thanks
> Ron

MSDB up to 2GB due to DTA

I noticed my MSDB database is up over 2GB. Most of the data is in
DTA_tuninglog. Is there a process to purge this data? Can I simply delete
all the rows or will that cause an inconsistency with another table?
Thanks
RonHi Ron,
I am not sure what version of the server you have running, however check
this link out as I think it may help you clean up the database:
http://support.microsoft.com/kb/899634
Take care.
--
~lb
"Ron" wrote:
> I noticed my MSDB database is up over 2GB. Most of the data is in
> DTA_tuninglog. Is there a process to purge this data? Can I simply delete
> all the rows or will that cause an inconsistency with another table?
> Thanks
> Ron

msdb Unavailable

I work on a SQL 2000 Standard server that is part of our SBS package. Two days ago we ran a set of MS updates on the server and the residual symptom is that DTS packages, the Import or Export wizard all will not run. The Sql Server Agent service runs under the local system account, however that was true prior to the updates.

The error when attempting to run an import is that the OLEDB connection object is not available. The error occurs when trying to use the Access driver or a DSN. Further investigation indicates that msdb could not be contacted.

Does anyone have an idea as to what might cause this and a workaround?
Thanks,

misdeanI would perform an integrity check (DBCC CHECKDB) of the MSDE database, to se if it is corrupt. It might be because it's late night here, but that's the only reason I could think of now.|||I will give it a try. Somehow, I believe is is related to the MS updates applied the other day. I will run DBCC on it in the AM and post the results.

Thanks,

Keith

msdb trascation logging mode

Hi,
I have a production SQL 2000 database with transaction logging turned on
('Full' recovery mode). I backup the transaction log every 15 minutes. In
order to use the point-in-time recovery option, do I also need to have the
'Full' recovery model set on the msdb? I ask this because I assume the msdb
holds information about the transaction log backup taken on the production
database. Without this information, I suspect point-in-time recover will not
be possible. Is my assumption correct?
Regards,
James G.Hi
MSDB is not needed to do point in time recovery. MSDB is just used to keep
the backup information so that Enterprise Manager UI can show you what
backups are available. It is a nice to have.
Point in time recovery needs the database dump and all the transaction log
dumps after that to the time you want to restore to.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"James Goodwill" wrote:

> Hi,
> I have a production SQL 2000 database with transaction logging turned on
> ('Full' recovery mode). I backup the transaction log every 15 minutes. In
> order to use the point-in-time recovery option, do I also need to have the
> 'Full' recovery model set on the msdb? I ask this because I assume the msd
b
> holds information about the transaction log backup taken on the production
> database. Without this information, I suspect point-in-time recover will n
ot
> be possible. Is my assumption correct?
> Regards,
> James G.
>
>|||Hi James
The MSDB doesn't have to be in full recovery mode. In fact the MSDB
has no role when you run the restore. If the MSDB would have any role
in the restore then it would be impossible to restore the database on a
different server. All the data that is needed for the point in time
restore is stored in the log backup it self.
Adi|||Hi,
'Full' recovery model set on the msdb?
Ans:- NO NEED
I suspect point-in-time recover will not be possible. Is my assumption
correct?
No. You can do it with out a MSDB tranasction log backup.
MSDB database holds the backup history information. FOR each backup SQL
Server logs an entry into BACKUPSET table in MSDB database.
This is just an information only and will be useful if you use Enterprise
manager to do point in time recovery. Even if you
do not have tis information you can do a recovery based on Transaction log
file date and time. For a time based recovery:-
1. Restore the Full database backup first with Norecovery
2. Restore the logbackup file in sequence based on date and time with
Norecovery till last file
3. Restore the last transaction log backup with RECOVERY and STOPAT option.
Note:-
I am taking the MSDB full database backup daily once only.
Thanks
Hari
SQL Server MVP
"James Goodwill" <james.goodwill@.uk.fujitsu.com> wrote in message
news:DmmHe.10993$SO4.8738@.newsfe4-win.ntli.net...
> Hi,
> I have a production SQL 2000 database with transaction logging turned on
> ('Full' recovery mode). I backup the transaction log every 15 minutes. In
> order to use the point-in-time recovery option, do I also need to have the
> 'Full' recovery model set on the msdb? I ask this because I assume the
> msdb
> holds information about the transaction log backup taken on the production
> database. Without this information, I suspect point-in-time recover will
> not
> be possible. Is my assumption correct?
> Regards,
> James G.
>
>

msdb trascation logging mode

Hi,
I have a production SQL 2000 database with transaction logging turned on
('Full' recovery mode). I backup the transaction log every 15 minutes. In
order to use the point-in-time recovery option, do I also need to have the
'Full' recovery model set on the msdb? I ask this because I assume the msdb
holds information about the transaction log backup taken on the production
database. Without this information, I suspect point-in-time recover will not
be possible. Is my assumption correct?
Regards,
James G.
Hi
MSDB is not needed to do point in time recovery. MSDB is just used to keep
the backup information so that Enterprise Manager UI can show you what
backups are available. It is a nice to have.
Point in time recovery needs the database dump and all the transaction log
dumps after that to the time you want to restore to.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"James Goodwill" wrote:

> Hi,
> I have a production SQL 2000 database with transaction logging turned on
> ('Full' recovery mode). I backup the transaction log every 15 minutes. In
> order to use the point-in-time recovery option, do I also need to have the
> 'Full' recovery model set on the msdb? I ask this because I assume the msdb
> holds information about the transaction log backup taken on the production
> database. Without this information, I suspect point-in-time recover will not
> be possible. Is my assumption correct?
> Regards,
> James G.
>
>
|||Hi James
The MSDB doesn't have to be in full recovery mode. In fact the MSDB
has no role when you run the restore. If the MSDB would have any role
in the restore then it would be impossible to restore the database on a
different server. All the data that is needed for the point in time
restore is stored in the log backup it self.
Adi
|||Hi,
'Full' recovery model set on the msdb?
Ans:- NO NEED
I suspect point-in-time recover will not be possible. Is my assumption
correct?
No. You can do it with out a MSDB tranasction log backup.
MSDB database holds the backup history information. FOR each backup SQL
Server logs an entry into BACKUPSET table in MSDB database.
This is just an information only and will be useful if you use Enterprise
manager to do point in time recovery. Even if you
do not have tis information you can do a recovery based on Transaction log
file date and time. For a time based recovery:-
1. Restore the Full database backup first with Norecovery
2. Restore the logbackup file in sequence based on date and time with
Norecovery till last file
3. Restore the last transaction log backup with RECOVERY and STOPAT option.
Note:-
I am taking the MSDB full database backup daily once only.
Thanks
Hari
SQL Server MVP
"James Goodwill" <james.goodwill@.uk.fujitsu.com> wrote in message
news:DmmHe.10993$SO4.8738@.newsfe4-win.ntli.net...
> Hi,
> I have a production SQL 2000 database with transaction logging turned on
> ('Full' recovery mode). I backup the transaction log every 15 minutes. In
> order to use the point-in-time recovery option, do I also need to have the
> 'Full' recovery model set on the msdb? I ask this because I assume the
> msdb
> holds information about the transaction log backup taken on the production
> database. Without this information, I suspect point-in-time recover will
> not
> be possible. Is my assumption correct?
> Regards,
> James G.
>
>