what's the possible reason to cause dbcc running automatically?

[Former Member]

I have a sp which truncates a large table(large 3G) and insert data into the table again(rebuild data for this table). When I run it on a production, it is fine.

but when I run it on a dev, it is very slow. After check the info in detail, I found there are lot of dbcc running during the sp running time.

when running the sp, got output like frequently:

09:35:58.865	DBMS	mysyb -- SQL Text: exec myproc ...
09:35:58.896	DBMS	mysyb -- DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
09:35:58.912	DBMS	mysyb -- DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
09:36:00.599	DBMS	mysyb -- DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

Not sure why. Before running the sp, somebody try to partition this table and then remove the partition. not sure if this is the reason.

So how to find out the reason for this dbcc? it is running automatically. If dbcc checktable or dbcc reindex will resolve this problem?

[former_member188958]

dbccs can be scheduled to run automatically at particular times using the job scheduler or a cron job.  But if it is running every time you execute the procedure, most likely some dbcc call has been included either in the stored procedure itself (or a sub procedure) or in an insert/update/delete trigger on a table that the procedure modifies.

You can look at the source code for the procedure or triggers with sp_helptext.

A quick and dirty check to see which if any compiled objects call DBCC  is:

select object_name(id), text from syscomments where upper(text) like "%DBCC%"

go

[Former Member]

no sp or trigger call dbcc in user database mydb. Only a few from master.

wondering if segment change or partition on table removed will trigger dbcc when data operation on those related object?

[former_member188958]

I can't think of any obvious way to trigger a dbcc on those actions, aside from rewriting sp_extendsegment, etc. to include a dbcc command.

A couple more possible approaches:

1)  If the dbcc is one that doesn't normally send output to the client except for the "dbcc exection complete" message, then turning on switch 3605 will direct the output to the errorlog and possibly allowing us to figure out which dbcc command(s) are being run, which may help figure out why and how they are being run.   ("undocumented" DBCCs like page, usedextents, etc. don't send output to the client by default)  3605 won't  help if the dbcc is one that normally sends output to the client (checkdb, checkindex etc.), nothing additional will be sent to the ASE errorlog.

set switch on 3605

go

execute <yourprocedure>

go

2) You might run your procedure under the stored procedure debugger utility sqldbgr and step through it while watching the output to see what statements trigger the dbcc message.

-bret