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 backup
device 'd:\sql_data\MSSQL\BACKUP\msdb_db_200407042207.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 is
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 specific T-SQL that was failing:
BACKUP DATABASE [msdb] TO DISK = N'd:\sql_data\MSSQL\BACKUP\msdb_db_200407042238.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'|||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_200407042238.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..

msdb recreate cause replication problems

We just went through a nasty experience whereby we had to recreate the
MSDB database.
System is now back, but replication is completely messed up. Is there a
way to get all the MS* tables back, in the MSDB? I tried to de-install
and install Replication, but that doesn't seem to generate all the
required tables and procs
SQL2005, SP1.
Any help much appreciated!
I take it you mean you want your jobs to pick up where they left off.
Dropping and recreating the publications and subscriptions should do that.
If you mean the replication table in msdb, they are created there when you
install sql server.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<renedevries@.gmail.com> wrote in message
news:1159994716.013545.158270@.i3g2000cwc.googlegro ups.com...
> We just went through a nasty experience whereby we had to recreate the
> MSDB database.
> System is now back, but replication is completely messed up. Is there a
> way to get all the MS* tables back, in the MSDB? I tried to de-install
> and install Replication, but that doesn't seem to generate all the
> required tables and procs
> SQL2005, SP1.
> Any help much appreciated!
>
|||Eventually called MS Tech Support. The goal was to het replication
working at all, never mind the jobs or the previous state. But since
all relevant tables in msdb where gone, the whole thing was in limbo
The only REAL option is indeed a fresh install of SQL. However, they
where able to help me out, though a number of steps which involved
removing repliation from the database, dropping the distribution db,
and some other assorted trickery. But eventually we got it to work.
Turns out that if you re-create the distribution database all the
associated msdb tables and jobs are also recreated. Fortunately ;)
Lesson: make sure you keep good, validated backups of msdb at all
times. Bitten once again
Rene
Hilary Cotter schreef:

> I take it you mean you want your jobs to pick up where they left off.
> Dropping and recreating the publications and subscriptions should do that.
> If you mean the replication table in msdb, they are created there when you
> install sql server.
> --

msdb recovery on sql 2005

Hi

I have a backup of msdb on sql 2005 version 9.00.2047

I need to use this backup to restore msdb on the same machine.

But the version has been upgraded to 9.00.2153

All of my attempts end up with the same messages

"...cannot be restored because it was created by a different version of the server..."

Thanks.

You are going to have to find an install of version 9.00.2047, restore the msdb database, upgrade the instance to 9.00.2153, backup the msdb database again, and then use that backup to restore to the instance that you want.

There is a reason that every hotfix and service pack has an explicit warning message that you must backup system databases immediately after applying the service pack or hotfix. You can not restore a system database across hotfix or service pack versions.

|||Thanks it worked as you said.

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 question

