Solved

SQL 2005 SERVER LOGS

Posted on 2007-11-30
10
3,414 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Table create permissions on SQL Server 2005 9 42
sql query help 2 53
INSERT DATE FROM STRING COLUMN 18 56
convert null in sql server 12 34
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…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
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…

831 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