Saturday, February 25, 2012

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

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:
> 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
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 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:
> > Hi
> > This looks like it holds the results of queries that are attached see
> >http://www.elsasoft.org/SUMMER.msdb/sp_dbospsenddbmail.htmbut 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
> > "rfi...@.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.- Hide quoted text -- Show quoted text -|||Well I guess the good news is that DBCC CHECKDB reports "CHECKDB found
0 allocation errors and 0 consistency errors ".
John Bell 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:
> > 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:
> > > Hi
> >
> > > This looks like it holds the results of queries that are attached see
> > >http://www.elsasoft.org/SUMMER.msdb/sp_dbospsenddbmail.htmbut 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
> >
> > > "rfi...@.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.- Hide quoted text -- Show quoted text -|||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:
> > 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 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:
> > > > Hi
> > >
> > > > This looks like it holds the results of queries that are attached see
> > > >http://www.elsasoft.org/SUMMER.msdb/sp_dbospsenddbmail.htmbut 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
> > >
> > > > "rfi...@.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.- Hide quoted text -- Show quoted text -
>|||Or perhaps ALTER INDEX with REORGANIZE and LOB_COMPACTION? I don't deal that much with blobs, and
I'd guess that compaction shouldn't be necessary in this case, but it might be worth a try?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9406A927-93E5-4E82-B52F-745E6AE4CD6E@.microsoft.com...
> 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:
>> > 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 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:
>> > > > Hi
>> > >
>> > > > This looks like it holds the results of queries that are attached see
>> > > >http://www.elsasoft.org/SUMMER.msdb/sp_dbospsenddbmail.htmbut 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
>> > >
>> > > > "rfi...@.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.- Hide quoted text -- Show quoted text -
>>

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...psenddbmail.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...psenddbmail.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:
>|||Or perhaps ALTER INDEX with REORGANIZE and LOB_COMPACTION? I don't deal that
much with blobs, and
I'd guess that compaction shouldn't be necessary in this case, but it might
be worth a try?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9406A927-93E5-4E82-B52F-745E6AE4CD6E@.microsoft.com...[vbcol=seagreen]
> 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:
>

MSDB suspect

Hi,
What are the steps I should execute if MSDB database is showing suspect.
The conditions is we dont have a backup but the sqlserver is running.Look at the sql server logs and see what might have caused this. Look in bol for sp_resetstatus. Worst case scenario, you will have to detach the msdb and run instmsdb.sql to recreate (however, you will lose all information stored in the msdb - DTS packages, scheduled tasks, backup/restore history, replication).

Oh yes - One last step - BACKUP ... :-)

msdb suspect

After move the whole sql server databases to a new box, msdb shows suspect,
the other databases all work fine.
A select statement shows that the staus of msdb is 280.
After I change the status to 24, it still does not work. I can't start
SQLServerAgent now.
Please help!!!
thanks, FeiCheck out the SQL Server errorlog why it became suspect. Perhaps you don't h
ave the files for msdb
in the same directory as the original SQL Server, for instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Fei" <Fei@.discussions.microsoft.com> wrote in message
news:81056E87-078C-4E54-91EA-D2A7273B4554@.microsoft.com...
> After move the whole sql server databases to a new box, msdb shows suspect
,
> the other databases all work fine.
> A select statement shows that the staus of msdb is 280.
> After I change the status to 24, it still does not work. I can't start
> SQLServerAgent now.
> Please help!!!
> thanks, Fei|||Tibor,
Thanks for your quick response.
I checked the errorlog and there is no related error msg found in the log.
The msdb files are on a different location (e:drive) from the original
installation (c: drive)because we wanted to seperate the datafiles from the
software files. The other system database files such as master, model are al
l
in the same location as the msdb and the other system databases are just
fine.
Any suggestions?
Fei
"Tibor Karaszi" wrote:

> Check out the SQL Server errorlog why it became suspect. Perhaps you don't
have the files for msdb
> in the same directory as the original SQL Server, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Fei" <Fei@.discussions.microsoft.com> wrote in message
> news:81056E87-078C-4E54-91EA-D2A7273B4554@.microsoft.com...
>|||Since the path for msdb is in the master database, it is expected that msdb
becomes suspect if you
move the files to some different location. I suggest you move the files back
and then follow
instructions in either KB 224071 if on 2000 or Books Online of on 2005 on ho
w to move files for
msdb.
Also, there *are* error messages for msdb to be found in the errorlog file,
you just need to find
the right errorlog file (first startup when it became suspect).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Fei" <Fei@.discussions.microsoft.com> wrote in message
news:36CB5CBE-2373-4261-97C0-1AA615E564E4@.microsoft.com...[vbcol=seagreen]
> Tibor,
> Thanks for your quick response.
> I checked the errorlog and there is no related error msg found in the log.
> The msdb files are on a different location (e:drive) from the original
> installation (c: drive)because we wanted to seperate the datafiles from th
e
> software files. The other system database files such as master, model are
all
> in the same location as the msdb and the other system databases are just
> fine.
> Any suggestions?
>
> Fei
> "Tibor Karaszi" wrote:
>|||Hi Tibor
> Since the path for msdb is in the master database, it is expected that
> msdb becomes suspect if you move the files to some different location.
In KB 224071 they mention in how to move MSDB database the different
location to be moved the MSDB database
4. Move the Msdbdata.mdf and Msdblog.ldf files from the current
location (D:\Mssql8\Data) to the new location (E:\Mssql8\Data).
However the new master's path is dE:\SQLDATA\master.mdf
and -lE:\SQLDATA\mastlog.ldf. So should msdb database be attach to the same
path?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C6840BE3-DB95-4C18-A44E-7630B5A67734@.microsoft.com...
> Since the path for msdb is in the master database, it is expected that
> msdb becomes suspect if you move the files to some different location. I
> suggest you move the files back and then follow instructions in either KB
> 224071 if on 2000 or Books Online of on 2005 on how to move files for
> msdb.
> Also, there *are* error messages for msdb to be found in the errorlog
> file, you just need to find the right errorlog file (first startup when it
> became suspect).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Fei" <Fei@.discussions.microsoft.com> wrote in message
> news:36CB5CBE-2373-4261-97C0-1AA615E564E4@.microsoft.com...
>|||No msdb doesn't have to be in the same path as master. But the path to msdb
is *in* master, so you
can't just move the files to some other location (which is true for any data
base).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OhPtlBTvHHA.4540@.TK2MSFTNGP05.phx.gbl.
.
> Hi Tibor
> In KB 224071 they mention in how to move MSDB database the different loc
ation to be moved the
> MSDB database
> 4. Move the Msdbdata.mdf and Msdblog.ldf files from the current locat
ion (D:\Mssql8\Data) to
> the new location (E:\Mssql8\Data).
>
> However the new master's path is dE:\SQLDATA\master.mdf and -lE:\SQLDATA\m
astlog.ldf. So should
> msdb database be attach to the same path?
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:C6840BE3-DB95-4C18-A44E-7630B5A67734@.microsoft.com...
>|||Tibor
Ah, yes. I just misunderstood your comments.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uh84O8ZvHHA.4540@.TK2MSFTNGP05.phx.gbl...
> No msdb doesn't have to be in the same path as master. But the path to
> msdb is *in* master, so you can't just move the files to some other
> location (which is true for any database).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OhPtlBTvHHA.4540@.TK2MSFTNGP05.phx.gbl...
>

