Solved

SQL 2005 SERVER LOGS

Posted on 2007-11-30
10
3,412 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
Comment Utility
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
Comment Utility
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
Comment Utility
Auto-Close should not be enabled for any database, any server, ever :)

Glad I could help
0
 

Author Comment

by:novicedbaUK
Comment Utility
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
Comment Utility
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
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.

 
LVL 21

Expert Comment

by:Kevin3NF
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Many thanks. You have been very helpful
0
 
LVL 1

Expert Comment

by:AdamNoffie
Comment Utility
Hey, thanks for this info.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 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

7 Experts available now in Live!

Get 1:1 Help Now