Maint plan fails to backup DB or TRN, odd error

When I run my maint plan on our new server I get a...
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.
This is a bit mysterious for three reasons:
1) There should be nothing to repair: it's a brand new DB
2) I've told it not to bother doing integrity checks before backups in the
plan (it was on, I turned it off today)
3) Using All Tasks -> Backup Database works fine (fast too!)
Any ideas?For this error, the CHECK BEFORE BACKUP is immaterial. The question is whet
her or not you have the Integrity Check jobs created. This part of the SQLM
AINT utility will execute the DBCC CHECKDB statements. For this to execute,
regardless if you've selected the ATTEMPT TO FIX option, the database will
need to be put in SINGLE_USER mode. SQLMAINT will not be able to do this if
there are persistent connections to the database that can not be dropped.
For a few of our systems, we have had to run a step in the job prior to SQLM
AINT step to execute an ALTER DATABASE SET OFFLINE/ONLINE WITH ROLLBACK IMME
DIATE statement.
Sincerely,
Anthony Thomas
--
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in mess
age news:BCDA29C3-CEE3-4FA8-83CB-96151AF34A15@.microsoft.com...
When I run my maint plan on our new server I get a...
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
processed. Database needs to be in single user mode.
This is a bit mysterious for three reasons:
1) There should be nothing to repair: it's a brand new DB
2) I've told it not to bother doing integrity checks before backups in the
plan (it was on, I turned it off today)
3) Using All Tasks -> Backup Database works fine (fast too!)
Any ideas?|||"AnthonyThomas" wrote:

> For this error, the CHECK BEFORE BACKUP is immaterial.
Ok, that answers that one.

> The question is whether or not you have the Integrity Check jobs created.
I do. I assume that this is not the same thing as CHECK BEFORE BACKUP then?
That's what I assumed it was -- and I don't think it was too dumb an
assumption.

> This part of the SQLMAINT utility will execute the DBCC CHECKDB
> statements. For this to execute, regardless if you've selected the
> ATTEMPT TO FIX option, the database will need to be put in
> SINGLE_USER mode.
This seems odd though. In this case the only connection is me on SQL
Manager. It would seem very stupid if the very tool used to start these jobs
makes them fail. It also doesn't explain why it failed last night, when no
one should have been on (although perhaps inactive Access connections).
What do I do? Simply delete this job?

> For a few of our systems, we have had to run a step in the job
> prior to SQLMAINT step to execute an ALTER DATABASE SET
> OFFLINE/ONLINE WITH ROLLBACK IMMEDIATE statement.
Ok, let me try that.
Seems silly I have to do this at all -- you'd think it would come out of the
box set to "work right".|||"AnthonyThomas" wrote:
Reading between the lines of your message I was able to get the transaction
log to backup by turning off the step in my maint plan. So now it works OK a
t
least, but I stand by my opinion that this should either not be a problem in
the first place, or should be MUCH more obvious as a potential issue.
But now that task doesn't run. It seems to me that it would be a good idea
to have it run every so often! Is there some "natural" time when I the
database will be in the right mode? Perhaps after a backup?

> For this error, the CHECK BEFORE BACKUP is immaterial. The question is whether or
not you have the Integrity Check jobs created. This part of the SQLMAINT utility w
ill execute the DBCC CHECKDB statements. For this to execute, regardless if you've
sel
ected the ATTEMPT TO FIX option, the database will need to be put in SINGLE_USER mode. SQLM
AINT will not be able to do this if there are persistent connections to the database that ca
n not be dropped.
> For a few of our systems, we have had to run a step in the job prior to SQ
LMAINT step to execute an ALTER DATABASE SET OFFLINE/ONLINE WITH ROLLBACK IM
MEDIATE statement.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in me
ssage news:BCDA29C3-CEE3-4FA8-83CB-96151AF34A15@.microsoft.com...
> When I run my maint plan on our new server I get a...
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statem
ent not
> processed. Database needs to be in single user mode.
> This is a bit mysterious for three reasons:
> 1) There should be nothing to repair: it's a brand new DB
> 2) I've told it not to bother doing integrity checks before backups in t
he
> plan (it was on, I turned it off today)
> 3) Using All Tasks -> Backup Database works fine (fast too!)
> Any ideas?|||I recommend removing the "repair minor problems" option from your integrity
checks. If you do have
corruption problems, you want to know about it and not try to sweep it under
the carpet. This is so
you can do root cause analysis of why you have those problems. The option is
said to be removed from
next version of SQL server, btw.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in messag
e
news:FC849EE8-986F-4F16-A044-BBFDC01509B9@.microsoft.com...[vbcol=seagreen]
> "AnthonyThomas" wrote:
> Reading between the lines of your message I was able to get the transactio
n
> log to backup by turning off the step in my maint plan. So now it works OK
at
> least, but I stand by my opinion that this should either not be a problem
in
> the first place, or should be MUCH more obvious as a potential issue.
> But now that task doesn't run. It seems to me that it would be a good idea
> to have it run every so often! Is there some "natural" time when I the
> database will be in the right mode? Perhaps after a backup?
>
the Integrity Check jobs created. This part of the SQLMAINT utility will ex
ecute the DBCC CHECKDB
statements. For this to execute, regardless if you've selected the ATTEMPT
TO FIX option, the
database will need to be put in SINGLE_USER mode. SQLMAINT will not be able
to do this if there are
persistent connections to the database that can not be dropped.[vbcol=seagreen]
an ALTER DATABASE SET OFFLINE/ONLINE WITH ROLLBACK IMMEDIATE statement.[vbcol=seagreen]
news:BCDA29C3-CEE3-4FA8-83CB-96151AF34A15@.microsoft.com...[vbcol=seagreen]