3

I have a nightly full backup that runs as well as a 2 hourly transaction log back. The full Backup also shrinks and checks the integrity of the database when it runs.

There does not appear to be any additional jobs that run except for a syspolicy_purge_history job that I believe is an automatically created job by SQL.

Thanks

Improve this question
3
  • What leads you to believe that CHECKDB is indeed running every 10 minutes automatically?
    – Chirag
    Oct 2, 2009 at 19:40
  • Windows Event Log CHECKDB for database 'mainDatabase' finished without errors on 2009-10-02 18:30:03.187 (local time). This is an informational message only; no user action is required.
    – TravisPUK
    Oct 5, 2009 at 6:32
  • Have you checked the history of all jobs to see if any run every 10 minutes?
    – Bryan S.
    Dec 8, 2009 at 16:01

4 Answers 4

Reset to default
5

Few things.

1) Turn off AUTO SHRINK. Its evil and can be source of long term performance issues as it can lead to file level fragmentation.

2) As mentioned above, it looks like AUTO CLOSE is turned ON. You need to turn it off as this is another one that is detrimental to good performance. Why? Every time the last connection is closed, SQL Server is trying to shut itself down and this leads to clearing the procedure and data cache. These two are the ones that support the good performance by reducing the need to fetch the data from disk, which is very expensive compared to reading from memory. And generating execution plans is a cpu intensive process and your sql server has to generate the plans after coming back from auto close. Now taht you know what its doing, go ahead and turn off AUTO CLOSE.

3) SQL Server is NOT really doing a DBCC CHECKDB but displaying a message after reading the boot file where it stores the last clean ran DBCC CHECKDB date. Its simply writing to the log file and not really running the CHECKDB here. It looks like it has been more than 2 months that you ran CHECKDB and its a good practise to run the CHECKDB in regular intervals as you can.

http://technet.microsoft.com/en-us/magazinebeta/2009.10.sqlqa.aspx

Improve this answer
2

I would check to see if Auto_Close is enabled on your databases. When it is enabled, your database will shutdown and free it's resources once the last user exits the connection. When a new user comes to use the database, it reopens. If your databases are running checkdb every time a database opens then this could be your problem.

The following query will check for Auto_Close on all databases:

SELECT * FROM sys.databases
WHERE is_auto_close_on = 1

Improve this answer
1

I had a similar circunstance of strange DBCC CHECKDBs running when I had nothing scheduled. Some minor digging lead me to one of our server engineers who had been instructed to backup the databases (these were Sharepoint dbs) with Symantec. Just prior to his backups, the Symantec tool was configured to run DBCC CHECKDB. In this case Symantec stayed connected so Activity Monitor helped me figure this out.

Improve this answer
0

Thanks for this post I just fixed 3 databases by disabling Auto Close and Auto Shrink to get rid of those "Starting up database ..." and "CHECKDB" messages every 5 minutes

And possible errors caused by them
27/08/2010 10:09:01     SQL Server has encountered 3 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. 
27/08/2010 10:09:01     SQL Server has encountered 3 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.       
27/08/2010 10:09:00     SQL Server has encountered 3 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .