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.
>
>

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.
>
>

MSDB Table User Permissions

Just out of curiosity, could someone point me towards a listing of the user permissions for the MSDB table? I have looked through BOL and on the internet and cannot find a good listing. An example would be something like...
dts_admin: <dts_admin description>

Thanks in advance.
-Kyle

Unfortunately msdb is being used by a number of different SQL Server services (built on top of SQL Server Engine, but shipped as part of SQL Server itself), and each type of service documents their own set of roles and usage (i.e. http://msdn2.microsoft.com/en-us/library/ms141053.aspx can help you with dts* principals), but there is no centralized documentation.

My recommendation right now would be to use msdn to search for any out of the box principal you want to know more about, and typically you will find the documentation for all related principals as well.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

msdb sysmail_attachments_transfer - very large, but 0 records

Our SQL server 2005 has a system table in the MSDB database called
sysmail_attachments_transfer. The management console summary report
shows almost 5 GB of data for the table with 0 records.
Table Name # Records Reserved Data
Indexes Unused
dbo.sysmail_attachments 1533 6178008 KB 6174160 KB 8
KB 3840 KB
dbo.sysmail_attachments_transfer 0 4936816 KB 4935864 KB 8
KB 944 KB
This is puzzling. I would like to know more about this system table,
and to get back some of the space used, if possible. Does anyone know
anything about this? There doesn't seem to be any documentation
anywhere on it.
Rob Fisch
Kaz, Inc.
Hi
This looks like it holds the results of queries that are attached see
http://www.elsasoft.org/SUMMER.msdb/sp_dbospsenddbmail.htm but I have not
found much more than this.
What does sp_spaceused give for this table?
Although this information should be correct you may want to try DBCC
UPDATEUSAGE to see if anything changes.
John
"rfisch@.gmail.com" wrote:

> Our SQL server 2005 has a system table in the MSDB database called
> sysmail_attachments_transfer. The management console summary report
> shows almost 5 GB of data for the table with 0 records.
> Table Name # Records Reserved Data
> Indexes Unused
> ----
> dbo.sysmail_attachments 1533 6178008 KB 6174160 KB 8
> KB 3840 KB
> dbo.sysmail_attachments_transfer 0 4936816 KB 4935864 KB 8
> KB 944 KB
> This is puzzling. I would like to know more about this system table,
> and to get back some of the space used, if possible. Does anyone know
> anything about this? There doesn't seem to be any documentation
> anywhere on it.
> Rob Fisch
> Kaz, Inc.
>
|||Hi John,
sp_spaceused gave the same reading as the summary report.
DBCC UPDATEUSAGE didn't change anything to speak of.
Thanks for the link. It was interesting. There may be some clues in
there, but nothing jumps out at me.
Thanks for giving it a stab.
Rob
John Bell wrote:[vbcol=seagreen]
> Hi
> This looks like it holds the results of queries that are attached see
> http://www.elsasoft.org/SUMMER.msdb/sp_dbospsenddbmail.htm but I have not
> found much more than this.
> What does sp_spaceused give for this table?
> Although this information should be correct you may want to try DBCC
> UPDATEUSAGE to see if anything changes.
> John
>
> "rfisch@.gmail.com" wrote:
|||Hi
I am not sure what has caused this. Have you tried DBCC CHECKDB?
John
On Nov 4, 1:55 am, "Rob Fisch" <rfi...@.gmail.com> wrote:[vbcol=seagreen]
> Hi John,
> sp_spaceused gave the same reading as the summary report.
> DBCC UPDATEUSAGE didn't change anything to speak of.
> Thanks for the link. It was interesting. There may be some clues in
> there, but nothing jumps out at me.
> Thanks for giving it a stab.
> Rob
>
> John Bell wrote:
>
>
>
>
|||Well I guess the good news is that DBCC CHECKDB reports "CHECKDB found
0 allocation errors and 0 consistency errors ".
John Bell wrote:[vbcol=seagreen]
> Hi
> I am not sure what has caused this. Have you tried DBCC CHECKDB?
> John
> On Nov 4, 1:55 am, "Rob Fisch" <rfi...@.gmail.com> wrote:
|||Hi ROb
What version are you using (SELECT @.@.VERSION) ?
I guess you could try manually deleting from/truncating the table even
though it is reporting no rows.
"Rob Fisch" wrote:

> Well I guess the good news is that DBCC CHECKDB reports "CHECKDB found
> 0 allocation errors and 0 consistency errors ".
>
>
> John Bell wrote:
>