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 backu
p
device 'd:\sql_data\MSSQL\BACKUP\msdb_db_200407
042207.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 i
s
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 spec
ific T-SQL that was failing:
BACKUP DATABASE [msdb] TO DISK = N'd:\sql_data\MSSQL\BACKUP\msdb_db_200
407042238.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
After some pondering and re-reading my own post, I went ahead and logged int
o the server interactively using SQL Server's account. I attempted to execu
te the above SQL and lo and behond, it failed (General Network Error). When
executed under my normal U
serID, 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 sam
e statement is run under the SQL Server account EXCEPT for removing the stat
s=10 command, it works fine|||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_20040
7042238.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..
Showing posts with label plan. Show all posts
Showing posts with label plan. Show all posts
Saturday, February 25, 2012
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.
Geof
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.BA K' 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 U
serID, 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
|||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.BA K' 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..
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.
Geof
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.BA K' 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 U
serID, 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
|||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.BA K' 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 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..
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..
Monday, February 20, 2012
MSDB database
I have under SQL Server 2000 Standard Version with SP3a on Windows 2000 SP4
a Database Maintenance plan to backup transaction logs of all databases
hourly.
This job has been frequently failing, and the reason is that on the MSDB
database, the Recovery option has for some mysterious reason frequently been
reset from FULL to SIMPLE, and in order to get transaction logs backing up,
it needs to be set to FULL.
anyone has any ideas why/what process, etc. would reset the recovery option
from FULL to SIMPLE?> I have under SQL Server 2000 Standard Version with SP3a on Windows 2000
SP4
> a Database Maintenance plan to backup transaction logs of all databases
> hourly.
Backing up the transaction logs from all databases seems like overkill.
What is the point, e.g. for tempdb?
> anyone has any ideas why/what process, etc. would reset the recovery
option
> from FULL to SIMPLE?
This is the way SQL Server works.
http://www.support.microsoft.com/?id=257856
If you really want to back up msdb every hour, then you can make a task in
your job to temporarily set the recovery model to FULL. However this won't
accomplish much as the transaction logs won't be complete (and maybe not
even usable, depending on how you plan to use them).
If you want the behavior to be different, you can let Microsoft know how you
feel: sqlwish@.microsoft.com
Aaron Bertrand
SQL Server MVP|||Aaron Bertrand - MVP wrote:
> option
> This is the way SQL Server works.
> http://www.support.microsoft.com/?id=257856
>
that link is interesting. sort of contradicts the line from BOL "All recove
ry
models are supported for model, msdb and distribution." since full recovery
on
msdb only works until sqlagent is restarted.
i always prefer to have a dbmp for user databases and one for system databas
es.
in the system one, i don't back up transaction logs.
i think it's sort of goofy that the transaction log backup from a dbmp will
try
to backup the logs for db's in simple recovery and then generate errors. i'
ve
written a script that skips simple recovery db's for the log backups, why ca
n't
microsoft implement it in the dbmp jobs?
a Database Maintenance plan to backup transaction logs of all databases
hourly.
This job has been frequently failing, and the reason is that on the MSDB
database, the Recovery option has for some mysterious reason frequently been
reset from FULL to SIMPLE, and in order to get transaction logs backing up,
it needs to be set to FULL.
anyone has any ideas why/what process, etc. would reset the recovery option
from FULL to SIMPLE?> I have under SQL Server 2000 Standard Version with SP3a on Windows 2000
SP4
> a Database Maintenance plan to backup transaction logs of all databases
> hourly.
Backing up the transaction logs from all databases seems like overkill.
What is the point, e.g. for tempdb?
> anyone has any ideas why/what process, etc. would reset the recovery
option
> from FULL to SIMPLE?
This is the way SQL Server works.
http://www.support.microsoft.com/?id=257856
If you really want to back up msdb every hour, then you can make a task in
your job to temporarily set the recovery model to FULL. However this won't
accomplish much as the transaction logs won't be complete (and maybe not
even usable, depending on how you plan to use them).
If you want the behavior to be different, you can let Microsoft know how you
feel: sqlwish@.microsoft.com
Aaron Bertrand
SQL Server MVP|||Aaron Bertrand - MVP wrote:
> option
> This is the way SQL Server works.
> http://www.support.microsoft.com/?id=257856
>
that link is interesting. sort of contradicts the line from BOL "All recove
ry
models are supported for model, msdb and distribution." since full recovery
on
msdb only works until sqlagent is restarted.
i always prefer to have a dbmp for user databases and one for system databas
es.
in the system one, i don't back up transaction logs.
i think it's sort of goofy that the transaction log backup from a dbmp will
try
to backup the logs for db's in simple recovery and then generate errors. i'
ve
written a script that skips simple recovery db's for the log backups, why ca
n't
microsoft implement it in the dbmp jobs?
MSDB database
I have under SQL Server 2000 Standard Version with SP3a on Windows 2000 SP4
a Database Maintenance plan to backup transaction logs of all databases
hourly.
This job has been frequently failing, and the reason is that on the MSDB
database, the Recovery option has for some mysterious reason frequently been
reset from FULL to SIMPLE, and in order to get transaction logs backing up,
it needs to be set to FULL.
anyone has any ideas why/what process, etc. would reset the recovery option
from FULL to SIMPLE?> I have under SQL Server 2000 Standard Version with SP3a on Windows 2000
SP4
> a Database Maintenance plan to backup transaction logs of all databases
> hourly.
Backing up the transaction logs from all databases seems like overkill.
What is the point, e.g. for tempdb?
> anyone has any ideas why/what process, etc. would reset the recovery
option
> from FULL to SIMPLE?
This is the way SQL Server works.
http://www.support.microsoft.com/?id=257856
If you really want to back up msdb every hour, then you can make a task in
your job to temporarily set the recovery model to FULL. However this won't
accomplish much as the transaction logs won't be complete (and maybe not
even usable, depending on how you plan to use them).
If you want the behavior to be different, you can let Microsoft know how you
feel: sqlwish@.microsoft.com
--
Aaron Bertrand
SQL Server MVP|||Aaron Bertrand - MVP wrote:
> > anyone has any ideas why/what process, etc. would reset the recovery
> option
> > from FULL to SIMPLE?
> This is the way SQL Server works.
> http://www.support.microsoft.com/?id=257856
>
that link is interesting. sort of contradicts the line from BOL "All recovery
models are supported for model, msdb and distribution." since full recovery on
msdb only works until sqlagent is restarted.
i always prefer to have a dbmp for user databases and one for system databases.
in the system one, i don't back up transaction logs.
i think it's sort of goofy that the transaction log backup from a dbmp will try
to backup the logs for db's in simple recovery and then generate errors. i've
written a script that skips simple recovery db's for the log backups, why can't
microsoft implement it in the dbmp jobs?
a Database Maintenance plan to backup transaction logs of all databases
hourly.
This job has been frequently failing, and the reason is that on the MSDB
database, the Recovery option has for some mysterious reason frequently been
reset from FULL to SIMPLE, and in order to get transaction logs backing up,
it needs to be set to FULL.
anyone has any ideas why/what process, etc. would reset the recovery option
from FULL to SIMPLE?> I have under SQL Server 2000 Standard Version with SP3a on Windows 2000
SP4
> a Database Maintenance plan to backup transaction logs of all databases
> hourly.
Backing up the transaction logs from all databases seems like overkill.
What is the point, e.g. for tempdb?
> anyone has any ideas why/what process, etc. would reset the recovery
option
> from FULL to SIMPLE?
This is the way SQL Server works.
http://www.support.microsoft.com/?id=257856
If you really want to back up msdb every hour, then you can make a task in
your job to temporarily set the recovery model to FULL. However this won't
accomplish much as the transaction logs won't be complete (and maybe not
even usable, depending on how you plan to use them).
If you want the behavior to be different, you can let Microsoft know how you
feel: sqlwish@.microsoft.com
--
Aaron Bertrand
SQL Server MVP|||Aaron Bertrand - MVP wrote:
> > anyone has any ideas why/what process, etc. would reset the recovery
> option
> > from FULL to SIMPLE?
> This is the way SQL Server works.
> http://www.support.microsoft.com/?id=257856
>
that link is interesting. sort of contradicts the line from BOL "All recovery
models are supported for model, msdb and distribution." since full recovery on
msdb only works until sqlagent is restarted.
i always prefer to have a dbmp for user databases and one for system databases.
in the system one, i don't back up transaction logs.
i think it's sort of goofy that the transaction log backup from a dbmp will try
to backup the logs for db's in simple recovery and then generate errors. i've
written a script that skips simple recovery db's for the log backups, why can't
microsoft implement it in the dbmp jobs?
MSDb and Master maintenance
Using the Database Maintenenace Plan Wizard I set up
maintenance plans for each of the databases on my MS SQL
Server. This is working fine except for the "msdb"
and "master" databases. For these I get the following
errors. What am I doing wrong?
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'EPDDOCS3' as 'NT AUTHORITY\SYSTEM'
(trusted)
Starting maintenance plan 'DB Maintenance Plan msdb' on
9/20/2004 8:00:01 AM
[1] Database msdb: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub
task is ignored.
End of maintenance plan 'DB Maintenance Plan msdb' on
9/20/2004 8:00:02 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'EPDDOCS3' as 'NT AUTHORITY\SYSTEM'
(trusted)
Starting maintenance plan 'DB Maintenance Plan msdb' on
9/20/2004 4:00:01 AM
[1] Database msdb: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub
task is ignored.
End of maintenance plan 'DB Maintenance Plan msdb' on
9/20/2004 4:00:02 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
You probably try to do log backups. You can't do log backup on master. You can on model, but that is
overkill and would require setting it to full recovery mode. I suggest you create two plans, One in
which you do log backup, another in which you don't.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Schuster" <schuster@.llnl.gov> wrote in message news:4aa201c49f30$4fc629d0$a601280a@.phx.gbl...
> Using the Database Maintenenace Plan Wizard I set up
> maintenance plans for each of the databases on my MS SQL
> Server. This is working fine except for the "msdb"
> and "master" databases. For these I get the following
> errors. What am I doing wrong?
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged
> on to SQL Server 'EPDDOCS3' as 'NT AUTHORITY\SYSTEM'
> (trusted)
> Starting maintenance plan 'DB Maintenance Plan msdb' on
> 9/20/2004 8:00:01 AM
> [1] Database msdb: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Backup can not be performed on database 'msdb'. This sub
> task is ignored.
> End of maintenance plan 'DB Maintenance Plan msdb' on
> 9/20/2004 8:00:02 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> ----
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged
> on to SQL Server 'EPDDOCS3' as 'NT AUTHORITY\SYSTEM'
> (trusted)
> Starting maintenance plan 'DB Maintenance Plan msdb' on
> 9/20/2004 4:00:01 AM
> [1] Database msdb: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Backup can not be performed on database 'msdb'. This sub
> task is ignored.
> End of maintenance plan 'DB Maintenance Plan msdb' on
> 9/20/2004 4:00:02 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
maintenance plans for each of the databases on my MS SQL
Server. This is working fine except for the "msdb"
and "master" databases. For these I get the following
errors. What am I doing wrong?
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'EPDDOCS3' as 'NT AUTHORITY\SYSTEM'
(trusted)
Starting maintenance plan 'DB Maintenance Plan msdb' on
9/20/2004 8:00:01 AM
[1] Database msdb: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub
task is ignored.
End of maintenance plan 'DB Maintenance Plan msdb' on
9/20/2004 8:00:02 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'EPDDOCS3' as 'NT AUTHORITY\SYSTEM'
(trusted)
Starting maintenance plan 'DB Maintenance Plan msdb' on
9/20/2004 4:00:01 AM
[1] Database msdb: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub
task is ignored.
End of maintenance plan 'DB Maintenance Plan msdb' on
9/20/2004 4:00:02 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
You probably try to do log backups. You can't do log backup on master. You can on model, but that is
overkill and would require setting it to full recovery mode. I suggest you create two plans, One in
which you do log backup, another in which you don't.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Schuster" <schuster@.llnl.gov> wrote in message news:4aa201c49f30$4fc629d0$a601280a@.phx.gbl...
> Using the Database Maintenenace Plan Wizard I set up
> maintenance plans for each of the databases on my MS SQL
> Server. This is working fine except for the "msdb"
> and "master" databases. For these I get the following
> errors. What am I doing wrong?
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged
> on to SQL Server 'EPDDOCS3' as 'NT AUTHORITY\SYSTEM'
> (trusted)
> Starting maintenance plan 'DB Maintenance Plan msdb' on
> 9/20/2004 8:00:01 AM
> [1] Database msdb: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Backup can not be performed on database 'msdb'. This sub
> task is ignored.
> End of maintenance plan 'DB Maintenance Plan msdb' on
> 9/20/2004 8:00:02 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> ----
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged
> on to SQL Server 'EPDDOCS3' as 'NT AUTHORITY\SYSTEM'
> (trusted)
> Starting maintenance plan 'DB Maintenance Plan msdb' on
> 9/20/2004 4:00:01 AM
> [1] Database msdb: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Backup can not be performed on database 'msdb'. This sub
> task is ignored.
> End of maintenance plan 'DB Maintenance Plan msdb' on
> 9/20/2004 4:00:02 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
MSDb and Master maintenance
Using the Database Maintenenace Plan Wizard I set up
maintenance plans for each of the databases on my MS SQL
Server. This is working fine except for the "msdb"
and "master" databases. For these I get the following
errors. What am I doing wrong?
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'EPDDOCS3' as 'NT AUTHORITY\SYSTEM'
(trusted)
Starting maintenance plan 'DB Maintenance Plan msdb' on
9/20/2004 8:00:01 AM
[1] Database msdb: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub
task is ignored.
End of maintenance plan 'DB Maintenance Plan msdb' on
9/20/2004 8:00:02 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
----
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'EPDDOCS3' as 'NT AUTHORITY\SYSTEM'
(trusted)
Starting maintenance plan 'DB Maintenance Plan msdb' on
9/20/2004 4:00:01 AM
[1] Database msdb: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub
task is ignored.
End of maintenance plan 'DB Maintenance Plan msdb' on
9/20/2004 4:00:02 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)You probably try to do log backups. You can't do log backup on master. You can on model, but that is
overkill and would require setting it to full recovery mode. I suggest you create two plans, One in
which you do log backup, another in which you don't.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Schuster" <schuster@.llnl.gov> wrote in message news:4aa201c49f30$4fc629d0$a601280a@.phx.gbl...
> Using the Database Maintenenace Plan Wizard I set up
> maintenance plans for each of the databases on my MS SQL
> Server. This is working fine except for the "msdb"
> and "master" databases. For these I get the following
> errors. What am I doing wrong?
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged
> on to SQL Server 'EPDDOCS3' as 'NT AUTHORITY\SYSTEM'
> (trusted)
> Starting maintenance plan 'DB Maintenance Plan msdb' on
> 9/20/2004 8:00:01 AM
> [1] Database msdb: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Backup can not be performed on database 'msdb'. This sub
> task is ignored.
> End of maintenance plan 'DB Maintenance Plan msdb' on
> 9/20/2004 8:00:02 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> ----
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged
> on to SQL Server 'EPDDOCS3' as 'NT AUTHORITY\SYSTEM'
> (trusted)
> Starting maintenance plan 'DB Maintenance Plan msdb' on
> 9/20/2004 4:00:01 AM
> [1] Database msdb: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Backup can not be performed on database 'msdb'. This sub
> task is ignored.
> End of maintenance plan 'DB Maintenance Plan msdb' on
> 9/20/2004 4:00:02 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
maintenance plans for each of the databases on my MS SQL
Server. This is working fine except for the "msdb"
and "master" databases. For these I get the following
errors. What am I doing wrong?
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'EPDDOCS3' as 'NT AUTHORITY\SYSTEM'
(trusted)
Starting maintenance plan 'DB Maintenance Plan msdb' on
9/20/2004 8:00:01 AM
[1] Database msdb: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub
task is ignored.
End of maintenance plan 'DB Maintenance Plan msdb' on
9/20/2004 8:00:02 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
----
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'EPDDOCS3' as 'NT AUTHORITY\SYSTEM'
(trusted)
Starting maintenance plan 'DB Maintenance Plan msdb' on
9/20/2004 4:00:01 AM
[1] Database msdb: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub
task is ignored.
End of maintenance plan 'DB Maintenance Plan msdb' on
9/20/2004 4:00:02 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)You probably try to do log backups. You can't do log backup on master. You can on model, but that is
overkill and would require setting it to full recovery mode. I suggest you create two plans, One in
which you do log backup, another in which you don't.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Schuster" <schuster@.llnl.gov> wrote in message news:4aa201c49f30$4fc629d0$a601280a@.phx.gbl...
> Using the Database Maintenenace Plan Wizard I set up
> maintenance plans for each of the databases on my MS SQL
> Server. This is working fine except for the "msdb"
> and "master" databases. For these I get the following
> errors. What am I doing wrong?
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged
> on to SQL Server 'EPDDOCS3' as 'NT AUTHORITY\SYSTEM'
> (trusted)
> Starting maintenance plan 'DB Maintenance Plan msdb' on
> 9/20/2004 8:00:01 AM
> [1] Database msdb: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Backup can not be performed on database 'msdb'. This sub
> task is ignored.
> End of maintenance plan 'DB Maintenance Plan msdb' on
> 9/20/2004 8:00:02 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> ----
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged
> on to SQL Server 'EPDDOCS3' as 'NT AUTHORITY\SYSTEM'
> (trusted)
> Starting maintenance plan 'DB Maintenance Plan msdb' on
> 9/20/2004 4:00:01 AM
> [1] Database msdb: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Backup can not be performed on database 'msdb'. This sub
> task is ignored.
> End of maintenance plan 'DB Maintenance Plan msdb' on
> 9/20/2004 4:00:02 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
Subscribe to:
Posts (Atom)