WHat would be the danger of adding a couple of indexes to the msdb system
tables (particularly the backup/restore jobs tables)? I know that a hotfix
or sp update may drop the tables, but if we are staying on sp3 or sp4 for
now, would it cause a sytem wide issue?
This is for SQL2000 on a Win2003 server.
Thankscw
Why? MS strongly recommended not direct updating system tables.
"cw" <cw@.3mc.com> wrote in message
news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
> WHat would be the danger of adding a couple of indexes to the msdb system
> tables (particularly the backup/restore jobs tables)? I know that a
> hotfix or sp update may drop the tables, but if we are staying on sp3 or
> sp4 for now, would it cause a sytem wide issue?
> This is for SQL2000 on a Win2003 server.
> Thanks
>|||I do it all the time. One side effect of constant build testing is that you
don't run something for a long time and see some of the life cycle issues
that we see in real life. Just don't add keys or constraints, only indexes
and you should be fine.
Here is the set I use:
/ ****************************************
********************************
* *
* Title: msdb index creation *
* Author: Geoff N. Hiten *
* Purpose: Index msdb database *
* Date: 12/12/2005 *
* Modifications: *
* *
* 06-22-2001 *
* Sample Entry *
* *
* *
****************************************
********************************/
use
msdb
go
--backupset
Create
index IX_backupset_backup_set_id on backupset (backup_set_id)
go
Create
index IX_backupset_backup_set_uuid on backupset (backup_set_uuid)
go
Create
index IX_backupset_media_set_id on backupset (media_set_id)
go
Create
index IX_backupset_backup_finish_date on backupset (backup_finish_date)
go
Create
index IX_backupset_backup_start_date on backupset (backup_start_date)
go
--backupmediaset
Create
index IX_backupmediaset_media_set_id on backupmediaset (media_set_id)
go
--backupfile
Create
index IX_backupfile_backup_set_id on backupfile (backup_set_id)
go
--backupmediafamily
Create
index IX_backupmediafamily_media_set_id on backupmediafamily (media_set_id)
go
--restorehistory
Create
index IX_restorehistory_restore_history_id on restorehistory
(restore_history_id)
go
Create
index IX_restorehistory_backup_set_id on restorehistory (backup_set_id)
go
--restorefile
Create
index IX_restorefile_restore_history_id on restorefile (restore_history_id)
go
--restorefilegroup
Create
index IX_restorefilegroup_restore_history_id on restorefilegroup
(restore_history_id)
go
"cw" <cw@.3mc.com> wrote in message
news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
> WHat would be the danger of adding a couple of indexes to the msdb system
> tables (particularly the backup/restore jobs tables)? I know that a
> hotfix or sp update may drop the tables, but if we are staying on sp3 or
> sp4 for now, would it cause a sytem wide issue?
> This is for SQL2000 on a Win2003 server.
> Thanks
>|||Thanks - several of these indexes you have listed below are the same ones I
added. I had dropped an old database from a SQL2000 server (using EM). The
DB had daily backups and several transaction log backups during the day.
This has been going on for the last 2-3 years and the number of records in
the msdb backup system tables for historical purposes was huge. When EM
drops a DB and begins to delete the historical information about the
backups, it called the sp_delete_backup_history procedure. If there was
ever a procedure that needed indexes, this is it. The procedure had been
running all night and a trace showed it doing several table scans using
"lots" of cursors.
After I added the indexes the job finished w/i 10 minutes. We dropped
another database that would have had the same backup history and the
procedure finished w/i 15 minutes.
Thanks for the confirmation -- cw
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%235GjJwqAHHA.996@.TK2MSFTNGP02.phx.gbl...
>I do it all the time. One side effect of constant build testing is that
>you don't run something for a long time and see some of the life cycle
>issues that we see in real life. Just don't add keys or constraints, only
>indexes and you should be fine.
> Here is the set I use:
> / ****************************************
********************************
> * *
> * Title: msdb index creation *
> * Author: Geoff N. Hiten *
> * Purpose: Index msdb database *
> * Date: 12/12/2005 *
> * Modifications: *
> * *
> * 06-22-2001 *
> * Sample Entry *
> * *
> * *
> ****************************************
********************************/
> use
> msdb
> go
> --backupset
> Create
> index IX_backupset_backup_set_id on backupset (backup_set_id)
> go
> Create
> index IX_backupset_backup_set_uuid on backupset (backup_set_uuid)
> go
> Create
> index IX_backupset_media_set_id on backupset (media_set_id)
> go
> Create
> index IX_backupset_backup_finish_date on backupset (backup_finish_date)
> go
> Create
> index IX_backupset_backup_start_date on backupset (backup_start_date)
> go
> --backupmediaset
> Create
> index IX_backupmediaset_media_set_id on backupmediaset (media_set_id)
> go
> --backupfile
> Create
> index IX_backupfile_backup_set_id on backupfile (backup_set_id)
> go
> --backupmediafamily
> Create
> index IX_backupmediafamily_media_set_id on backupmediafamily
> (media_set_id)
> go
> --restorehistory
> Create
> index IX_restorehistory_restore_history_id on restorehistory
> (restore_history_id)
> go
> Create
> index IX_restorehistory_backup_set_id on restorehistory (backup_set_id)
> go
> --restorefile
> Create
> index IX_restorefile_restore_history_id on restorefile
> (restore_history_id)
> go
> --restorefilegroup
> Create
> index IX_restorefilegroup_restore_history_id on restorefilegroup
> (restore_history_id)
> go
>
> "cw" <cw@.3mc.com> wrote in message
> news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
>|||I also use a "nibbler" to drive the s_delete_backup_history cleanuot proc.
It deletes one day at a time until it gets to the minimum retention time I
set. And no, I don't think I will post that.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"cw" <cw@.3mc.com> wrote in message
news:OHXUGKrAHHA.3540@.TK2MSFTNGP03.phx.gbl...
> Thanks - several of these indexes you have listed below are the same ones
> I added. I had dropped an old database from a SQL2000 server (using EM).
> The DB had daily backups and several transaction log backups during the
> day. This has been going on for the last 2-3 years and the number of
> records in the msdb backup system tables for historical purposes was huge.
> When EM drops a DB and begins to delete the historical information about
> the backups, it called the sp_delete_backup_history procedure. If there
> was ever a procedure that needed indexes, this is it. The procedure had
> been running all night and a trace showed it doing several table scans
> using "lots" of cursors.
> After I added the indexes the job finished w/i 10 minutes. We dropped
> another database that would have had the same backup history and the
> procedure finished w/i 15 minutes.
> Thanks for the confirmation -- cw
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%235GjJwqAHHA.996@.TK2MSFTNGP02.phx.gbl...
>|||Several months ago I noticed that these backup tables in msdb were
growing out of control. Attempts to delete rows using the delete backup
history sp took *forever*. Since it is my understanding that these
tables are really only needed if you use the interfaces, such as EM or
Mgmt Studio, to do restores, I decided to just truncate the tables then
start running the sp every night to only keep 3 days history. This
method was much faster needless to say and nothing was the worse for
it.
Geoff N. Hiten wrote:[vbcol=seagreen]
> I also use a "nibbler" to drive the s_delete_backup_history cleanuot proc.
> It deletes one day at a time until it gets to the minimum retention time I
> set. And no, I don't think I will post that.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "cw" <cw@.3mc.com> wrote in message
> news:OHXUGKrAHHA.3540@.TK2MSFTNGP03.phx.gbl...