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 whether or not you have the Integrity Check jobs created. 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. 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 SQLMAINT step to execute an ALTER DATABASE SET OFFLINE/ONLINE WITH ROLLBACK IMMEDIATE statement.
Sincerely,
Anthony Thomas
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message 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 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?|||"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 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?
> 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 will 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. 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 SQLMAINT step to execute an ALTER DATABASE SET OFFLINE/ONLINE WITH ROLLBACK IMMEDIATE statement.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message 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 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?
|||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 message
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 transaction
> 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 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.[vbcol=seagreen]
an ALTER DATABASE SET OFFLINE/ONLINE WITH ROLLBACK IMMEDIATE statement.[vbcol=seagreen]
news:BCDA29C3-CEE3-4FA8-83CB-96151AF34A15@.microsoft.com...[vbcol=seagreen]
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment