DBCC CHECKDB

Question

Post reply

DBCC CHECKDB

MasterDB

Hall of Fame

Points: 3054
More actions
January 18, 2012 at 12:35 am

#263034

Hi Folks,
I've not created or automated any job to do run DBCC CHECKDB. But i noticed that DBCC CHECKDB is running on daily basis. This will consume lot of physical memory that time.
How can i know where exactly and who is using this command in my SQL SERVER 2005.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

You can use Profiler for this purpose.

Check whether AutoClose property is ON for this DB.

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 2

SQL error log should tell you which SQL account was used to run it.

Regards,
Raj

http://Strictlysql.blogspot.com

MasterDB Hall of Fame Points: 3054 More actions · Answer 3

Thanks a lot for quick response.

Autoclose option is set to false.Now i started profiler to check.

I can't find any thing from SQL ErrorLogs.

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 4

Your errorlog should say

DBCC CHECKDB (<db name>) executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 47 seconds.

Regards,
Raj

http://Strictlysql.blogspot.com

MasterDB Hall of Fame Points: 3054 More actions · Answer 5

No I didn't find any thing in errorlogs about DBCC CHECKDB.

DBCC CHECKDB was last executed on 2012-01-17(i.e., yesterday). But since 13/12/2011 there are no error logs..

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

MasterDB (1/18/2012)
I've not created or automated any job to do run DBCC CHECKDB.

Why not? You should IMHO.

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

MasterDB (1/18/2012)
I've not created or automated any job to do run DBCC CHECKDB. But i noticed that DBCC CHECKDB is running on daily basis.

Why do you think it's running daily?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MasterDB Hall of Fame Points: 3054 More actions · Answer 8

I got a query, which results when DBCC CHECKDB was ran last time. so that i came to know it was executed yesterday.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

And that query is?????

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

InvisibleCat Default port Points: 1496 More actions · Answer 10

Firstly I think you should be automating your CHECKDB.

Secondly, if you saying it's not in the log but your query say's it has been run then that's odd.

The way I check this is by using the following;

DBCC dbinfo('DatabaseName) WITH TABLERESULTS

Then look for the record with a field value equal to 'dbi_dbccLastKnownGood'.

Can you confirm what the date is that this was last run?

Edit

-----

As Gail points out, can you show us your query?

MasterDB Hall of Fame Points: 3054 More actions · Answer 11

IF OBJECT_ID('tempdb..#CheckDB') IS NOT NULL

DROP TABLE #CheckDB

CREATE TABLE #CheckDB (LogDate DATETIME, ProcessInfo VARCHAR(50), [Text] VARCHAR(500))

INSERT #CheckDB

EXEC XP_READERRORLOG 0, 1, 'CheckDB'

SELECT SUBSTRING([Text], 23, CHARINDEX( '''',[Text],23)-23) AS DatabaseName,

SUBSTRING([Text], CHARINDEX( '2',[Text],PATINDEX('%errors%', [Text])), 23) AS LastCheckDBRanDate

FROM #CheckDB

WHERE [Text] LIKE 'CHECKDB for database%'

UNION

SELECT SUBSTRING([Text], 15, CHARINDEX( ')',[Text],15)-15) AS DatabaseName,

LogDate AS LastCheckDBRanDate

FROM #CheckDB

WHERE [Text] LIKE 'DBCC CHECKDB%'

DROP TABLE #CheckDB

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

And for one database that shows yesterday?

Sure there's no maint plan that someone set up? (btw, CheckDB daily is a good idea)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

InvisibleCat Default port Points: 1496 More actions · Answer 13

MasterDB (1/18/2012)
Thanks a lot for quick response.
Autoclose option is set to false.Now i started profiler to check.
I can't find any thing from SQL ErrorLogs.

I'm a little confused, you said the error log had no mention of the CHECKDB or who ran it?

If so then how does the xp_readerrorlog work? Is it possible that you could have a maintenance plan on another server that is running the check on this server?