This is just a quick post to serve as a warning sign to those who are DBAs in shops where you routinely get vendor pre-packaged databases (which should encompass about 99% of you). Today we had a vendor installing a new application which required a SQL database, to which I am ever thankful. Unfortunately this database, as I came to discover after a discussion with my junior DBA, that it was actually a restore of a database that was a created after mashing a bunch of data together from disparate systems. Ouch.
Well long story short after the install I took a look at the database server’s (disclaimer: I put them on a shared SQL instance since the app is relatively small) error log to make sure nothing crazy was happening since during the install they had issues with account creations. FYI that turned out to be a small “we need sysadmin” rights issue to which I said ‘NAY!’ and granted them temp rights to complete their install and then revoked those rights when it was complete. Anyhow the error log showed me something interesting:
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
Well that’s interesting. I quickly Banged it out and the first solution I found was a KB article (KB917828) that said these were “normal” messages you’d see in a database restored starting from SQL 2005 SP2 and upwards. I read on as I needed to know WHY these messages appeared and BAM, something hit me and sent off alarms:
The whole procedure cache is cleared when certain database level operations are performed in the following scenarios:
- A database has the AUTO_CLOSE database option set to ON. When no user connection references or uses the database, the background task tries to close and shut down the database automatically.
Auto_close enabled on a database on my server? NOT ON MY WATCH! I quickly jumped over the database options and sure enough not only was AUTO_CLOSE enabled but AUTO_SHRINK as well. For those of you curious as to why I’d freak out about this, these options are really resource intensive and this being a shared instance I really don’t need that hassle. If you’d like to read about how/why these two options are bad check out Tim Ford’s blog at http://www.ford-it.com (aka @SQLAgentman on Twitter) and read his post on Dealing with SQL Shrinkage.
So remember kids: check, re-check and double-check what your vendor is doing when they setup databases on your systems. Ideally they should provide scripts for you to be able to validate and evaluate before you just “slap it in” to your production environment.