msdb suspect

After move the whole sql server databases to a new box, msdb shows suspect,
the other databases all work fine.
A select statement shows that the staus of msdb is 280.
After I change the status to 24, it still does not work. I can't start
SQLServerAgent now.
Please help!!!
thanks, Fei
Check out the SQL Server errorlog why it became suspect. Perhaps you don't have the files for msdb
in the same directory as the original SQL Server, for instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Fei" <Fei@.discussions.microsoft.com> wrote in message
news:81056E87-078C-4E54-91EA-D2A7273B4554@.microsoft.com...
> After move the whole sql server databases to a new box, msdb shows suspect,
> the other databases all work fine.
> A select statement shows that the staus of msdb is 280.
> After I change the status to 24, it still does not work. I can't start
> SQLServerAgent now.
> Please help!!!
> thanks, Fei
|||Tibor,
Thanks for your quick response.
I checked the errorlog and there is no related error msg found in the log.
The msdb files are on a different location (e:drive) from the original
installation (c: drive)because we wanted to seperate the datafiles from the
software files. The other system database files such as master, model are all
in the same location as the msdb and the other system databases are just
fine.
Any suggestions?
Fei
"Tibor Karaszi" wrote:

> Check out the SQL Server errorlog why it became suspect. Perhaps you don't have the files for msdb
> in the same directory as the original SQL Server, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Fei" <Fei@.discussions.microsoft.com> wrote in message
> news:81056E87-078C-4E54-91EA-D2A7273B4554@.microsoft.com...
>
|||Since the path for msdb is in the master database, it is expected that msdb becomes suspect if you
move the files to some different location. I suggest you move the files back and then follow
instructions in either KB 224071 if on 2000 or Books Online of on 2005 on how to move files for
msdb.
Also, there *are* error messages for msdb to be found in the errorlog file, you just need to find
the right errorlog file (first startup when it became suspect).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Fei" <Fei@.discussions.microsoft.com> wrote in message
news:36CB5CBE-2373-4261-97C0-1AA615E564E4@.microsoft.com...[vbcol=seagreen]
> Tibor,
> Thanks for your quick response.
> I checked the errorlog and there is no related error msg found in the log.
> The msdb files are on a different location (e:drive) from the original
> installation (c: drive)because we wanted to seperate the datafiles from the
> software files. The other system database files such as master, model are all
> in the same location as the msdb and the other system databases are just
> fine.
> Any suggestions?
>
> Fei
> "Tibor Karaszi" wrote:
|||Hi Tibor
> Since the path for msdb is in the master database, it is expected that
> msdb becomes suspect if you move the files to some different location.
In KB 224071 they mention in how to move MSDB database the different
location to be moved the MSDB database
4. Move the Msdbdata.mdf and Msdblog.ldf files from the current
location (D:\Mssql8\Data) to the new location (E:\Mssql8\Data).
However the new master's path is dE:\SQLDATA\master.mdf
and -lE:\SQLDATA\mastlog.ldf. So should msdb database be attach to the same
path?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C6840BE3-DB95-4C18-A44E-7630B5A67734@.microsoft.com...
> Since the path for msdb is in the master database, it is expected that
> msdb becomes suspect if you move the files to some different location. I
> suggest you move the files back and then follow instructions in either KB
> 224071 if on 2000 or Books Online of on 2005 on how to move files for
> msdb.
> Also, there *are* error messages for msdb to be found in the errorlog
> file, you just need to find the right errorlog file (first startup when it
> became suspect).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Fei" <Fei@.discussions.microsoft.com> wrote in message
> news:36CB5CBE-2373-4261-97C0-1AA615E564E4@.microsoft.com...
>
|||No msdb doesn't have to be in the same path as master. But the path to msdb is *in* master, so you
can't just move the files to some other location (which is true for any database).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OhPtlBTvHHA.4540@.TK2MSFTNGP05.phx.gbl...
> Hi Tibor
> In KB 224071 they mention in how to move MSDB database the different location to be moved the
> MSDB database
> 4. Move the Msdbdata.mdf and Msdblog.ldf files from the current location (D:\Mssql8\Data) to
> the new location (E:\Mssql8\Data).
>
> However the new master's path is dE:\SQLDATA\master.mdf and -lE:\SQLDATA\mastlog.ldf. So should
> msdb database be attach to the same path?
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:C6840BE3-DB95-4C18-A44E-7630B5A67734@.microsoft.com...
>
|||Tibor
Ah, yes. I just misunderstood your comments.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uh84O8ZvHHA.4540@.TK2MSFTNGP05.phx.gbl...
> No msdb doesn't have to be in the same path as master. But the path to
> msdb is *in* master, so you can't just move the files to some other
> location (which is true for any database).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OhPtlBTvHHA.4540@.TK2MSFTNGP05.phx.gbl...
>

msdb suspect

After move the whole sql server databases to a new box, msdb shows suspect,
the other databases all work fine.
A select statement shows that the staus of msdb is 280.
After I change the status to 24, it still does not work. I can't start
SQLServerAgent now.
Please help!!!
thanks, FeiCheck out the SQL Server errorlog why it became suspect. Perhaps you don't have the files for msdb
in the same directory as the original SQL Server, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Fei" <Fei@.discussions.microsoft.com> wrote in message
news:81056E87-078C-4E54-91EA-D2A7273B4554@.microsoft.com...
> After move the whole sql server databases to a new box, msdb shows suspect,
> the other databases all work fine.
> A select statement shows that the staus of msdb is 280.
> After I change the status to 24, it still does not work. I can't start
> SQLServerAgent now.
> Please help!!!
> thanks, Fei|||Tibor,
Thanks for your quick response.
I checked the errorlog and there is no related error msg found in the log.
The msdb files are on a different location (e:drive) from the original
installation (c: drive)because we wanted to seperate the datafiles from the
software files. The other system database files such as master, model are all
in the same location as the msdb and the other system databases are just
fine.
Any suggestions?
Fei
"Tibor Karaszi" wrote:
> Check out the SQL Server errorlog why it became suspect. Perhaps you don't have the files for msdb
> in the same directory as the original SQL Server, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Fei" <Fei@.discussions.microsoft.com> wrote in message
> news:81056E87-078C-4E54-91EA-D2A7273B4554@.microsoft.com...
> > After move the whole sql server databases to a new box, msdb shows suspect,
> > the other databases all work fine.
> >
> > A select statement shows that the staus of msdb is 280.
> >
> > After I change the status to 24, it still does not work. I can't start
> > SQLServerAgent now.
> >
> > Please help!!!
> >
> > thanks, Fei
>|||Since the path for msdb is in the master database, it is expected that msdb becomes suspect if you
move the files to some different location. I suggest you move the files back and then follow
instructions in either KB 224071 if on 2000 or Books Online of on 2005 on how to move files for
msdb.
Also, there *are* error messages for msdb to be found in the errorlog file, you just need to find
the right errorlog file (first startup when it became suspect).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Fei" <Fei@.discussions.microsoft.com> wrote in message
news:36CB5CBE-2373-4261-97C0-1AA615E564E4@.microsoft.com...
> Tibor,
> Thanks for your quick response.
> I checked the errorlog and there is no related error msg found in the log.
> The msdb files are on a different location (e:drive) from the original
> installation (c: drive)because we wanted to seperate the datafiles from the
> software files. The other system database files such as master, model are all
> in the same location as the msdb and the other system databases are just
> fine.
> Any suggestions?
>
> Fei
> "Tibor Karaszi" wrote:
>> Check out the SQL Server errorlog why it became suspect. Perhaps you don't have the files for
>> msdb
>> in the same directory as the original SQL Server, for instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Fei" <Fei@.discussions.microsoft.com> wrote in message
>> news:81056E87-078C-4E54-91EA-D2A7273B4554@.microsoft.com...
>> > After move the whole sql server databases to a new box, msdb shows suspect,
>> > the other databases all work fine.
>> >
>> > A select statement shows that the staus of msdb is 280.
>> >
>> > After I change the status to 24, it still does not work. I can't start
>> > SQLServerAgent now.
>> >
>> > Please help!!!
>> >
>> > thanks, Fei|||Hi Tibor
> Since the path for msdb is in the master database, it is expected that
> msdb becomes suspect if you move the files to some different location.
In KB 224071 they mention in how to move MSDB database the different
location to be moved the MSDB database
4. Move the Msdbdata.mdf and Msdblog.ldf files from the current
location (D:\Mssql8\Data) to the new location (E:\Mssql8\Data).
However the new master's path is dE:\SQLDATA\master.mdf
and -lE:\SQLDATA\mastlog.ldf. So should msdb database be attach to the same
path?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C6840BE3-DB95-4C18-A44E-7630B5A67734@.microsoft.com...
> Since the path for msdb is in the master database, it is expected that
> msdb becomes suspect if you move the files to some different location. I
> suggest you move the files back and then follow instructions in either KB
> 224071 if on 2000 or Books Online of on 2005 on how to move files for
> msdb.
> Also, there *are* error messages for msdb to be found in the errorlog
> file, you just need to find the right errorlog file (first startup when it
> became suspect).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Fei" <Fei@.discussions.microsoft.com> wrote in message
> news:36CB5CBE-2373-4261-97C0-1AA615E564E4@.microsoft.com...
>> Tibor,
>> Thanks for your quick response.
>> I checked the errorlog and there is no related error msg found in the
>> log.
>> The msdb files are on a different location (e:drive) from the original
>> installation (c: drive)because we wanted to seperate the datafiles from
>> the
>> software files. The other system database files such as master, model are
>> all
>> in the same location as the msdb and the other system databases are just
>> fine.
>> Any suggestions?
>>
>> Fei
>> "Tibor Karaszi" wrote:
>> Check out the SQL Server errorlog why it became suspect. Perhaps you
>> don't have the files for msdb
>> in the same directory as the original SQL Server, for instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Fei" <Fei@.discussions.microsoft.com> wrote in message
>> news:81056E87-078C-4E54-91EA-D2A7273B4554@.microsoft.com...
>> > After move the whole sql server databases to a new box, msdb shows
>> > suspect,
>> > the other databases all work fine.
>> >
>> > A select statement shows that the staus of msdb is 280.
>> >
>> > After I change the status to 24, it still does not work. I can't
>> > start
>> > SQLServerAgent now.
>> >
>> > Please help!!!
>> >
>> > thanks, Fei
>|||No msdb doesn't have to be in the same path as master. But the path to msdb is *in* master, so you
can't just move the files to some other location (which is true for any database).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OhPtlBTvHHA.4540@.TK2MSFTNGP05.phx.gbl...
> Hi Tibor
>> Since the path for msdb is in the master database, it is expected that msdb becomes suspect if
>> you move the files to some different location.
> In KB 224071 they mention in how to move MSDB database the different location to be moved the
> MSDB database
> 4. Move the Msdbdata.mdf and Msdblog.ldf files from the current location (D:\Mssql8\Data) to
> the new location (E:\Mssql8\Data).
>
> However the new master's path is dE:\SQLDATA\master.mdf and -lE:\SQLDATA\mastlog.ldf. So should
> msdb database be attach to the same path?
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:C6840BE3-DB95-4C18-A44E-7630B5A67734@.microsoft.com...
>> Since the path for msdb is in the master database, it is expected that msdb becomes suspect if
>> you move the files to some different location. I suggest you move the files back and then follow
>> instructions in either KB 224071 if on 2000 or Books Online of on 2005 on how to move files for
>> msdb.
>> Also, there *are* error messages for msdb to be found in the errorlog file, you just need to find
>> the right errorlog file (first startup when it became suspect).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Fei" <Fei@.discussions.microsoft.com> wrote in message
>> news:36CB5CBE-2373-4261-97C0-1AA615E564E4@.microsoft.com...
>> Tibor,
>> Thanks for your quick response.
>> I checked the errorlog and there is no related error msg found in the log.
>> The msdb files are on a different location (e:drive) from the original
>> installation (c: drive)because we wanted to seperate the datafiles from the
>> software files. The other system database files such as master, model are all
>> in the same location as the msdb and the other system databases are just
>> fine.
>> Any suggestions?
>>
>> Fei
>> "Tibor Karaszi" wrote:
>> Check out the SQL Server errorlog why it became suspect. Perhaps you don't have the files for
>> msdb
>> in the same directory as the original SQL Server, for instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Fei" <Fei@.discussions.microsoft.com> wrote in message
>> news:81056E87-078C-4E54-91EA-D2A7273B4554@.microsoft.com...
>> > After move the whole sql server databases to a new box, msdb shows suspect,
>> > the other databases all work fine.
>> >
>> > A select statement shows that the staus of msdb is 280.
>> >
>> > After I change the status to 24, it still does not work. I can't start
>> > SQLServerAgent now.
>> >
>> > Please help!!!
>> >
>> > thanks, Fei
>>
>|||Tibor
Ah, yes. I just misunderstood your comments.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uh84O8ZvHHA.4540@.TK2MSFTNGP05.phx.gbl...
> No msdb doesn't have to be in the same path as master. But the path to
> msdb is *in* master, so you can't just move the files to some other
> location (which is true for any database).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OhPtlBTvHHA.4540@.TK2MSFTNGP05.phx.gbl...
>> Hi Tibor
>> Since the path for msdb is in the master database, it is expected that
>> msdb becomes suspect if you move the files to some different location.
>> In KB 224071 they mention in how to move MSDB database the different
>> location to be moved the MSDB database
>> 4. Move the Msdbdata.mdf and Msdblog.ldf files from the current
>> location (D:\Mssql8\Data) to the new location (E:\Mssql8\Data).
>>
>> However the new master's path is dE:\SQLDATA\master.mdf
>> and -lE:\SQLDATA\mastlog.ldf. So should msdb database be attach to the
>> same path?
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:C6840BE3-DB95-4C18-A44E-7630B5A67734@.microsoft.com...
>> Since the path for msdb is in the master database, it is expected that
>> msdb becomes suspect if you move the files to some different location. I
>> suggest you move the files back and then follow instructions in either
>> KB 224071 if on 2000 or Books Online of on 2005 on how to move files for
>> msdb.
>> Also, there *are* error messages for msdb to be found in the errorlog
>> file, you just need to find the right errorlog file (first startup when
>> it became suspect).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Fei" <Fei@.discussions.microsoft.com> wrote in message
>> news:36CB5CBE-2373-4261-97C0-1AA615E564E4@.microsoft.com...
>> Tibor,
>> Thanks for your quick response.
>> I checked the errorlog and there is no related error msg found in the
>> log.
>> The msdb files are on a different location (e:drive) from the original
>> installation (c: drive)because we wanted to seperate the datafiles from
>> the
>> software files. The other system database files such as master, model
>> are all
>> in the same location as the msdb and the other system databases are
>> just
>> fine.
>> Any suggestions?
>>
>> Fei
>> "Tibor Karaszi" wrote:
>> Check out the SQL Server errorlog why it became suspect. Perhaps you
>> don't have the files for msdb
>> in the same directory as the original SQL Server, for instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Fei" <Fei@.discussions.microsoft.com> wrote in message
>> news:81056E87-078C-4E54-91EA-D2A7273B4554@.microsoft.com...
>> > After move the whole sql server databases to a new box, msdb shows
>> > suspect,
>> > the other databases all work fine.
>> >
>> > A select statement shows that the staus of msdb is 280.
>> >
>> > After I change the status to 24, it still does not work. I can't
>> > start
>> > SQLServerAgent now.
>> >
>> > Please help!!!
>> >
>> > thanks, Fei
>>
>>
>

msdb Single user

my msdb has been put into single user mode and i cant get it back to
multi_user can anyone help me with this
What put it in single user mode? Did you check the logs?
If it was a user action, did you try executing the
following:
ALTER DATABASE msdb
SET MULTI_USER
-Sue
On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
<AB@.discussions.microsoft.com> wrote:

>my msdb has been put into single user mode and i cant get it back to
>multi_user can anyone help me with this
|||I tried that but it said there was already a connection and could not alter
the database. I had to shutdown the SQL Server Agent in order to stop the
processes that are running in the background on that table. Then I could
execute the alter database statement.
"Sue Hoegemeier" wrote:

> What put it in single user mode? Did you check the logs?
> If it was a user action, did you try executing the
> following:
> ALTER DATABASE msdb
> SET MULTI_USER
> -Sue
> On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
> <AB@.discussions.microsoft.com> wrote:
>
>

msdb Single user

my msdb has been put into single user mode and i cant get it back to
multi_user can anyone help me with thisWhat put it in single user mode? Did you check the logs?
If it was a user action, did you try executing the
following:
ALTER DATABASE msdb
SET MULTI_USER
-Sue
On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
<AB@.discussions.microsoft.com> wrote:
>my msdb has been put into single user mode and i cant get it back to
>multi_user can anyone help me with this|||I tried that but it said there was already a connection and could not alter
the database. I had to shutdown the SQL Server Agent in order to stop the
processes that are running in the background on that table. Then I could
execute the alter database statement.
"Sue Hoegemeier" wrote:
> What put it in single user mode? Did you check the logs?
> If it was a user action, did you try executing the
> following:
> ALTER DATABASE msdb
> SET MULTI_USER
> -Sue
> On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
> <AB@.discussions.microsoft.com> wrote:
> >my msdb has been put into single user mode and i cant get it back to
> >multi_user can anyone help me with this
>

msdb Single user

my msdb has been put into single user mode and i cant get it back to
multi_user can anyone help me with thisWhat put it in single user mode? Did you check the logs?
If it was a user action, did you try executing the
following:
ALTER DATABASE msdb
SET MULTI_USER
-Sue
On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
<AB@.discussions.microsoft.com> wrote:

>my msdb has been put into single user mode and i cant get it back to
>multi_user can anyone help me with this|||I tried that but it said there was already a connection and could not alter
the database. I had to shutdown the SQL Server Agent in order to stop the
processes that are running in the background on that table. Then I could
execute the alter database statement.
"Sue Hoegemeier" wrote:

> What put it in single user mode? Did you check the logs?
> If it was a user action, did you try executing the
> following:
> ALTER DATABASE msdb
> SET MULTI_USER
> -Sue
> On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
> <AB@.discussions.microsoft.com> wrote:
>
>

msdb restore?

A terrible event has occurred. Our SQL Server 2000 database was attacked an
d
the master, msdb, etc was deleted. We used a utility to undelete those file
s
and have saved them. We had to reinstall Windows and SQL Server, but am
unable to replace the msdb. We have no backup so that isn't an option. Is
there a way to restore our DTS packages from the original msdb? Thanks.Did you try attaching it with a different name?
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:104A0726-0C71-4A29-B3A8-DF21C9158F38@.microsoft.com...
> A terrible event has occurred. Our SQL Server 2000 database was attacked
and
> the master, msdb, etc was deleted. We used a utility to undelete those
files
> and have saved them. We had to reinstall Windows and SQL Server, but am
> unable to replace the msdb. We have no backup so that isn't an option.
Is
> there a way to restore our DTS packages from the original msdb? Thanks.|||I tried but I get the following error:
I/O error 38(Reached the end of the file.) detected during read at offset
0000000000000000 in file 'C:\Mymsdbdata.mdf'.
Connection Broken
"Aaron [SQL Server MVP]" wrote:

