Solved

SQL 2005 SERVER LOGS

Posted on 2007-11-30
10
3,416 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

740 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