Error 823 with msdb

When I try to create a new Maintenance Plan or revise an existing one I
get the following error:
Error: 823, Severity: 24, State: 2
Microsoft SQL-DMO (ODBC SQL State:HY000)
Error 823: I/O error (torn page) detected during read at offsett
0x000000000f0000 in file 'd:\sqldata\MSSQL\data\msdbdata.mdf'.
There are no known disk problems that I can detect. The existing
Maintenance Plans run fine. I do not have a backup of msdb prior to when
this problem started (I have no idea of when it started, it only appears
when I try to modify the maintenance plan).
I can easily recreate the maintenance plans but I need to fix the problem
with msdb first. Can msdb be repaired, can I empty it to start fresh, can I
use the msdb from an original install'
Any suggestions are greatly appreciated.
Thanks
Russ StevensI just tried to repair msdb
DBCC CHECKDB ('msdb', REPAIR_ALLOW_DATA_LOSS)
It fixed 6 errors - I now have a bigger mess <g> - am getting the following
error (and other strange errors) when I try to modify a Maintenance Plan;
Error 170: Line 1: Incorrect syntax near ','.
I need a clean working msdb - appreciate any help.
Thanks
Russ Stevens|||Anytime you run a dbcc checkdb with repair_allow_data_loss you risk data
integrity. there is no way to know what kind of data is lost etc. In
your case, you have to recreate the maintenance plan, which should not
be a big issue. In fact if that is the only problem that you get after
the repair, you should be happy.
Alternatively you can always restore from backup :)
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Russell Stevens wrote:
> I just tried to repair msdb
> DBCC CHECKDB ('msdb', REPAIR_ALLOW_DATA_LOSS)
> It fixed 6 errors - I now have a bigger mess <g> - am getting the following
> error (and other strange errors) when I try to modify a Maintenance Plan;
> Error 170: Line 1: Incorrect syntax near ','.
> I need a clean working msdb - appreciate any help.
> Thanks
> Russ Stevens
>|||Yih-Yoon,
<<In your case, you have to recreate the maintenance plan, which should not
be a big issue. In fact if that is the only problem that you get after
the repair, you should be happy.>>
No - I am not happy <g> - I couldn't create a maintenance plan before the
repair or after the repair. I can't add a plan or revise a plan. Until msdb
is fixed, I am dead in the water (the existing plans still run fine). And I
can't restore from an msdb backup as I evidently don't have one that was
made before the problem started (I only keep a few weeks).
Looks like I need to rebuild the master and start over. It just seems that
MS should have a way of rebuilding just msdb without having to rebuild the
master.
Thanks
Russ Stevens|||There is a way to rebuild msdb from scratch. May not be a supported one.
Try this on a test server before doing this on a production server
Start SQL Server from the command prompt recover just the master
database, such as sqlservr.exe -c -f -T3608 -T4022
you can then connect to sql server from query analzyer (-f means yo
uwill only get single connection), then you can delete MSDB database.
There is a script in mssql\install folder called instmsdb.sql. This is
the script to recreate MSDB database.
I have to warn you this is probably not a supported solution by
Microsoft. I am not responsible for any damage :) But if you are
thinking of rebuilding master database anyway, why not give this a try?
the idea is test it thoroughly and make sure it test in your development
server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Russell Stevens wrote:
> Yih-Yoon,
> <<In your case, you have to recreate the maintenance plan, which should not
> be a big issue. In fact if that is the only problem that you get after
> the repair, you should be happy.>>
> No - I am not happy <g> - I couldn't create a maintenance plan before the
> repair or after the repair. I can't add a plan or revise a plan. Until msdb
> is fixed, I am dead in the water (the existing plans still run fine). And I
> can't restore from an msdb backup as I evidently don't have one that was
> made before the problem started (I only keep a few weeks).
> Looks like I need to rebuild the master and start over. It just seems that
> MS should have a way of rebuilding just msdb without having to rebuild the
> master.
> Thanks
> Russ Stevens
>|||Yih-Yoon,
I suspect the instmsdb.sql script is also used by the rebuildm.exe
utility. I didn't want to chance it. I rebuilt everything, am back on line
(after an all nighter).
Needless to say I was quite amazed that after rebuilding, my sa password
had been set to a blank - no message, no warning, I was just open to the
world. How can Microsoft miss something like that?
Thanks for your help.
Russ Stevens|||A rebuild of Master will recreate the structure and base data, but not
repopulate existing data. I would have tried the other solution first.
Drop and rerun the instmsdb.sql script. Then the risk was limited to just
msdb, which contains far less useful information than master.
Also, a good installation recommendation is to always set a responsible
backup strategy for your system databases. School of hard knocks works, but
it is an expensive lesson.
Sincerely,
Anthony Thomas
"Russell Stevens" <rastevens@.aol.com> wrote in message
news:%23tlm6PWAFHA.3504@.TK2MSFTNGP12.phx.gbl...
Yih-Yoon,
I suspect the instmsdb.sql script is also used by the rebuildm.exe
utility. I didn't want to chance it. I rebuilt everything, am back on line
(after an all nighter).
Needless to say I was quite amazed that after rebuilding, my sa password
had been set to a blank - no message, no warning, I was just open to the
world. How can Microsoft miss something like that?
Thanks for your help.
Russ Stevenssql