cachestore flush - a bad thing?

Posted on 2009-12-18
Medium Priority
Last Modified: 2012-05-08
I notice from my event logs that we are receiving a lot of cachestore flush notices and then subsequent notices of databases being opened.

The situation is better described in the post http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24072323.html?sfQueryTermInfo=1+cachestor+flush

I see that I could probably get rid of these notices by setting each database auto close setting to off but I'm wondering whether from a performance point of view it is better to close, flush and then reopen or set the auto close to false and leave them open all the time.

The environment is Windows 2003, SQL Server Express and we're running abourt 20-30 websites which utilise SQL Server Express.

It should be noted that we are not currently having any performance issues relating to SQL Server.
Question by:fvillena
  • 2
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 375 total points
ID: 26084170
for normal production databases, the setting (autoclose) should be set to off, to avoid this event happening.
when the database closes, all the dirty has has to be written to disk, an the data flushed from memory.
so, on the (first) subsequent call to the database it has to open, and any data to be read must be read from disk....

Author Closing Comment

ID: 31667858
Correct answer

Author Comment

ID: 26094354
Below is a stored procedure which will set Auto Close to Off on all databases

        @isql varchar(2000),
        @dbname varchar(64)
        declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
        open c1
        fetch next from c1 into @dbname
        While @@fetch_status <> -1
                select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
                select @isql = replace(@isql,'@dbname',@dbname)
                print @isql
                fetch next from c1 into @dbname
        close c1
        deallocate c1

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question