MSDB Recovery Model Option

Hi,
On one of our servers, whenever there is a system reboot the recovery model
option on the MSDB database is reset to 'Simple'. This is causing the
Transaction Log to fail. At that point I have to go back and change it to
'Full' again. Is there any setting/configuration that I can change so the
recovery model is not changed and is always 'Full' like I setup. Any kind of
input on this is very much appreciated.
Thank you
This is by design. Leave the MSDB database in simple recovery and do more
frequent full backups. SQL does not like full recovery for system
databases.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:C862E63E-B996-43A8-8936-7C691D15C0F0@.microsoft.com...
> Hi,
> On one of our servers, whenever there is a system reboot the recovery
> model
> option on the MSDB database is reset to 'Simple'. This is causing the
> Transaction Log to fail. At that point I have to go back and change it to
> 'Full' again. Is there any setting/configuration that I can change so the
> recovery model is not changed and is always 'Full' like I setup. Any kind
> of
> input on this is very much appreciated.
> Thank you
>
|||Thank you very very much Geoff.
"Geoff N. Hiten" wrote:

> This is by design. Leave the MSDB database in simple recovery and do more
> frequent full backups. SQL does not like full recovery for system
> databases.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "helpplease" <helpplease@.discussions.microsoft.com> wrote in message
> news:C862E63E-B996-43A8-8936-7C691D15C0F0@.microsoft.com...
>
>

MSDB Recovery Model Option

Hi,
On one of our servers, whenever there is a system reboot the recovery model
option on the MSDB database is reset to 'Simple'. This is causing the
Transaction Log to fail. At that point I have to go back and change it to
'Full' again. Is there any setting/configuration that I can change so the
recovery model is not changed and is always 'Full' like I setup. Any kind of
input on this is very much appreciated.
Thank youThis is by design. Leave the MSDB database in simple recovery and do more
frequent full backups. SQL does not like full recovery for system
databases.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:C862E63E-B996-43A8-8936-7C691D15C0F0@.microsoft.com...
> Hi,
> On one of our servers, whenever there is a system reboot the recovery
> model
> option on the MSDB database is reset to 'Simple'. This is causing the
> Transaction Log to fail. At that point I have to go back and change it to
> 'Full' again. Is there any setting/configuration that I can change so the
> recovery model is not changed and is always 'Full' like I setup. Any kind
> of
> input on this is very much appreciated.
> Thank you
>|||Thank you very very much Geoff.
"Geoff N. Hiten" wrote:
> This is by design. Leave the MSDB database in simple recovery and do more
> frequent full backups. SQL does not like full recovery for system
> databases.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "helpplease" <helpplease@.discussions.microsoft.com> wrote in message
> news:C862E63E-B996-43A8-8936-7C691D15C0F0@.microsoft.com...
> > Hi,
> >
> > On one of our servers, whenever there is a system reboot the recovery
> > model
> > option on the MSDB database is reset to 'Simple'. This is causing the
> > Transaction Log to fail. At that point I have to go back and change it to
> > 'Full' again. Is there any setting/configuration that I can change so the
> > recovery model is not changed and is always 'Full' like I setup. Any kind
> > of
> > input on this is very much appreciated.
> >
> > Thank you
> >
>
>

MSDB Recovery Model Option

Hi,
On one of our servers, whenever there is a system reboot the recovery model
option on the MSDB database is reset to 'Simple'. This is causing the
Transaction Log to fail. At that point I have to go back and change it to
'Full' again. Is there any setting/configuration that I can change so the
recovery model is not changed and is always 'Full' like I setup. Any kind o
f
input on this is very much appreciated.
Thank youThis is by design. Leave the MSDB database in simple recovery and do more
frequent full backups. SQL does not like full recovery for system
databases.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:C862E63E-B996-43A8-8936-7C691D15C0F0@.microsoft.com...
> Hi,
> On one of our servers, whenever there is a system reboot the recovery
> model
> option on the MSDB database is reset to 'Simple'. This is causing the
> Transaction Log to fail. At that point I have to go back and change it to
> 'Full' again. Is there any setting/configuration that I can change so the
> recovery model is not changed and is always 'Full' like I setup. Any kind
> of
> input on this is very much appreciated.
> Thank you
>|||Thank you very very much Geoff.
"Geoff N. Hiten" wrote:

> This is by design. Leave the MSDB database in simple recovery and do more
> frequent full backups. SQL does not like full recovery for system
> databases.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "helpplease" <helpplease@.discussions.microsoft.com> wrote in message
> news:C862E63E-B996-43A8-8936-7C691D15C0F0@.microsoft.com...
>
>

MSDB Database SUSPECT - RESET PROCEDURE ?

I have a regional server running SQL2000 and Merge replication, the MSDB, NORTHWIND and 2 User DB's have been marked suspect. I cannot get into query analyzer etc to run the necessary scripts as it is all suspect.

Any ideas on how I can reset the status on the MSDB database??
Unfortunately there is no backup of either of the 2 system databases :(

** ANY ASSISTANCE WOULD BE GREATLY APPRECIATED **

JReset database with "suspect" status:

Do the following steps from the Query Analyzer:

1) Use Master
GO
EXEC sp_resetstatus <dbname>
2) Stop & Start the SQL Server
3) Run DBCC CHECKDB
4) Do the Full database backup (Master DB included)

If the above steps didn't resolve the problem, run the query below:

Use Master
GO
EXEC sp_configure 'allow updates', 1
GO
Reconfigure with override
GO

Do the step 1 ~ 4 above, then:

Use Master
GO
EXEC sp_configure 'allow updates', 0 -- to turn off
GO
Reconfigure with override
GO

NOTE: If the database become "suspect" because it ran out of disk space, you have to do the step #1 above, then use the "ALTER DATABASE ..." to add more data file (or log file), Stop & start the SQL Server and finish with step #3 & #4 as above.

Good luck.|||Valid reponse by SVT and make sure to have regular backups for SYSTEM Databases too in future.