Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL 2005 SERVER LOGS

Posted on 2007-11-30
10
3,415 Views
Last Modified: 2012-06-22
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
Comment
Question by:novicedbaUK
  • 5
  • 4
10 Comments
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 250 total points
ID: 20381936
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
 

Author Closing Comment

by:novicedbaUK
ID: 31411884
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 20382010
Auto-Close should not be enabled for any database, any server, ever :)

Glad I could help
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:novicedbaUK
ID: 20382038
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 20382137
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 20382139
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
 

Author Comment

by:novicedbaUK
ID: 20382299
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 20382351
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
 

Author Comment

by:novicedbaUK
ID: 20382398
Many thanks. You have been very helpful
0
 
LVL 1

Expert Comment

by:AdamNoffie
ID: 21362384
Hey, thanks for this info.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL HELP 2 93
SQL Server 208R2 not recognizing DBF file in linked Server 11 60
Divide by zero error encountered. 2 42
Grid querry results 41 80
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.
In this article I will describe the Copy Database Wizard 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.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

809 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