June 23, 2012 at 12:17 pm
Hi,
How to check whatprocess started the DBCC check DB.
In our server there is no sql job/windows job for dbcc checkdb
But network saying there is dbcc checkbd goingon only 1 database and taking most resources. Please advise.
June 23, 2012 at 4:20 pm
If the dbcc command is still going you can call a command of
sp_who2 active
in SSMS to see some connection information.
Otherwise, some DBCC commands get logged in the SQL server logs, depending on logging levels. You can also sometimes find the windows application log to have some details, again, depending on logging levels.
June 23, 2012 at 4:51 pm
I would check the default trace first.
June 24, 2012 at 5:51 pm
Agreed with Clayman and Matt,
If you think this is something happened automatically? Then you probably have to look at the database options if there is "Auto Close" option set to True or not. If it is true, turn it OFF. What this option does is when the last user disconnect, database shutdown and when new user try to login, database reopens. Every time it opens it will run dbcc checkdb on the database and logged the below logs in errorlogs.
Starting up database 'dbname'
CHECKDB for database 'dbname' finished without errors
I am not sure if autoclose option really run checkdb against the db or just echoing the result of last known value of dbcc check db ran (Never Tested.)
You can find the last known checkdb ran against any database inside the boot page of the database by enabling the trace flag 3604 as below:
DBCC TRACEON(3604)
DBCC PAGE ('dbname',1,9,3) --look for value of dbi_dbccLastKnownGood
Hope this helps..
-AB
June 25, 2012 at 1:43 am
sql.AB (6/24/2012)
What this option does is when the last user disconnect, database shutdown and when new user try to login, database reopens. Every time it opens it will run dbcc checkdb on the database and logged the below logs in errorlogs.
No it does not. SQL never runs CheckDB automatically, only according to a job or user request.
All that happens when SQL starts up a database is that it prints to the error log the last known good checkDB date. Compare the date of the log record and the date of the checkDB and you'll see.
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
June 25, 2012 at 6:17 am
GilaMonster (6/25/2012)
sql.AB (6/24/2012)
What this option does is when the last user disconnect, database shutdown and when new user try to login, database reopens. Every time it opens it will run dbcc checkdb on the database and logged the below logs in errorlogs.No it does not. SQL never runs CheckDB automatically, only according to a job or user request.
All that happens when SQL starts up a database is that it prints to the error log the last known good checkDB date. Compare the date of the log record and the date of the checkDB and you'll see.
Gail, could you give some details on your statement? Is this something that is not being logged or traced correctly? This is a known thing when Auto Close is turned on. Here is a linke to a Forum article that talks about it also. 55 database server and each first user connection SPID into each db showed in trace as running checkdb. There are other references, but this was the first I found when I checke MSDN before respondeing. 😎
June 25, 2012 at 6:33 am
SanDroid (6/25/2012)
Here is a linke to a Forum article that talks about it also. 55 database server and each first user connection SPID into each db showed in trace as running checkdb.
No, it does not. The first message from each DB is SQL printing the time that CheckDB last ran successfully. Read the message.
CHECKDB for database '<database name>' finished without errors on <timestamp> (local time). This is an informational message only; no user action is required.
When checkDB actually runs, the message is this:
DBCC CHECKDB (<database name>) WITH no_infomsgs executed by <login> found 0 errors and repaired 0 errors. Elapsed time: <time taken>
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
June 25, 2012 at 6:41 am
Gail I understand what what your are saying and agree with your statement.
I do not think clear enough with my question.
Do you have any information for those using SQL 2005 what the difference is in the CheckDB they see running when auto_close is on?
This would be the one that shows as running in SQL trace that does not seem to stop, and not the line item in SQL logs you refer to.
I do not have a pre SP3 SQL 2005 server to test on, but from what I have heard from others this morning it does not happen on any version of SQL server after SQL 2008 release.
June 25, 2012 at 6:44 am
SanDroid (6/25/2012)
Do you have any information for those using SQL 2005 what the difference is in the CheckDB they see running when auto_close is on?
CheckDB only runs when someone manually runs it or when it is scheduled. SQL does not run it automatically. If it's running, it's because someone ran the command or a job ran the command.
Got a connect item or kb article on that pre-SP3 bug?
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
June 25, 2012 at 6:54 am
There has never, ever, ever been a time when DBCC CHECKDB is run automatically by SQL Server - no matter what you may think you've seen implied by others in forum posts. Some other job/connection/script/user is running the DBCC CHECKDBs or they are seeing the last-known-good message when the database starts.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
June 25, 2012 at 7:11 am
GilaMonster (6/25/2012)
SanDroid (6/25/2012)
Do you have any information for those using SQL 2005 what the difference is in the CheckDB they see running when auto_close is on?CheckDB only runs when someone manually runs it or when it is scheduled. SQL does not run it automatically. If it's running, it's because someone ran the command or a job ran the command.
Got a connect item or kb article on that pre-SP3 bug?
No I do not have this, wish I did. All I can find is some forum posts where people have blamed it on Auto_Close and turned it off to resolve. This article talks about the auto_close option dropping procedure cache, but that is the only one that mentions auto_close and checkdb.
http://support.microsoft.com/kb/917828
I also noticed two other things; None of them mention the specific server type they are on, or are on SQL Express, turned of Auto_Close, and seen it stop. Kinda impossible to report something that you can not reproduce after installing the latest SP. right? 😀
I just found some information where other people have resolved this issue by turning off auto_close and thought some more details might be available. Apparently there are none.
June 25, 2012 at 7:32 am
SanDroid (6/25/2012)
No I do not have this, wish I did. All I can find is some forum posts where people have seen this and resolved it or blamed it on Auto_Close and turned it off to resolve.
Extremely likely they are misinterpreting the last known good checkDB message in the error log and thinking it means that SQL is running CheckDB. It is not, it's just printing the date of the last time checkDB ran successfully.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply