Clean checkdb & checkcatalog - why is "Attempt to repair any minor problems" kicking in ?

A client was complaining that their DB Maint. plans ( on a server set
up by them )started failing.
Checked it out and once I turned the "Attempt to repair any minor
problems" off it was OK. I understand this is a known problem.
I never turn this option on myself so I am not familiar with it.
Integrity check should be running an DBCC CHECKDB, right?
So this option would probably be the equivalent of the REPAIR_FAST
option.
I did a dbcc checkdb and checkcatalog manually and both were clean.
Why is "auto repair" kicking in if everything is OK?
or
If there is a problem to fix why is it not showing up in DBCC?
Any suggestions?
Thanks
ZSeems like you assume that maint wiz first run DBCC CHECKDB without the repair option and if it find
a problem, it run again, using FAST_REPAIR. It doesn't. It just runs the command once, using the
FAST_REPAIR option. IMO, exposing this option in a wizard-like tool is bad design...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"zoranlee" <zoranlee@.hotmail.com> wrote in message
news:3842add9.0406161549.585ece6e@.posting.google.com...
> A client was complaining that their DB Maint. plans ( on a server set
> up by them )started failing.
> Checked it out and once I turned the "Attempt to repair any minor
> problems" off it was OK. I understand this is a known problem.
> I never turn this option on myself so I am not familiar with it.
> Integrity check should be running an DBCC CHECKDB, right?
> So this option would probably be the equivalent of the REPAIR_FAST
> option.
> I did a dbcc checkdb and checkcatalog manually and both were clean.
> Why is "auto repair" kicking in if everything is OK?
> or
> If there is a problem to fix why is it not showing up in DBCC?
> Any suggestions?
> Thanks
> Z|||Tibor, you mean REPAIR_FAST.
The command has to run in single_user mode and the maintenance job is
failing because of that. You shouldn't use that option and I've ensured its
been removed in SQL Server 2005.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OBWpvLDVEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Seems like you assume that maint wiz first run DBCC CHECKDB without the
repair option and if it find
> a problem, it run again, using FAST_REPAIR. It doesn't. It just runs the
command once, using the
> FAST_REPAIR option. IMO, exposing this option in a wizard-like tool is bad
design...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "zoranlee" <zoranlee@.hotmail.com> wrote in message
> news:3842add9.0406161549.585ece6e@.posting.google.com...
> > A client was complaining that their DB Maint. plans ( on a server set
> > up by them )started failing.
> >
> > Checked it out and once I turned the "Attempt to repair any minor
> > problems" off it was OK. I understand this is a known problem.
> >
> > I never turn this option on myself so I am not familiar with it.
> > Integrity check should be running an DBCC CHECKDB, right?
> > So this option would probably be the equivalent of the REPAIR_FAST
> > option.
> >
> > I did a dbcc checkdb and checkcatalog manually and both were clean.
> >
> > Why is "auto repair" kicking in if everything is OK?
> > or
> > If there is a problem to fix why is it not showing up in DBCC?
> >
> > Any suggestions?
> >
> > Thanks
> >
> > Z
>|||> Tibor, you mean REPAIR_FAST.
Indeed. Thanks for catching that.
> and I've ensured its
> been removed in SQL Server 2005.
I'm happy to hear that! :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%233rYvdOVEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Tibor, you mean REPAIR_FAST.
> The command has to run in single_user mode and the maintenance job is
> failing because of that. You shouldn't use that option and I've ensured its
> been removed in SQL Server 2005.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OBWpvLDVEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Seems like you assume that maint wiz first run DBCC CHECKDB without the
> repair option and if it find
> > a problem, it run again, using FAST_REPAIR. It doesn't. It just runs the
> command once, using the
> > FAST_REPAIR option. IMO, exposing this option in a wizard-like tool is bad
> design...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "zoranlee" <zoranlee@.hotmail.com> wrote in message
> > news:3842add9.0406161549.585ece6e@.posting.google.com...
> > > A client was complaining that their DB Maint. plans ( on a server set
> > > up by them )started failing.
> > >
> > > Checked it out and once I turned the "Attempt to repair any minor
> > > problems" off it was OK. I understand this is a known problem.
> > >
> > > I never turn this option on myself so I am not familiar with it.
> > > Integrity check should be running an DBCC CHECKDB, right?
> > > So this option would probably be the equivalent of the REPAIR_FAST
> > > option.
> > >
> > > I did a dbcc checkdb and checkcatalog manually and both were clean.
> > >
> > > Why is "auto repair" kicking in if everything is OK?
> > > or
> > > If there is a problem to fix why is it not showing up in DBCC?
> > >
> > > Any suggestions?
> > >
> > > Thanks
> > >
> > > Z
> >
> >
>

