DBCC running too long?

DBCC running too long?

Post by Ron Lounsbur » Fri, 17 Nov 2000 04:00:00



Yesterday morning we kicked off a "DBCC CHECKALLOC" command from query
analyzer.  Today, 30 hours later, it is still running, and the database and
transaction log seem to have grown by atleast a factor of 10!  I am more of
a programmer that a database administrator, but I get the task of
administering this database.  Does this seem excessive to anyone else, or am
I OK?  I'm not sure exactly how large the database file was when we started

Can I stop the DBCC?  Should I?

any suggestions would be gratefully accepted.

TIA
Ron Lounsbury
Progeny Systems Corp.
(Remove numbers from address when replying by e-mail)

 
 
 

DBCC running too long?

Post by John Cortenbac » Fri, 17 Nov 2000 04:00:00


Ron,

You didn't state "why" you ran DBCC CHECKALLOC. As stated in BOL, I'll
quote:

"DBCC CHECKDB is the safest repair statement because it identifies and
repairs the widest possible range of errors. If only allocation errors are
reported for a database, execute DBCC CHECKALLOC with a repair option to
correct them. However, to ensure that all errors (including allocation
errors) are repaired properly, execute DBCC CHECKDB with a repair option."

Since your email didn't state you added any parameters or options to the
command, it really isn't repairing anything ... it's just checking for
allocation errors.

I ran the same command on a 300MB DB and it tooks 7 seconds.

HTH.


> Yesterday morning we kicked off a "DBCC CHECKALLOC" command from query
> analyzer.  Today, 30 hours later, it is still running, and the database
and
> transaction log seem to have grown by atleast a factor of 10!  I am more
of
> a programmer that a database administrator, but I get the task of
> administering this database.  Does this seem excessive to anyone else, or
am
> I OK?  I'm not sure exactly how large the database file was when we
started

> Can I stop the DBCC?  Should I?

> any suggestions would be gratefully accepted.

> TIA
> Ron Lounsbury
> Progeny Systems Corp.
> (Remove numbers from address when replying by e-mail)


 
 
 

DBCC running too long?

Post by John Cortenbac » Fri, 17 Nov 2000 04:00:00


BTW - You should be able to stop the command with no problem.


> Yesterday morning we kicked off a "DBCC CHECKALLOC" command from query
> analyzer.  Today, 30 hours later, it is still running, and the database
and
> transaction log seem to have grown by atleast a factor of 10!  I am more
of
> a programmer that a database administrator, but I get the task of
> administering this database.  Does this seem excessive to anyone else, or
am
> I OK?  I'm not sure exactly how large the database file was when we
started

> Can I stop the DBCC?  Should I?

> any suggestions would be gratefully accepted.

> TIA
> Ron Lounsbury
> Progeny Systems Corp.
> (Remove numbers from address when replying by e-mail)

 
 
 

DBCC running too long?

Post by Ron Lounsbur » Fri, 17 Nov 2000 04:00:00


John
    Thanks for your response.  We ran this because we were getting an error
when we attempted any changes to the database that added a constraint.  The
error message was:
'tblSubsystems' table
- Unable to create index 'PK_tblSubsystems'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Backup,
CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE
FILE) operations on a database must be serialized. Reissue the statement
after the current backup, CHECKALLOC, or file manipulation operation is
completed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.

At the time, CHECKALLOC was not running, and one of our progeammers presumed
that this meant that we needed to run it.

--
Ron Lounsbury
Progeny Systems Corp.
(Remove numbers from address when replying by e-mail)


> Ron,

> You didn't state "why" you ran DBCC CHECKALLOC. As stated in BOL, I'll
> quote:

> "DBCC CHECKDB is the safest repair statement because it identifies and
> repairs the widest possible range of errors. If only allocation errors are
> reported for a database, execute DBCC CHECKALLOC with a repair option to
> correct them. However, to ensure that all errors (including allocation
> errors) are repaired properly, execute DBCC CHECKDB with a repair option."

> Since your email didn't state you added any parameters or options to the
> command, it really isn't repairing anything ... it's just checking for
> allocation errors.

> I ran the same command on a 300MB DB and it tooks 7 seconds.

> HTH.



> > Yesterday morning we kicked off a "DBCC CHECKALLOC" command from query
> > analyzer.  Today, 30 hours later, it is still running, and the database
> and
> > transaction log seem to have grown by atleast a factor of 10!  I am more
> of
> > a programmer that a database administrator, but I get the task of
> > administering this database.  Does this seem excessive to anyone else,
or
> am
> > I OK?  I'm not sure exactly how large the database file was when we
> started

GB.

> > Can I stop the DBCC?  Should I?

> > any suggestions would be gratefully accepted.

> > TIA
> > Ron Lounsbury
> > Progeny Systems Corp.
> > (Remove numbers from address when replying by e-mail)

 
 
 

DBCC running too long?

Post by Jeff Ericso » Fri, 17 Nov 2000 04:00:00


You don't need to run it due to that error message.  Kill it with kill spid.
Find out what else is running on that server.  sounds like a dump job or
something is hung up.


> Yesterday morning we kicked off a "DBCC CHECKALLOC" command from query
> analyzer.  Today, 30 hours later, it is still running, and the database
and
> transaction log seem to have grown by atleast a factor of 10!  I am more
of
> a programmer that a database administrator, but I get the task of
> administering this database.  Does this seem excessive to anyone else, or
am
> I OK?  I'm not sure exactly how large the database file was when we
started

> Can I stop the DBCC?  Should I?

> any suggestions would be gratefully accepted.

> TIA
> Ron Lounsbury
> Progeny Systems Corp.
> (Remove numbers from address when replying by e-mail)

 
 
 

DBCC running too long?

Post by Ron Lounsbur » Sat, 18 Nov 2000 04:00:00


Thanks to all.  I think that it turned out to be a backup job that was hung
up and blocking the DBCC.  I rebooted the server and DBCC ran in a few
seconds.

--
Ron Lounsbury
Progeny Systems Corp.
(Remove numbers from address when replying by e-mail)


> You don't need to run it due to that error message.  Kill it with kill
spid.
> Find out what else is running on that server.  sounds like a dump job or
> something is hung up.



> > Yesterday morning we kicked off a "DBCC CHECKALLOC" command from query
> > analyzer.  Today, 30 hours later, it is still running, and the database
> and
> > transaction log seem to have grown by atleast a factor of 10!  I am more
> of
> > a programmer that a database administrator, but I get the task of
> > administering this database.  Does this seem excessive to anyone else,
or
> am
> > I OK?  I'm not sure exactly how large the database file was when we
> started

GB.

> > Can I stop the DBCC?  Should I?

> > any suggestions would be gratefully accepted.

> > TIA
> > Ron Lounsbury
> > Progeny Systems Corp.
> > (Remove numbers from address when replying by e-mail)