• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3424
  • Last Modified:

SQL 2005 SERVER LOGS

Hello

We are using SQL Server 2005 Standard Edition 64 bit, running on Windows NT. Last night, I moved the system databases to the C drive to a different drive. The box has six instances. I carried out the move on one instance. However, I have been getting these error messages that I do not understand. 'SQL Server has encountered 4 occurence os cachestore flush for the bound trees cachestore (part of plan cache) dure to some database maintenance or reconfigure options'

Could someone please explain what this is and how to rectify if there is something wrong?

Many Thanks
0
novicedbaUK
Asked:
novicedbaUK
  • 5
  • 4
1 Solution
 
Kevin3NFCommented:
IN sql 2005, if you change ANY of the database options such as recover model, auto-close, etc, SQL dumps the procedure cache.  I think this applies to database restores as well.

The only issue with this (besides being bad SQL design from MS) is that all the stored procs have to recompile the next time they are called, so on a very busy system you get a short performance hit
0
 
novicedbaUKAuthor Commented:
Many thanks

At least I have nothing to worry about. I think most of it was auto close as these databases were showing a status of Shutdown, Normal. I queried some of the tables and this error cleared
0
 
Kevin3NFCommented:
Auto-Close should not be enabled for any database, any server, ever :)

Glad I could help
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
novicedbaUKAuthor Commented:
I have not enabled auto close on any database. Why were they showing a status of shutdown, normal? As mentioned onece I queried some of the tables the status changed to Normal. FRom reading a few posts on shutdown, normal status, a lot of them referenced auto-close???
0
 
Kevin3NFCommented:
no idea...could be something in 2005 I've not seen.
Run this to generate a list of statements that will then tell you what the Auto Close Value is for each database:

Select 'Select DatabasePropertyEx('''+[name]+''',''IsAutoClose'')'
From master..sysdatabases
0
 
Kevin3NFCommented:
no idea...could be something in 2005 I've not seen.
Run this to generate a list of statements that will then tell you what the Auto Close Value is for each database:

Select 'Select DatabasePropertyEx('''+[name]+''',''IsAutoClose'')'
From master..sysdatabases
0
 
novicedbaUKAuthor Commented:
I have three databases returning a value od 1, which is a bit worrying as two of them are live. I know there is a feature ti turn it off but dont want to enforce it without doing it on a test environment. Also what are the implications of a database being on autoclose?
0
 
Kevin3NFCommented:
it just wastes cpu cycles closing it and then re-opening for the next connection.

You can change this in the GUI (database properties) with NO impact to production other than the cacheflush you noted above.  Save it for after hours :)
0
 
novicedbaUKAuthor Commented:
Many thanks. You have been very helpful
0
 
AdamNoffieCommented:
Hey, thanks for this info.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now