Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
WindChaser
Posting Yak Master
225 Posts |
 Posted - 2009-10-18 : 23:19:43
|
| Hi folks.We've noticed something strange happening at some of our client sites which are running SQLServer 2005 Express. Every couple of seconds, an event ID 17137 "Starting up database" is logged under the application log for a given database. Then, every 5 minutes, a few event IDs 2803 occur as well: "SQL Server has encountered 5 occurences of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations." However, there is no maintenance plan. This does not seem to occur at sites running SQL Server 2005 STD even though the exact same application setup is used.Is this normal and is it necessary? This is generating an awful lot of events and likely using up disk space (then again maybe not...).Thanks! |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-10-19 : 04:07:42
|
| You've got a database with Auto_close set to true. When auto_close is on for a database, SQL closes the DB when the last connection closes. This forces a cache flush. Next time someone accesses the DB, it's reopened, causing the 'starting up database' message.I believe that auto_close is default on for DBs created in SQL express.--Gail ShawSQL Server MVP |
 |
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2009-10-19 : 08:10:51
|
| Thanks Gail! Really appreciate your help!I did a bit of research regarding how to turn off the option. However, I only found references to SQL-DMO to do this. My issue is that the SQL-DMO libraries are not installed on the client systems so I'd rather not go in that direction. Does anyone know of an alternate way of affecting the options? I couldn't find a SP.Thanks! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-10-19 : 10:13:51
|
| ALTER DATABASE <Database Name> SET AUTO_CLOSE OFFYou have a query tool? SQL Server Management Studio Express is free (if you don't) and designed to work with the express engine--Gail ShawSQL Server MVP |
|
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2009-10-19 : 10:34:05
|
| Thanks again Gail.Doing the work manually through SSMSE would not be feasible because we have hundreds of clients. The idea is to create an update program which will do the work with a simple double-click of a button. Thank you so much for your help! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-10-19 : 12:28:37
|
| You should be able to run that command from a .net app. Should be just a SQLCommand object and executeNonQuery (though I'm not a .net developer)Just make sure it has sufficient permissions--Gail ShawSQL Server MVP |
|
|
|
|
|
|
Event ID: 2803 and 17137 constantly occuring