> Did you try attaching it with a different name?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:104A0726-0C71-4A29-B3A8-DF21C9158F38@.microsoft.com...
> and
> files
> Is
>
>|||> I tried but I get the following error:
What *exactly* did you try? I see that you renamed the MDF file, I meant to
attach as a different database name.
If the file is corrupt, and you may be S.O.L. (this is what backups are
for).
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F9540632-EF19-41D8-A3FD-572722B13190@.microsoft.com...[vbcol=seagreen]
> I/O error 38(Reached the end of the file.) detected during read at offset
> 0000000000000000 in file 'C:\Mymsdbdata.mdf'.
> Connection Broken
> "Aaron [SQL Server MVP]" wrote:
>
attacked[vbcol=seagreen]
those[vbcol=seagreen]
am[vbcol=seagreen]
option.[vbcol=seagreen]
Thanks.[vbcol=seagreen]|||I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
ran the following in Query Analyser:
exec sp_attach_db @.dbname =
N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filename2=N'C:\Mymsdblog.ldf'
"Aaron [SQL Server MVP]" wrote:

> What *exactly* did you try? I see that you renamed the MDF file, I meant
to
> attach as a different database name.
> If the file is corrupt, and you may be S.O.L. (this is what backups are
> for).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:F9540632-EF19-41D8-A3FD-572722B13190@.microsoft.com...
> attacked
> those
> am
> option.
> Thanks.
>
>|||The data file is 0k, but the log file has data in it. I'm hoping that since
msdb was never backed up that it can be rebuilt using the log file. Can thi
s
be done?
"Phill" wrote:
[vbcol=seagreen]
> I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
> ran the following in Query Analyser:
> exec sp_attach_db @.dbname =
> N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filename2=N'C:\Mymsdblog.ldf'
> "Aaron [SQL Server MVP]" wrote:
>|||I think you may have to open a case with PSS, or cut your losses. In the
future: backup is a very important word.
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:6B1824AD-016E-41CE-A851-889622057227@.microsoft.com...[vbcol=seagreen]
> I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
> ran the following in Query Analyser:
> exec sp_attach_db @.dbname =
> N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filename2=N'C:\Mymsdblog.ldf'
> "Aaron [SQL Server MVP]" wrote:
>
meant to[vbcol=seagreen]
offset[vbcol=seagreen]
but[vbcol=seagreen]|||Thanks Aaron. I knida knew that answer but didn't want to hear it.
"Aaron [SQL Server MVP]" wrote:

> I think you may have to open a case with PSS, or cut your losses. In the
> future: backup is a very important word.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:6B1824AD-016E-41CE-A851-889622057227@.microsoft.com...
> meant to
> offset
> but
>
>

msdb restore?

A terrible event has occurred. Our SQL Server 2000 database was attacked and
the master, msdb, etc was deleted. We used a utility to undelete those files
and have saved them. We had to reinstall Windows and SQL Server, but am
unable to replace the msdb. We have no backup so that isn't an option. Is
there a way to restore our DTS packages from the original msdb? Thanks.
Did you try attaching it with a different name?
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:104A0726-0C71-4A29-B3A8-DF21C9158F38@.microsoft.com...
> A terrible event has occurred. Our SQL Server 2000 database was attacked
and
> the master, msdb, etc was deleted. We used a utility to undelete those
files
> and have saved them. We had to reinstall Windows and SQL Server, but am
> unable to replace the msdb. We have no backup so that isn't an option.
Is
> there a way to restore our DTS packages from the original msdb? Thanks.
|||I tried but I get the following error:
I/O error 38(Reached the end of the file.) detected during read at offset
0000000000000000 in file 'C:\Mymsdbdata.mdf'.
Connection Broken
"Aaron [SQL Server MVP]" wrote:

> Did you try attaching it with a different name?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:104A0726-0C71-4A29-B3A8-DF21C9158F38@.microsoft.com...
> and
> files
> Is
>
>
|||> I tried but I get the following error:
What *exactly* did you try? I see that you renamed the MDF file, I meant to
attach as a different database name.
If the file is corrupt, and you may be S.O.L. (this is what backups are
for).
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F9540632-EF19-41D8-A3FD-572722B13190@.microsoft.com...[vbcol=seagreen]
> I/O error 38(Reached the end of the file.) detected during read at offset
> 0000000000000000 in file 'C:\Mymsdbdata.mdf'.
> Connection Broken
> "Aaron [SQL Server MVP]" wrote:
attacked[vbcol=seagreen]
those[vbcol=seagreen]
am[vbcol=seagreen]
option.[vbcol=seagreen]
Thanks.[vbcol=seagreen]
|||I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
ran the following in Query Analyser:
exec sp_attach_db @.dbname =
N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filenam e2=N'C:\Mymsdblog.ldf'
"Aaron [SQL Server MVP]" wrote:

> What *exactly* did you try? I see that you renamed the MDF file, I meant to
> attach as a different database name.
> If the file is corrupt, and you may be S.O.L. (this is what backups are
> for).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:F9540632-EF19-41D8-A3FD-572722B13190@.microsoft.com...
> attacked
> those
> am
> option.
> Thanks.
>
>
|||The data file is 0k, but the log file has data in it. I'm hoping that since
msdb was never backed up that it can be rebuilt using the log file. Can this
be done?
"Phill" wrote:
[vbcol=seagreen]
> I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
> ran the following in Query Analyser:
> exec sp_attach_db @.dbname =
> N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filenam e2=N'C:\Mymsdblog.ldf'
> "Aaron [SQL Server MVP]" wrote:
|||I think you may have to open a case with PSS, or cut your losses. In the
future: backup is a very important word.
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:6B1824AD-016E-41CE-A851-889622057227@.microsoft.com...[vbcol=seagreen]
> I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
> ran the following in Query Analyser:
> exec sp_attach_db @.dbname =
> N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filenam e2=N'C:\Mymsdblog.ldf'
> "Aaron [SQL Server MVP]" wrote:
meant to[vbcol=seagreen]
offset[vbcol=seagreen]
but[vbcol=seagreen]
|||Thanks Aaron. I knida knew that answer but didn't want to hear it.
"Aaron [SQL Server MVP]" wrote:

> I think you may have to open a case with PSS, or cut your losses. In the
> future: backup is a very important word.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:6B1824AD-016E-41CE-A851-889622057227@.microsoft.com...
> meant to
> offset
> but
>
>

msdb Restore......

I have restored the production msdb database on a test box. I can see my
entire DTS package and the scheduled jobs.
When I try to modify the job I am getting the following error.
Error 14274: Cannot add, update, or delete a job (or its steps or schedules)
that originated from an MSZSERVER. The job was not saved. OK Button
How to delete/update then?
SQL 2K.
Thank you,
Benjamin
Dear Benjamin,
Is the server was renamed from one name to another, if so see the
logins were in sync, another work around is
The best way to handle this problem after the rename process is to follow
these steps: 1. Rename the server back to the original name.
2. Script out all of the jobs and then delete them.
3. Rename the server to the new name.
4. Add back the jobs by running the script generated from step 2.
Or
1.Script the jobs in the server from where u backed up the msdb database.
2.Run the scripts in the new server.
for more details go through the following kb site
http://support.microsoft.com/default...b;en-us;281642
"Benjamin Nethan" wrote:

