cachestore flush - a bad thing?

Posted on 2009-12-18
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

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
    LVL 142

    Accepted Solution

    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

    Correct answer

    Author Comment

    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

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now