Checkpoint not being issued

Hi,

Has anybody encountered this situation before? DB on SQL Server 2000 SP4 with trunc log on chkpt option turned on. Checkpoint trace flags were turned on but noticing no checkpoints are being done on one specific DB resulting into growing transaction log. No open transactions.

Any ideas?

Thanks.What traceflags are you using, and why?

Checkpoint file used by another process

I have an SSIS solution with 8 packages in it. I have checkpoint turned on with the 'If Exists' option. Each of the 8 packages have 8 separate checkpoint files specified.

One out of two runs will fail with one of the below errors:

The checkpoint file \\xxxxxxxx is locked by another process. This may occur if another instance of this package is currently executing.

Checkpoint file \\xxxxxxxx failed to open due to error 0x80070020 "The process cannot access the file because it is being used by another proces

I have checked all the settings and everything looks fine, looks like the problem is when you have many Control Flow tasks in a package and if two of them are completed at the same time and they try to write to this file one of them is unable to write and it fails.

This is causing the entire job to fail even though the control flow was successful.

Anyone encounter this issue? Any assistance is appreciated.

Thanks!

I am under the impression that the package (process) controls checkpoints not the tasks. Checkpointing is at the package level, you only enable tasks to use it.

This is interesting...maybe someone can shed light on it.

What happens if you try to run the two DFTs in sequence?

|||

Philips-HCR wrote:

I have an SSIS solution with 8 packages in it. I have checkpoint turned on with the 'If Exists' option. Each of the 8 packages have 8 separate checkpoint files specified.

One out of two runs will fail with one of the below errors:

The checkpoint file \\xxxxxxxx is locked by another process. This may occur if another instance of this package is currently executing.

Checkpoint file \\xxxxxxxx failed to open due to error 0x80070020 "The process cannot access the file because it is being used by another proces

I have checked all the settings and everything looks fine, looks like the problem is when you have many Control Flow tasks in a package and if two of them are completed at the same time and they try to write to this file one of them is unable to write and it fails.

This is causing the entire job to fail even though the control flow was successful.

Anyone encounter this issue? Any assistance is appreciated.

Thanks!

Based on my own experiences I doubt that is the problem. I have had multiple concurrent tasks writing to a checkpoint file withno problem at all. And Ravi is right, they are all written by the same thread (i.e. the one running the package) not by a seperate thread for each task. I suspect your idea about concurrent tasks is a red herring.

Get hold of Process Monitor (http://www.microsoft.com/technet/sysinternals/default.mspx) to find out what is holding onto those files.

-Jamie

|||I have not tried it just because there are numerous tasks and the check point file that fails is not always the same either. Let me try one at a time to isolate the problem. Thanks for the advice!|||Good idea, let me try that. Thanks!|||

Philips-HCR wrote:

I have not tried it just because there are numerous tasks and the check point file that fails is not always the same either. Let me try one at a time to isolate the problem. Thanks for the advice!

By the way, are you running the 8 packages in series or parallel?
You might want to try running them in serial too.