Re: MSDB (Subspect)

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 04/20/04 

Date: Tue, 20 Apr 2004 13:27:10 +0200

Probably because the database was damaged when you took the backup. Do you perform regular DBCC checks against
the MSDB database?

You can always rebuild the system databases (rebuildm.exe). This is a rather advanced option, don't do this
unless you know what happens and how it work.

Or perhaps use another SQL Server. Disconnect from network. Make sure you have the same machine name. Install
SQL Server. Make sure you select the same collation as the production machine. Stop that SQL Server. Copy the
files from the msdb database to the production SQL Server. You will of course have an "empty" msdb database
now. This should work, but is most probably not supported.

Also, I think that you can run the script file which creates the msdb database. You probably need to set some
trace flag to allow you to drop the msdb database in the first place. This could be documented in KB 224071. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Philip" <anonymous@discussions.microsoft.com> wrote in message
news:5ACC3100-003F-4686-9AC7-06603C8EB86E@microsoft.com...
>
> Hi,
>
> I have already tried restore the msdb database from the backup but don't know why still subspected.....
>
> Any Idea?
>      ----- Tibor Karaszi wrote: -----
>
>      Just restore your latest backup of the msdb database. Msdb is just like any other database when it
comes to
>      handling a suspect database. General recommendations for handling a suspect database:
>
>      http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>
>      -- 
>      Tibor Karaszi, SQL Server MVP
>      http://www.karaszi.com/sqlserver/default.asp
>
>
>      "Philip" <zeta_01@hotmail.com> wrote in message
news:A51A7CEF-958B-4911-B647-810C27A2DCE4@microsoft.com...
>      > Does anyone know how I can recover the MSDB once it is not subspect?
>      >> The reason is the server was down during the backup job running in night and I have already copy the
>      database file to the orginial location but only the MSDB was subspected....
>      >>

Relevant Pages

  • Re: What is stored in MSDB database for SQL Server 2005 Express ?
    ... At least it does on my SQL Server 2005 Express test machine. ... N'Past day stats rollup', @command = N'EXECUTE stats_rollup1_proc', ... GOTO QuitWithRollback END COMMIT TRANSACTION GOTO EndSave ... it seems that there is no reason the application has to make use of the MSDB database during database upgrade. ...
    (microsoft.public.sqlserver.server)
  • Re: What is stored in MSDB database for SQL Server 2005 Express ?
    ... I guess it is a general script that applies to SQL Server 2005 Express, SQL Server 2005 and SQL Server 2008. ... GOTO QuitWithRollback END COMMIT TRANSACTION GOTO EndSave ... it seems that there is no reason the application has to make use of the MSDB database during database upgrade. ...
    (microsoft.public.sqlserver.server)
  • Re: What is stored in MSDB database for SQL Server 2005 Express ?
    ... We also perform an in-place upgrade to SQL Server 2005 Express a couple of weeks before. ... QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule ... it seems that there is no reason the application has to make use of the MSDB database during database upgrade. ...
    (microsoft.public.sqlserver.server)
  • Re: MSDB (Subspect)
    ... Microsoft SQL Server Storage Engine ... > Probably because the database was damaged when you took the backup. ... > files from the msdb database to the production SQL Server. ...
    (microsoft.public.sqlserver.server)
  • Re: Security for Jobs
    ... Thanks Tom. ... >Add them as users to the msdb database and then add them ... >Columnist, SQL Server Professional ... > restart jobs when failure has occurred. ...
    (microsoft.public.sqlserver.security)
Loading