> I have restored the production msdb database on a test box. I can see my
> entire DTS package and the scheduled jobs.
> When I try to modify the job I am getting the following error.
> Error 14274: Cannot add, update, or delete a job (or its steps or schedules)
> that originated from an MSZSERVER. The job was not saved. OK Button
> How to delete/update then?
> SQL 2K.
> Thank you,
> Benjamin
>
>
>
|||You need to update the originating_server column in sysjobs to reflect the
name of the instance you restored the database to. You can use the following
code to do that http://sqldev.net/download/sqlagent/...ent_rename.sql
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
"Benjamin Nethan" <BNethan@.hotmail.com> wrote in message
news:e59AAE5pFHA.820@.TK2MSFTNGP09.phx.gbl...
>I have restored the production msdb database on a test box. I can see my
> entire DTS package and the scheduled jobs.
> When I try to modify the job I am getting the following error.
> Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules)
> that originated from an MSZSERVER. The job was not saved. OK Button
> How to delete/update then?
> SQL 2K.
> Thank you,
> Benjamin
>
>

msdb Restore......

I have restored the production msdb database on a test box. I can see my
entire DTS package and the scheduled jobs.
When I try to modify the job I am getting the following error.
Error 14274: Cannot add, update, or delete a job (or its steps or schedules)
that originated from an MSZSERVER. The job was not saved. OK Button
How to delete/update then?
SQL 2K.
Thank you,
BenjaminDear Benjamin,
Is the server was renamed from one name to another, if so see the
logins were in sync, another work around is
The best way to handle this problem after the rename process is to follow
these steps: 1. Rename the server back to the original name.
2. Script out all of the jobs and then delete them.
3. Rename the server to the new name.
4. Add back the jobs by running the script generated from step 2.
Or
1.Script the jobs in the server from where u backed up the msdb database.
2.Run the scripts in the new server.
for more details go through the following kb site
http://support.microsoft.com/default.aspx?scid=kb;en-us;281642
"Benjamin Nethan" wrote:
> I have restored the production msdb database on a test box. I can see my
> entire DTS package and the scheduled jobs.
> When I try to modify the job I am getting the following error.
> Error 14274: Cannot add, update, or delete a job (or its steps or schedules)
> that originated from an MSZSERVER. The job was not saved. OK Button
> How to delete/update then?
> SQL 2K.
> Thank you,
> Benjamin
>
>
>|||You need to update the originating_server column in sysjobs to reflect the
name of the instance you restored the database to. You can use the following
code to do that http://sqldev.net/download/sqlagent/sp_sqlagent_rename.sql
--
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
"Benjamin Nethan" <BNethan@.hotmail.com> wrote in message
news:e59AAE5pFHA.820@.TK2MSFTNGP09.phx.gbl...
>I have restored the production msdb database on a test box. I can see my
> entire DTS package and the scheduled jobs.
> When I try to modify the job I am getting the following error.
> Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules)
> that originated from an MSZSERVER. The job was not saved. OK Button
> How to delete/update then?
> SQL 2K.
> Thank you,
> Benjamin
>
>

msdb Restore......

I have restored the production msdb database on a test box. I can see my
entire DTS package and the scheduled jobs.
When I try to modify the job I am getting the following error.
Error 14274: Cannot add, update, or delete a job (or its steps or schedules)
that originated from an MSZSERVER. The job was not saved. OK Button
How to delete/update then?
SQL 2K.
Thank you,
BenjaminDear Benjamin,
Is the server was renamed from one name to another, if so see the
logins were in sync, another work around is
The best way to handle this problem after the rename process is to follow
these steps: 1. Rename the server back to the original name.
2. Script out all of the jobs and then delete them.
3. Rename the server to the new name.
4. Add back the jobs by running the script generated from step 2.
Or
1.Script the jobs in the server from where u backed up the msdb database.
2.Run the scripts in the new server.
for more details go through the following kb site
http://support.microsoft.com/defaul...kb;en-us;281642
"Benjamin Nethan" wrote:

> I have restored the production msdb database on a test box. I can see my
> entire DTS package and the scheduled jobs.
> When I try to modify the job I am getting the following error.
> Error 14274: Cannot add, update, or delete a job (or its steps or schedule
s)
> that originated from an MSZSERVER. The job was not saved. OK Button
> How to delete/update then?
> SQL 2K.
> Thank you,
> Benjamin
>
>
>|||You need to update the originating_server column in sysjobs to reflect the
name of the instance you restored the database to. You can use the following
code to do that http://sqldev.net/download/sqlagent...gent_rename.sql
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
"Benjamin Nethan" <BNethan@.hotmail.com> wrote in message
news:e59AAE5pFHA.820@.TK2MSFTNGP09.phx.gbl...
>I have restored the production msdb database on a test box. I can see my
> entire DTS package and the scheduled jobs.
> When I try to modify the job I am getting the following error.
> Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules)
> that originated from an MSZSERVER. The job was not saved. OK Button
> How to delete/update then?
> SQL 2K.
> Thank you,
> Benjamin
>
>

msdb restore to standby server

Can the msdb database safely be moved to a new server by taking a backup fro
m
the source server and restoring to the target? I am aware that
sysjobs.originating_server must be updated after the restore and that
Microsoft suggests transfering the msdb by scripting jobs, dts packages, etc
.SQL Server 2000 sp3
"Brandon" wrote:

> Can the msdb database safely be moved to a new server by taking a backup f
rom
> the source server and restoring to the target? I am aware that
> sysjobs.originating_server must be updated after the restore and that
> Microsoft suggests transfering the msdb by scripting jobs, dts packages, e
tc.
>|||Ive never had good luck doing that and just would up going the MS route.
--
TIA,
ChrisR
"Brandon" wrote:

> Can the msdb database safely be moved to a new server by taking a backup f
rom
> the source server and restoring to the target? I am aware that
> sysjobs.originating_server must be updated after the restore and that
> Microsoft suggests transfering the msdb by scripting jobs, dts packages, e
tc.
>

msdb restore to standby server

Can the msdb database safely be moved to a new server by taking a backup from
the source server and restoring to the target? I am aware that
sysjobs.originating_server must be updated after the restore and that
Microsoft suggests transfering the msdb by scripting jobs, dts packages, etc.
SQL Server 2000 sp3
"Brandon" wrote:

> Can the msdb database safely be moved to a new server by taking a backup from
> the source server and restoring to the target? I am aware that
> sysjobs.originating_server must be updated after the restore and that
> Microsoft suggests transfering the msdb by scripting jobs, dts packages, etc.
>
|||Ive never had good luck doing that and just would up going the MS route.
TIA,
ChrisR
"Brandon" wrote:

