August 24, 2009 at 3:03 pm
Hi all. I'm primarily a sql developer but I also do whatever is necessary in our environment. I have an issue on a server that appears very odd. I have the following sequence of events in the SQL Server log:
Starting up database 'db_name'
Analysis of 'db_name' is 100% complete..
CHECKDB for database 'db_name' is 100% complete..
This group of 3 messages seems fairly self explanatory but I see this group of 3 messages repeated over and over throughout the day for the same database. The interval is anywhere from 3 to 15 minutes. I can scroll thru many screens of this exact same sequence. It appears to be happening to only one of our databases on the server but so far I have no idea why. I would expect this during startup but not throughout the day. (and no, our server is not being restarted or anything)
Does anybody have any suggestions on why this might be happening or what I should do as far as researching this issue goes ?
Thanks for your help.
August 24, 2009 at 3:08 pm
Check the properties of the database and see if the auto-close option is on. If so, that would explain it. With that option on, the database shuts down when the last user disconnects and then has to startup again when a user connects.
If it is on, turn it off.
August 24, 2009 at 3:24 pm
Thanks for the suggestion but AutoClose is set to False. I did notice the following: Initial datafile size was 152mb, current size is 569 mb, and growth rate was set to 1mb. This seem really funky to me so I changed the growth rate to 10% so it does not grow the file so frequently. I doubt this is the source of the log messages but was probably the result of somebody not paying attention when creating the db initially.
August 24, 2009 at 3:33 pm
Can you post the actual messages from the error log? (cut and paste). That's not a standard CheckDB message, so I'm going to assume you're paraphrasing.
If it says something like
CHECKDB for database 'Testing' finished without errors on 2008-12-22 10:20:06.007 (local time). This is an informational message only; no user action is required.
then that's not checkDB running, it's saying when CheckDB last ran. This message is printed every time a database comes online. If autoclose is off, then either the DB is being taken offline and brought online, detached and attached or restored from backup on a regular interval.
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
August 24, 2009 at 3:43 pm
William Plourde (8/24/2009)
Thanks for the suggestion but AutoClose is set to False. I did notice the following: Initial datafile size was 152mb, current size is 569 mb, and growth rate was set to 1mb. This seem really funky to me so I changed the growth rate to 10% so it does not grow the file so frequently. I doubt this is the source of the log messages but was probably the result of somebody not paying attention when creating the db initially.
I would not set it to grow at 10%. Each time it grows it will grow 10% of its current size, which means each time it grows, it grows at a large increment. I'd set it to grow at a fixed size. Also, the database should not be allowed to grow unchecked on its own. This could result in a highly freagmented file system. The database should have enough free space for 3 to 6 months data growth without having to autogrow. Adding space should be done actively by the DBA.
August 24, 2009 at 3:44 pm
Ok, It's a little repetitive but here it is. This is the exact verbiage except that I replaced the customer database name with 'CustomerDbName' for privacy reasons. I might be missing something but it appears to be the same stuff over and over.
08/05/2009 06:45:12,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:45:12,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:45:12,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:45:00,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:45:00,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:45:00,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:44:42,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:44:42,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:44:42,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:43:55,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:43:54,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:43:54,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:43:39,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:43:39,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:43:39,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:43:06,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:43:05,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:43:05,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:42:55,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:42:54,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:42:54,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:42:36,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:42:36,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:42:36,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:42:22,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:42:22,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:42:22,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:42:07,spid57,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:42:07,spid57,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:42:07,spid57,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:42:07,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:42:07,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:42:07,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:41:56,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:41:56,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:41:56,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:41:39,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:41:39,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:41:39,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:41:28,spid17s,Unknown,SQL Server has encountered 14 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
08/05/2009 06:41:28,spid17s,Unknown,SQL Server has encountered 14 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
08/05/2009 06:41:28,spid17s,Unknown,SQL Server has encountered 14 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
08/05/2009 06:41:27,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:41:27,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:41:27,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:41:08,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:41:08,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:41:08,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:41:00,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:41:00,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:41:00,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:40:48,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:40:48,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:40:48,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:40:34,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:40:34,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:40:34,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:40:21,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:40:20,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:40:20,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:40:01,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:40:01,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:40:01,spid58,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:39:48,spid57,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:39:48,spid57,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:39:48,spid57,Unknown,Starting up database 'CustomerDbName'.
08/05/2009 06:39:47,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time). This is an informational message only; no user action is required.
08/05/2009 06:39:47,spid58,Unknown,Analysis of database 'CustomerDbName' (37) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
08/05/2009 06:39:47,spid58,Unknown,Starting up database 'CustomerDbName'.
August 25, 2009 at 3:02 am
That's the standard database startup messages. SQL will always print those in the log when it brings a DB online and opens it.
The things that will cause that are:
autoclose
the DB being taken offline and brought online
Perhaps a restore.
Autogrow won't result in that message.
Since it happens so often, try running a trace to see what commands are run against that DB right before that appears in the log.
Are you sure autoclose is off for that DB? This is a classic sign of autoclose on.
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
August 25, 2009 at 5:18 am
Yes, it does appear to be an auto close type of situation but the auto close property was False when I checked it. I'm just going to monitor this and see if it happens again. I'm starting to wonder if somebody changed a setting or if some process could be effecting it. Everything I've seen points toward autoclose. Thanks everyone.
August 26, 2009 at 8:38 am
hmmm ... could it be that there is a transaction log backup that runs DBCC ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 26, 2009 at 8:49 am
rudy komacsar (8/26/2009)
hmmm ... could it be that there is a transaction log backup that runs DBCC ?
It's not running CheckDB. It's saying when CheckDB last ran successfully.
08/05/2009 06:41:00,spid58,Unknown,CHECKDB for database 'CustomerDbName' finished without errors on 2009-07-19 01:09:59.770 (local time)
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply