Monday, February 20, 2012

MSDB (Subspect)

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...
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/inf...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...
>
|||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/inf...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...[vbcol=seagreen]
> Does anyone know how I can recover the MSDB once it is not subspect?
database file to the orginial location but only the MSDB was subspected...[vbcol=seagreen]
|||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/inf...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...[vbcol=seagreen]
> database file to the orginial location but only the MSDB was subspected...
|||Your best bet is to call PSS (http://support.microsoft.com) who will be able
to help you with this.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMEBrpsJEHA.428@.TK2MSFTNGP11.phx.gbl...
> 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.[vbcol=seagreen]
> --
> 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...
know why still subspected....[vbcol=seagreen]
like any other database when it[vbcol=seagreen]
> comes to
suspect database:[vbcol=seagreen]
> news:A51A7CEF-958B-4911-B647-810C27A2DCE4@.microsoft.com...
subspect?[vbcol=seagreen]
in night and I have already copy the[vbcol=seagreen]
subspected...
>
|||Hi Tiber / Philip,
I have one more suggestion to this issue, I feel that during the MSDB mdf
and LDF physical file copy (cold Backup), they would have started the SQL
server service. In this case during MSDB startup the files may be used by
the backup process and startup would have been failed and marked the
database "Suspect".
If this case he can run the procedure sp_resetstaus <dbname> and restart sql
server service.
steps:
1.
Use Master
GO
EXEC sp_resetstatus <dbname>
2. Stop & Start the SQL Server
Thanks
Hari
MCDBA
2."Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in message news:OMEBrpsJEHA.428@.TK2MSFTNGP11.phx.gbl...
> 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.[vbcol=seagreen]
> --
> 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...
know why still subspected....[vbcol=seagreen]
like any other database when it[vbcol=seagreen]
> comes to
suspect database:[vbcol=seagreen]
> news:A51A7CEF-958B-4911-B647-810C27A2DCE4@.microsoft.com...
subspect?[vbcol=seagreen]
in night and I have already copy the[vbcol=seagreen]
subspected...
>

No comments:

Post a Comment