> Can the msdb database safely be moved to a new server by taking a backup from
> the source server and restoring to the target? I am aware that
> sysjobs.originating_server must be updated after the restore and that
> Microsoft suggests transfering the msdb by scripting jobs, dts packages, etc.
>

msdb restore to standby server

Can the msdb database safely be moved to a new server by taking a backup from
the source server and restoring to the target? I am aware that
sysjobs.originating_server must be updated after the restore and that
Microsoft suggests transfering the msdb by scripting jobs, dts packages, etc.SQL Server 2000 sp3
"Brandon" wrote:
> Can the msdb database safely be moved to a new server by taking a backup from
> the source server and restoring to the target? I am aware that
> sysjobs.originating_server must be updated after the restore and that
> Microsoft suggests transfering the msdb by scripting jobs, dts packages, etc.
>|||Ive never had good luck doing that and just would up going the MS route.
--
TIA,
ChrisR
"Brandon" wrote:
> Can the msdb database safely be moved to a new server by taking a backup from
> the source server and restoring to the target? I am aware that
> sysjobs.originating_server must be updated after the restore and that
> Microsoft suggests transfering the msdb by scripting jobs, dts packages, etc.
>

msdb restore

Hello --
Using SQL 2000. When performing a trans log restore to our backup SQL box
the server locked up. After I got the server restarted the msdb was marked
'suspect'. I don't have a backup of the msdb. I can still perform
restores, but I get error message about the msdb. Do I need to re-install
SQL on this box? I'm not sure what to do (besides doing msdb backups from
now on). Any help would be appreciated. Thank you.
Hi,
No need to install SQL Server.
Start SQL Server with Trace 3608 from command prompt
SQLSERVER -T3608 -m
Drop or detach the MSDB database and rebuild the MSDB using the installation
script(INSTMSDB.SQL) in the SLQ Server install folder. But after this
you need to create all your sql agent jobs manually. Please ensure that you
do a backup of MSDB database hereafter
One more solution-
Start SQL Server with Trace 3608 from command prompt
SQLSERVER -T3608 -m
From query analyzer Detach MSDB database, copy the files from the CD (in
x86\Data) and then attach this files.
Thanks
Hari
SQL Server MVP
"FRR" <FRR@.discussions.microsoft.com> wrote in message
news:51EACBD9-BB21-4FB6-9C2F-2A5C5B74D083@.microsoft.com...
> Hello --
> Using SQL 2000. When performing a trans log restore to our backup SQL box
> the server locked up. After I got the server restarted the msdb was
> marked
> 'suspect'. I don't have a backup of the msdb. I can still perform
> restores, but I get error message about the msdb. Do I need to re-install
> SQL on this box? I'm not sure what to do (besides doing msdb backups from
> now on). Any help would be appreciated. Thank you.

msdb restore

I have a client who is trying to restore an old backup of
the msdb database. Of course, their new build of SQL is
different, so they can't do the restore. We get an error
similar to:
"Server: Msg 3168, Level 16, State 1, Line 1
The backup of the system database on device
d:\temp\master.bak cannot be restored because it was
created by a different version of the server (134217904)
than this server (134217920).
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally. "
what I need to know is how to interpret the version number
in the error message - i.e. "134217904" so I can figure
out what version they need to have running in order to
restore. Anyone have any ideas?
Thanks!Take a look at Q264474. That will help you :-)
/Peter
"Lia" <lkeston@.patriotsoftware.biz> wrote in message
news:4cdc01c3d5fb$f1edd8b0$7d02280a@.phx.gbl...
> I have a client who is trying to restore an old backup of
> the msdb database. Of course, their new build of SQL is
> different, so they can't do the restore. We get an error
> similar to:
> "Server: Msg 3168, Level 16, State 1, Line 1
> The backup of the system database on device
> d:\temp\master.bak cannot be restored because it was
> created by a different version of the server (134217904)
> than this server (134217920).
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally. "
> what I need to know is how to interpret the version number
> in the error message - i.e. "134217904" so I can figure
> out what version they need to have running in order to
> restore. Anyone have any ideas?
> Thanks!
>|||The following command should tell you what build of SQL Server the backup
was taken from:
RESTORE HEADERONLY
FROM DISK = 'd:\temp\master.bak '
You can look at http://support.microsoft.com/?id=264474 for further
information.
Rand
This posting is provided "as is" with no warranties and confers no rights.

msdb restore

Hello --
Using SQL 2000. When performing a trans log restore to our backup SQL box
the server locked up. After I got the server restarted the msdb was marked
'suspect'. I don't have a backup of the msdb. I can still perform
restores, but I get error message about the msdb. Do I need to re-install
SQL on this box? I'm not sure what to do (besides doing msdb backups from
now on). Any help would be appreciated. Thank you.Hi,
No need to install SQL Server.
Start SQL Server with Trace 3608 from command prompt
SQLSERVER -T3608 -m
Drop or detach the MSDB database and rebuild the MSDB using the installation
script(INSTMSDB.SQL) in the SLQ Server install folder. But after this
you need to create all your sql agent jobs manually. Please ensure that you
do a backup of MSDB database hereafter :)
One more solution-
Start SQL Server with Trace 3608 from command prompt
SQLSERVER -T3608 -m
From query analyzer Detach MSDB database, copy the files from the CD (in
x86\Data) and then attach this files.
Thanks
Hari
SQL Server MVP
"FRR" <FRR@.discussions.microsoft.com> wrote in message
news:51EACBD9-BB21-4FB6-9C2F-2A5C5B74D083@.microsoft.com...
> Hello --
> Using SQL 2000. When performing a trans log restore to our backup SQL box
> the server locked up. After I got the server restarted the msdb was
> marked
> 'suspect'. I don't have a backup of the msdb. I can still perform
> restores, but I get error message about the msdb. Do I need to re-install
> SQL on this box? I'm not sure what to do (besides doing msdb backups from
> now on). Any help would be appreciated. Thank you.

msdb restore

Hello --
Using SQL 2000. When performing a trans log restore to our backup SQL box
the server locked up. After I got the server restarted the msdb was marked
'suspect'. I don't have a backup of the msdb. I can still perform
restores, but I get error message about the msdb. Do I need to re-install
SQL on this box? I'm not sure what to do (besides doing msdb backups from
now on). Any help would be appreciated. Thank you.Hi,
No need to install SQL Server.
Start SQL Server with Trace 3608 from command prompt
SQLSERVER -T3608 -m
Drop or detach the MSDB database and rebuild the MSDB using the installation
script(INSTMSDB.SQL) in the SLQ Server install folder. But after this
you need to create all your sql agent jobs manually. Please ensure that you
do a backup of MSDB database hereafter
One more solution-
Start SQL Server with Trace 3608 from command prompt
SQLSERVER -T3608 -m
From query analyzer Detach MSDB database, copy the files from the CD (in
x86\Data) and then attach this files.
Thanks
Hari
SQL Server MVP
"FRR" <FRR@.discussions.microsoft.com> wrote in message
news:51EACBD9-BB21-4FB6-9C2F-2A5C5B74D083@.microsoft.com...
> Hello --
> Using SQL 2000. When performing a trans log restore to our backup SQL box
> the server locked up. After I got the server restarted the msdb was
> marked
> 'suspect'. I don't have a backup of the msdb. I can still perform
> restores, but I get error message about the msdb. Do I need to re-install
> SQL on this box? I'm not sure what to do (besides doing msdb backups from
> now on). Any help would be appreciated. Thank you.

msdb renamed to msbd

Hello,
Yesterday i was trying to install a backup client for sql on our sql server,
it kept on failing so i contacted the software vendor to see if there was a
problem with the client. after they poked around on our sql server they rea
lized that one of the datab
ases had been renamed, for what reason i dont know. The msdb database was re
named to msbd. This is the reason that we can not install the backup agent s
oftware. Does anyone out there know how to rename the database, the physical
name of the database file
is fine its the display name inside of Enterprise manager.
ThanksHi,
Refer sp_renamedb in books online
sp_renamedb [ @.dbname = ] 'old_name' ,
[ @.newname = ] 'new_name'
Thanks
Hari
MCDBA
"Brian Butler" <anonymous@.discussions.microsoft.com> wrote in message
news:564BE6BC-5201-4828-A0B3-9E1450653B3A@.microsoft.com...
> Hello,
> Yesterday i was trying to install a backup client for sql on our sql
server, it kept on failing so i contacted the software vendor to see if
there was a problem with the client. after they poked around on our sql
server they realized that one of the databases had been renamed, for what
reason i dont know. The msdb database was renamed to msbd. This is the
reason that we can not install the backup agent software. Does anyone out
there know how to rename the database, the physical name of the database
file is fine its the display name inside of Enterprise manager.
> Thanks|||In SQL 2000, an alternative to sp_renamedb is ALTER DATABASE:
ALTER DATABASE msbd
MODIFY NAME = msdb
Hope this helps.
Dan Guzman
SQL Server MVP
"Brian Butler" <anonymous@.discussions.microsoft.com> wrote in message
news:564BE6BC-5201-4828-A0B3-9E1450653B3A@.microsoft.com...
> Hello,
> Yesterday i was trying to install a backup client for sql on our sql
server, it kept on failing so i contacted the software vendor to see if
there was a problem with the client. after they poked around on our sql
server they realized that one of the databases had been renamed, for what
reason i dont know. The msdb database was renamed to msbd. This is the
reason that we can not install the backup agent software. Does anyone out
there know how to rename the database, the physical name of the database
file is fine its the display name inside of Enterprise manager.
> Thanks

msdb refuses to back up w/ plan

All,
I've created (and re-created) a Mx plan for the system databases.
Part of it is a complete backup. All the default options in the
wizard were chosen, including the option to verify.
Master and Model back up fine. MSDB, however, fails. According to
the Mx Plan log, the backup completes successfully, but the verify
fails as follows:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backu
p
device 'd:\sql_data\MSSQL\BACKUP\msdb_db_200407
042207.BAK'. Device
error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE i
s
terminating abnormally.
1- It doesn't appear that the backup actually succeeded, as there is
no msdb_db_200407042207.bak file in the directory.
2- There is no additional information in the SQL Server error log
3- I can successfully back up msdb -manually- without a hiccup.
I've tried:
1- Recreating a new Mx Plan from scratch (no effect)
2- Can full index/optimizations on msdb (no errors found)
3- Tried shrinking the database (it's 215MBish) to make it faster
4- Tried a full backup/restore of msdb in case it had any mystery
corruption (no effect)
Oh yes, SQL Server standard 2000 sp3 on W2.3K standard. NTFS
partition. SQL Agent using a domain account, SQL Server running under
the same account.
Can anyone shed light on this? I can come up with no reason why the
Mx plan would fail for just this one database, when I can back it up
just fine manually.
GeofHere's some more info:
I did find some additional information in the app event log, giving the spec
ific T-SQL that was failing:
BACKUP DATABASE [msdb] TO DISK = N'd:\sql_data\MSSQL\BACKUP\msdb_db_200
407042238.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
After some pondering and re-reading my own post, I went ahead and logged int
o the server interactively using SQL Server's account. I attempted to execu
te the above SQL and lo and behond, it failed (General Network Error). When
executed under my normal U
serID, it executed fine.
Trial and error then produced the rather odd discovery that the problem part
of the statement is the seemingly innocuous "STATS=10" command. If the sam
e statement is run under the SQL Server account EXCEPT for removing the stat
s=10 command, it works fine|||Go the Maintenance Plan and right click and view maintenance history out
there ...Thats where most of the reasoning is for the failure..
"Geof" <Geof@.discussions.microsoft.com> wrote in message
news:C6436F94-BF81-41FD-9869-5595F7FBA6B4@.microsoft.com...
> Here's some more info:
> I did find some additional information in the app event log, giving the
specific T-SQL that was failing:
> BACKUP DATABASE [msdb] TO DISK =
N'd:\sql_data\MSSQL\BACKUP\msdb_db_20040
7042238.BAK' WITH INIT , NOUNLOAD
, NOSKIP , STATS = 10, NOFORMAT
> After some pondering and re-reading my own post, I went ahead and logged
into the server interactively using SQL Server's account. I attempted to
execute the above SQL and lo and behond, it failed (General Network Error).
When executed under my normal UserID, it executed fine.
> Trial and error then produced the rather odd discovery that the problem
part of the statement is the seemingly innocuous "STATS=10" command. If the
same statement is run under the SQL Server account EXCEPT for removing the
stats=10 command, it works fine.
> While I've gotten more information on exactly what is causing the problem,
I:
> 1- Have no idea why the problem is occurring, and
> 2- Don't know a way around it (other than backup w/o using a Mx Plan)
> My nearest stab would be that this particular T-SQL (and STATS option in
particular) is causing some sort of collision between SQL Server and SQL
Server Agent since they would both be running under the same account. (But
surely I'm not the only one in the history of SQL Server to have created a
sysdb mx plan w/ both SQL and SQL Agent running under an identical domain
account, huh')
> Ideas'|||In my first post I listed what was in the Mx Plan History. Does
anyone have any other ideas?
Geof
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:<#GDGyveYEHA.996@.TK2MSFTNGP12.phx.gbl
>...
> Go the Maintenance Plan and right click and view maintenance history out
> there ...Thats where most of the reasoning is for the failure..