fvillena
asked on
cachestore flush - a bad thing?
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 https://www.experts-exchange.com/questions/24072323/cachestore-flush-error.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.
The situation is better described in the post https://www.experts-exchange.com/questions/24072323/cachestore-flush-error.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.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Below is a stored procedure which will set Auto Close to Off on all databases
USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
begin
select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
select @isql = replace(@isql,'@dbname',@d
print @isql
exec(@isql)
fetch next from c1 into @dbname
end
close c1
deallocate c1
USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
begin
select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
select @isql = replace(@isql,'@dbname',@d
print @isql
exec(@isql)
fetch next from c1 into @dbname
end
close c1
deallocate c1
ASKER