March 2, 2010 at 11:56 am
I can look in the Event Viewer for Event ID 8957 and see that DBCC CHECKDB is being ran on every database by the domain administrator account. The problem is that it runs every night at a different time. For example (12AM, 5AM, 1AM). I have no job in SQL Server Agent that runs a DBCC CHECKDB. I have no maintenance plan that runs DBCC CHECKDB. The part that perplexes me is that the time it runs varies from day to day and time to time. Could this be called by an application?
If anyone has any ideas, I would love to hear them.
March 2, 2010 at 12:16 pm
DBCC could be run from just about anything. You could have it run through an application, powershell script, OSQL, SSIS Package (from another machine)...pretty much anywhere that you can run sql code you can run a checkdb given that the user has permissions to do so.
March 2, 2010 at 12:35 pm
I would setup a profiler trace.. add additional columns such as hostname,ip,login and then the standard stuff and filter the trace to only pickup that domain admin account and if it is not logging just a ton of stuff for that account, I would leave it up overnight. You could even filter on the DBCC command or find some other way to narrow down the profiler capture. I would not normally recommend leaving a trace up overnight unless it is logging the bare minimum needed to troubleshoot the problem.
Find the hostname executing the command should shed more light on where to look further.
I would also check the sql server logs for any clues there. Also look for anything indicating corruption or I/O issues. Maybe SQL is detecting some type of corruption and is kicking of the dbcc checkdb to verify consistency.
March 2, 2010 at 12:51 pm
mike mcneer (3/2/2010)
I would setup a profiler trace.. add additional columns such as hostname,ip,login and then the standard stuff and filter the trace to only pickup that domain admin account and if it is not logging just a ton of stuff for that account, I would leave it up overnight. You could even filter on the DBCC command or find some other way to narrow down the profiler capture. I would not normally recommend leaving a trace up overnight unless it is logging the bare minimum needed to troubleshoot the problem.Find the hostname executing the command should shed more light on where to look further.
I would also check the sql server logs for any clues there. Also look for anything indicating corruption or I/O issues. Maybe SQL is detecting some type of corruption and is kicking of the dbcc checkdb to verify consistency.
Youu can also set it up as a server side trace, and then you wouldn't have the overhead of Profiler running.
March 2, 2010 at 12:51 pm
I'd second Mike's suggestion for a trace. That will help narrow down from where it's running.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 2, 2010 at 12:58 pm
mike mcneer (3/2/2010)
Maybe SQL is detecting some type of corruption and is kicking of the dbcc checkdb to verify consistency.
SQL won't run checkDB automatically. It's a very CPU and IO intensive operation, it's not something that can be quietly run in the background without affecting users.
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
March 2, 2010 at 1:00 pm
Nick, what's the exact message you're seeing in the log? (obfuscate domain and DB names if you like)
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
March 2, 2010 at 1:01 pm
Yep Server side trace.. my main concern was the profiler running the C drive out of space. I have not checked lately in the 2008 tools but in the previous version(s) I know there is no way to direct the profiler output to a drive other than C: .. found this out the hard way when i filled up the c drive on the server taking a trace..ooops.. fortunately I caught it before it became a problem 🙂 does anyone know if that has been fixed? i would love to run my profiler to a partition with a ton of storage on it to take longer traces.
March 2, 2010 at 1:07 pm
Don't run profiler GUI on the server, run it on a client and point it at the server.
Don't run profiler GUI against a busy production server. Use a server-side trace instead.
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
March 2, 2010 at 1:10 pm
GilaMonster (3/2/2010)
mike mcneer (3/2/2010)
Maybe SQL is detecting some type of corruption and is kicking of the dbcc checkdb to verify consistency.SQL won't run checkDB automatically. It's a very CPU and IO intensive operation, it's not something that can be quietly run in the background without affecting users.
I may be wrong about this I cant recall 100% but I am thinking hard shutdown your server or instance and upon restarting the instance it does a DBCC checkdb on the databases which I thought it was to make sure they were consistent before onlining them... I could have swore I have seen that happening in the logs but then again I could be thinking about something else. I am thinking along the lines of he is having a san failure or i/o problem maybe not even long enough to notice but then Sql is checking the consistency?? i dunno just a shot in the dark without a trace. It doesnt make sense that he says it is happening on all Db's..that makes me suspect more it is coming from SQl server as opposed to a user or app initiating it.
March 2, 2010 at 1:42 pm
mike mcneer (3/2/2010)
I may be wrong about this I cant recall 100% but I am thinking hard shutdown your server or instance and upon restarting the instance it does a DBCC checkdb on the databases which I thought it was to make sure they were consistent before onlining them... I could have swore I have seen that happening in the logs but then again I could be thinking about something else.
Oh, the messages you're thinking about are there, but that's not SQL running checkDB. That's SQL telling you when checkDB last ran successfully on that database. That message will appear in the logs every time a database is brought online.
Next time you look at the logs, look more carefully at the message, specifically at the log date and the checkDB date. It'll be something like this (extracted from my local server)
Date 2010/03/02 15:13:25
Log SQL Server (Current - 2010/03/02 15:13:00)
Source spid22s
Message
CHECKDB for database 'Testing' finished without errors on 2009-10-01 11:30:09.390 (local time). This is an informational message only; no user action is required.
I've worked with databases that took 4+ hours to run checkDB on. I assure you, it did not take 4+ hours for SQL to bring those databases online.
I am thinking along the lines of he is having a san failure or i/o problem maybe not even long enough to notice but then Sql is checking the consistency??
No. SQL will not run CheckDB automatically. An IO problem would trigger an error 823 or 825, nothing more.
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
March 2, 2010 at 1:52 pm
Good points and explanation. Thanks for clearing that up for me.
March 3, 2010 at 12:34 am
mike mcneer (3/2/2010)
Maybe SQL is detecting some type of corruption and is kicking of the dbcc checkdb to verify consistency.
Can it be possible that Sql server automatically run DBCC Checkdb command and if yes , then how it would use its details ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 3, 2010 at 12:39 am
Bhuvnesh (3/3/2010)
mike mcneer (3/2/2010)
Maybe SQL is detecting some type of corruption and is kicking of the dbcc checkdb to verify consistency.Can it be possible that Sql server automatically run DBCC Checkdb command and if yes , then how it would use its details ?
Sorry i didnt read whole post before posting this ques.
My Bad:-)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 3, 2010 at 8:51 am
Check to see if those databases have auto close enabled.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply