Well the new year is here and I’m currently planning what’s next for SQL University this semester. I’m hoping to have a few more additions in staff as well as add some more interactive options to get everyone involved! I’ll have more details in the coming weeks about that. In the meantime I have setup a feedback form for SQL University. If you can, please take a minute to fill out this form. This feedback lets me know what we’re doing wrong/right and what you would like to see in the future. Thank you!
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:
Message
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.
This post is actually going to be a copy/paste job from a comment I left on Colin Stasiuk’s (Twitter | Blog) post encouraging his user group, EDMPASS (Edmonton chapter of SQL PASS user group), to jump on the Twitter bandwagon before their next meeting for hockey tickets. In his post Colin asks his members to join Twitter, if they haven’t already, follow at least 10 SQL Tweeps, and share a story of a positive experience they had with fellow SQL Tweeps. In the past year I’ve come to find that Twitter has become an invaluable tool for me professionally as I can get many quick answers and sometimes (as you’ll see below) even more complicated issues resolved. Without further ado here’s my tale:
I’m really not eligible but I’ll share a story that I used during my SQL+Twitter talk at SQLSaturday (and also whenever I tell anyone about the value of Twitter).
For those who don’t know Paul Randal (@paulrandal on Twitter), I liken him as such: If the SQL World were the Star Wars Universe he and Kimberly Tripp (@kimberlyLTripp on Twitter) would sit on the Jedi Council. That being said they’re pretty important in the SQL world.
One fine day I got a call from a coworker whose server had run out of space because the transaction log file had grown too much (whoops). Rather than call me to fix the problem he did the thing all DBAs dread…he deleted the .LDF file and thought restarting SQL would rebuild a new one. Needless to say this pained me deeply. I tweeted my plight to the world (as I do whenever I’m working on something interesting) and Paul responded. Now the reason this was so exciting to me is for a few reasons: 1-This guy WROTE the code for DBCC CHECKDB and knows every nook and cranny of how the database engine works and how/why corruptions happen and how to fix them. If you’re interested in the technical bits of the story check out Paul’s blog post on it: http://www.sqlskills.com/BLOGS/PAUL/post/A-sad-tale-of-mis-steps-and-corruption-(from-today).aspx
Long story short, thanks to the relationships I’ve fostered with fellow SQL professionals I got world-class, one-on-one help (he and I emailed back/forth that afternoon while he walked me through what to look for, commands to run, etc.) and all for free. Simply put I cannot stress enough how much Twitter has enhanced my worklife and helped me to be a better DBA as now I have the power of so many sql resources at my fingertips almost instantly. Outside of SQL I also had another great experience the other day with Shay Levy (@shaylevy), Powershell guru extraordinaire. I don’t know PS well yet and he connected with me via Google voice chat and he helped review the script I was writing with me. Oh yeah, did I mention that Shay lives in Israel?!? Yeah, international help FTW!!!!
Lastly how can I forget my connection with you Colin? Another international connection forged from the fires of Twitter. Now thanks to our connecting via Twitter you and I are collaborating with yet another SQL Tweep on writing a Policy Based Management book. I really should just copy/paste this on to my blog and make it a post as I’ve apparently rambled on enough for this.
So there you have it kids! Do you have a positive story from Twitter you’d like to share? I’d love to hear about it so leave your comments/stories below.
Brent Ozar (@Brento) and Aaron Nelson (@sqlvariant) were talking discussing on Twitter the creation of dedicated hashtag for those who need help with SQL Server issues. As Brent is known for doing, he magically whipped up a blog post on the matter explaining how this new hashtag, #SQLHELP, should be used. I highly recommend you head over to Brent’s blog and read up on how to use #SQLHelp. I’ve found Twitter to be an invaluable tool for getting quick responses to any SQL (and non-SQL as well) questions I have. If anything this should be a great hashtag to search on and show to management as a good business-value for Twitter in the workplace!
![Reblog this post [with Zemanta]](https://i0.wp.com/img.zemanta.com/reblog_e.png?w=580)
