June 29, 2009 at 1:39 pm
My company uses Altiris help desk software that connects to SQL Server 2005 Express.
Within the past 2 weeks users have complained that Altiris is occasionally very slow. I checked the SQL log file and discovered that the following messages appear approximately every 15 minutes.
Our version of SQL:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.2 (Build 3790: Service Pack 2)
2009-06-29 05:30:02.46 spid15s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2009-06-29 05:30:02.56 spid15s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2009-06-29 05:30:02.56 spid15s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2009-06-29 05:30:02.57 spid23s Starting up database 'Altiris_Incidents'.
I am not aware of any database maintenance or reconfiguration operations taking place.
Does anyone have any ideas about what may be wrong?
June 29, 2009 at 2:17 pm
What that message tells me is that you have the database set to auto close. That means the database is closed when nobody is accessing it anymore and then has to start up the next time someone needs it.
Turn that feature off and you should no longer have that particular problem.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 29, 2009 at 2:20 pm
I believe for sql express the database option auto close is enabled by default. Amongst other things this can cause the proc cache to be cleared causing slow downs in query response. If this option is on, turn it off.
Turn off auto shrink as well if it is on.
---------------------------------------------------------------------
June 29, 2009 at 2:31 pm
Thanks, Jeff.
Not being a DBA I want to make sure there will be no adverse effects of turning Autoclose off. Can you think of any reasons why I wouldn't want to do it?
June 29, 2009 at 2:36 pm
Patrick Womack (6/29/2009)
Thanks, Jeff.
you're welcome 😉
Not being a DBA I want to make sure there will be no adverse effects of turning Autoclose off. Can you think of any reasons why I wouldn't want to do it?
on behalf of Jeff and myself, there are none
---------------------------------------------------------------------
June 29, 2009 at 3:02 pm
As George has stated, there are no reason to have that option enabled. Turn it off - and also (as George stated) turn off auto shrink if it is enabled also.
BTW - you might also want to verify your recovery model and make sure you have the right backup/maintenance plan in place.
If your database is set to full recovery model, make sure you are performing frequent transaction log backups. If you don't need point in time recovery, you can change the database recovery model to simple.
Review the article I link to in my signature on how to manage the transaction logs.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 29, 2009 at 4:04 pm
We run Altiris here. Our 'Incidents' database is very small at only 9MB and is in simple user mode. It appears to be static but that may be installation specific.
Its sister database, Altiris is much larger and very busy and in full recovery mode. Check the options on that database too.
Recovery mode chosen all depends on how much data you can afford to lose in the event of some sort of disaster.
edited for clarification
---------------------------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply