Audit Configuration Changes

Do you know the last time a Server Property was changed on your instances of SQL Server?

Are you wondering when the setting for max degree of parallelism was changed?

Do you know who changed the setting?

In some environments there are a lot of hands trying to help mix the pot.  Sometimes more hands can make light work.  This is not always the case though.  More hands in the mix can be a troublesome thing.  Especially when things start changing and the finger pointing starts but nobody really knows for sure who made the change or when the change was made.

I know, that is why there is this concept called change management.  If you make a change to a setting, it should be documented, approved and communicated.  Unfortunately the process does not always dictate the work flow and it may be circumvented.  This is why auditing is a tool that is in place and should be in place – like it or not.

Auditing can be a very good tool.  Like many things – too much of a good thing is not a good thing.  Too much auditing can be more of a hindrance than help.  You don’t want to cause interference by auditing too many things.  You also don’t want too much data that the facts get blurred.  I hope that this script strikes more of a balance with just the right amount of data being captured to be of use.

The basic principle to auditing server configs is to find what values changes, when they were changed and by whom.  There are ancillary details that can be helpful in tracking the source of the change such as the hostname of the source computer making the change.  These are all things that we should capture.  But if a setting hasn’t changed – then we need not necessarily report that the setting was unchanged (it should go without saying).

So for this, I created a couple of tables and then a script that I can add to a job to run on a regular basis.  I can put the script in a stored procedure should I desire.  I’ll leave that to you as an exercise to perform.

[codesyntax lang=”tsql”]


Here I am trapping the config settings on a daily basis (as the script is written for now). I then cross reference the current settings against the previous settings.  Then I check the default trace to see if any of those settings exist in the trace file.

The default trace captures the data related to configuration changes.  On busy systems, it is still possible for these settings to roll out of the trace files.  For those systems, we may need to make some alterations to the script (such as running more frequently and changing the comparisons to account for smaller time intervals than a day break).

To ensure proper comparison between the audit tables and the trace file, note the substring function employed.  I can capture the configuration name and then join to the audit tables on configuration name.

This has proven useful to me so far in tracking who did what to which setting and when they did it.

I hope you will find good use for it as well.

Last Time CheckDB was Run

Categories: Corner, News, Professional, SSC
Comments: 9 Comments
Published on: January 17, 2013

Corrupt PagesBack in November 2012, I shared a story about checking the last known good checkdb in the boot page of a database.  You can read that story here.

This is an important enough topic that it is worth repeating frequently if I wanted to do that.  If  for no other reason than to continue to hammer at how important it is to both run checkdb and know the last time that checkdb was run successfully.

Alas, I am writing to fix a few things with the script that I shared in that last past.

I run this script on every server I touch to get a report for the last known good checkdb for every single database.  I had been running the script flawlessly across many servers without error.  Then it happened.  The script failed with a nasty error.

After a bit of looking, it became apparent my flaw in the script.  I had not written the script with CS (case sensitivity) in mind.  I touch so few CS servers, that I sometimes forget to check for that.  Slap my hands and I will work on that going forward.

So here is the update to the script.

[codesyntax lang=”tsql”]


T-SQL Tuesday #38 Recap

Comments: No Comments
Published on: January 15, 2013

This is the wrap up for TSQL Tuesday #38.  We had a good turnout this month with some new faces and with some regulars.  In total, we had 22 entries.  All of the entries this month were very good.

The topic was on “Standing Firm” and can be found by clicking the image to the left.

Stay tuned to twitter for future TSQL Tuesday announcements and news via the #tsql2sday hash tag.


Rob Farley (blog | twitter) – Running Goals: In his opening paragraph Rob says “Peer pressure can be useful at times, but I also find that it can make me even more stubborn.”  WOW!  I can really relate to that.  Then he proceeds to recount a story from his past that would be hard for a lot of people to overcome.  I know I would have a hard time with not being able to lift my children.  Now Rob is running and plans to keep going and improving.  Excellent story!

Jim McLeod (blog | twitter) – Environment: Jim also decided to write about fitness and exercise, specifically cycling.  Jim ties planning into his goal to cycle more and stick with it.  Then he ties that into SQL Server.  “Put together an environment that supports and encourages you to stick to your resolution.”  Plan ahead whether it be with certification or cycling, and not just for the happy times but for the rough times as well.

Thomas Stringer (blog | twitter) – Lucky 13:  Like many cross country runners, Thomas wants to hit it hard.  Then he wants to maintain the pace.  The principle is to set out to do something and build up a routine.  Routine becomes habit – and eventually becomes easier.  Thomas is setting out to learn something new every day.  That is a good goal.

Robert Davis (blog | twitter) – Disaster Recovery Resolutions:  This month Robert happens to be providing a month long series on Disaster Recovery.  In keeping with that theme, he has offered up several resolutions every DBA should make.

Koen Verbeeck (blog | twitter) – Resolving an SSIS Performance Problem:  Koen reached Defcon 2 (not really – but you should read it) when presented an opportunity to troubleshoot performance problems in an SSIS package.  The problem seems pretty typical – package works and then starts slowing down over time.  Part of the reason for that is due to four bullet points that Koen discusses.

John Sansom (blog | twitter) – Take More Ownership:  John hits on a big button in his contribution this month.  There are problems within our database environments.  Often times these problems are allowed to coexist.  While they cost time and money and raise frustration levels, how many times do we step up and offer a solution to the real problem?  It’s a good story – check it out.

Matt Velic (blog | twitter) – Stuck:  Matt is a thinker and he was stuck in a rut of late.  Matt enjoys thinking about decisions – a lot.  This was contributing to him being stuck.  He reveals some quick tips about how to get unstuck and to enjoy life just a bit more.

Steve Jones (blog | twitter) – Resolute:  Many people have a hard time standing up for what they want or believe.  They have a hard time saying “no.”  We sometimes joke about DBAs being a stop in the flow in work because we say “no” too much.  I think we probably do not say “no” enough.  And Steve points out plenty of ways that we need to learn to say no more often.  It is all about life balance.  How many times do you say “no” to a new project when you already have a full plate?

Julie Koesmarno (blog | twitter) – Tribute to Mum:  Julie, like her mother, is a WIT/Engineering.  We learn   about some of the personal life of Julie as she gives credit to her mother for being a role model as she grew up.  Now, Julie wants to help be an inspiration to others as her mother was for her.

Chris Fradenburg (blog | twitter) – Avoiding the Repetitive Mess of a Disaster:  Chris is the first of the first timers that participated this month (we had a few – woohoo).  Besides the bottle of gel soap that must be used every time he washes his hands, he is trying to improve his environment by reducing the manual repetitive tasks.  This is a good story about a disaster encountered on the first day on the new job.

Wayne Sheffield (blog | twitter) – Learning:  I did a fair bit of arm twisting to get Wayne to participate this month.  He was having a bit of writers block.  Then a topic fell into his lap and it should provide a month of writing for him.  Wayne discusses how is looking to learn and also provide a series of articles on PoSH to help others learn.  It should be out soon!

Alan Dykes (blog | twitter) – Solid Skills:  Alan is pretty much a self taught SQL Developer.  He has recently resolved to sharpen his skillset.  He learned from reading another post about how performance can vary by using a different tsql solution (e.g. NOT EXISTS versus LEFT OUTER JOINS).

Robert Pearl (aka Bobby Tables) (blog | twitter) – HealthySQL:  Some more arm twisting on this one.  Bobby has an excellent idea and it is something we should resolve to do every year as Data professionals.  Too often we get into the break/fix mode rather than the preemptive strike mode.  Regular maintenance (like you should do with your car) keeps things running longer and with less overall cost.

Hemanth.D (blog | twitter) – Firmness of Purpose:  Hemanth is the second of our first timers to participate this month.  Hemanth goes back and explores the past a little and how community ties in with the #SQLFamily.  Hemanth wants to be more resolute in the upcoming year when faced with an issue and ensuring the issue is resolved.

Mickey Stuewe (blog | twitter) – Spoons:  Mickey introduces us first to Neo and his effort at bending a spoon.  Then she correlates that to her writing ability.  For her to write is like trying to bend a spoon with her mind.  So she uses other tools to accomplish the goal.  She shared a couple of tools of which I think I will try the mind mapping tool.  That might mean some bizarre maps though.

Ed Watson (blog | twitter) – Speaking of Resolve:  Another first timer to the party (iirc), Ed shares his resolution to start speaking more at the local level.  Ed was more prone to flight than fight when it came to public speaking.  He talks about his experience and what he did in 2012 to start speaking publicly.

Jeffrey Verheul (blog | twitter) – Standing Firm:  Jeff had already planned out his goals by the time TSQL Tuesday rolled around.  After he had written some goals, he ran into a rough patch.  Based on that week, he has chosen to stand more firm and improve all that much more.

Ana Mihalj (blog | twitter) – Getting Involved:  Another warm welcome to another first timer.  Ana is from Bosnia and Herzegovina and has been working on blogging and getting involved in the community for the past year and a half.  Now she wants to step up her efforts.

Ken Watson (blog | twitter) – Not an Average Accidental DBA:  Ken shares a story on how he transitioned from a jack of all trades to being a DBA.  Ken is not enamored with being an average DBA and is working at something about which he is passionate.  He chased a dream and it is working out for him.

Tim Ford (blog | twitter) – Bring out your Dead:  You’ll get it once you read his post.  Tim is bringing back #Learn365.  If there were two themes this month it was exercise and learning.  Learn365 is right up that alley with learning.  Go check it out.

Chris Yates (blog | twitter) – Hoops and Databases:  We have a baller among us.  Chris talks about the resolve he had as a High School athlete to be a better ball player and make it to the Collegiate game.  That same resolve is applicable to being an Exceptional DBA.  Practice your craft and stand firm in your determination to be an Exceptional DBA.

My entry (twitter) – A Firm Foundation:  I shared a story that relates to disaster recovery and database consistency.  In that article, I also explored the immediate impact of changing the page verify for your database (plan cache).


January S3OLV 2013

Tags: , ,
Comments: No Comments
Published on: January 9, 2013

I hope you are ready for some free SQL learning.  Coming up on Thursday January 10th at 6:30 PM Pacific (GMT – 8 ) we will have a stellar opportunity to do just that – Learn!

Edwin Sarmiento will be presenting for us this at the upcoming monthly Las Vegas User Group meeting.  Edwin has chosen (well he left it up to me to decide) to present on Powershell.

Title: Windows PowerShell for the SQL Server DBA


Windows PowerShell is becoming the scripting language of choice for managing Microsoft servers and workstations. And while T-SQL is still the scripting language for DBAs to administer and manage SQL Server, there are tons of stuff that can be easily done with Windows PowerShell. In this session, learn Windows PowerShell from the ground up and how you can use it with SQL Server Management Objects (SMO) to administer and manage a SQL Server instance.

Here is the bonus behind all of this – we will be broadcasting the presentation to the virtual sphere.Here are the details for joining the meeting via livemeeting.

Copy this address and paste it into your web browser:


Copy and paste the required information:

  • Meeting ID: JRW8JQ
  • Location:


Fast Starts

Categories: News, Professional, SSC
Comments: 1 Comment
Published on: January 8, 2013

We are barely under way with the New Year and it is shaping up to be a busier year already.  Not quite as busy as has been published by the folks at Confio – but busy nonetheless.

The first thing up for me is to get my SQL Learn on.  I will be joining a bunch of geeks and some of their families for a week at sea.  Yes, you heard that correctly – at sea.

I will be SQLCruising out of Miami through the Caribbean.  This should be a good learning/relaxing SQLcation.  Yes Tim, I took a little liberty to add the water ripple to that logo.  Just getting excited to be on the water and rippling water helps.  It’s almost time to go pack for this trip.

Right after I get back from Cruising, I will be in Albuquerque for SQL Saturday 183.  I will be joining people like Steve Jones, Aaron Bertrand, Tjay Belt, Denny Cherry, Ben Miller and a host of other talented people.  You can see the schedule here.

Here is what I am thinking about attending.

Time Presenter Topic
8:30 AM Jeff Renz Data Vault Data Warehouse Architecture
10:00 AM Carlos Bossy Using Columnstore Indexes in SQL Server 2012
11:15 AM I will be presenting
1:45 PM Rob Mandeville Virtualizing our Environment
3:00 PM Michael Fal Exposing the Core: SQL 2012 on Server Core
4:15 PM Chris Shaw Outages: Dispatchers, Cops and Detectives

That brings us to early February.  At some point I expect to retake the Lab exam in February or March.  And then I expect to be presenting at a couple more SQL Saturdays within the next few months.

A Firm Foundation

Last week I sent out an invite for the monthly TSQL Tuesday party.

The theme for the party is a take on the words resolve or resolution.  I was hoping the theme would encourage some reflection and sharing of real life experiences that have led to a difference being made.

I have resolved on two stories to share.  Both are rather short and simple in nature.


This arch (in Arches National Park, Ut.) has stood RESOLUTE for milennia

Story the First

Near the end of the year in 2012, I inherited a database that had not had a consistency check done on it – ever!  In checking the page_verify setting, I found that it was set to none as well.  Both of these should be alarming to any DBA – unless you are completely unconcerned by corrupt data and the potential for corrupt data.  Never-mind the potential business repercussions of having corrupt or lost data.

To find what level of page verification you have enabled, it is a matter of a quick script like the following.

[codesyntax lang=”tsql”]


You can have any one of three settings for your page_verify.  The recommended option is to have CHECKSUM enabled.  If you see NONE or TORN_PAGE_DETECTION, you really need to consider changing that.  Keep in mind if you are still running SQL 2000, CHECKSUM is not an option and the query provided will fail.

Changing the verify option is very simple as well.  It only requires an Alter Database to be run such as the following.

[codesyntax lang=”tsql”]


You will probably notice that I am using the msdb in my sample script.  There is a reason for this that will be shown later.  Just keep in mind that msdb should not need to be changed because it should already be using the CHECKSUM option.

What if you have numerous databases that are not using the CHECKSUM method?  It can become rather tedious to change each of those manually.  That is why we might come up with a cursor such as the following.

[codesyntax lang=”tsql”]


This script is only checking for databases that are not using CHECKSUM.  Then it loops through and changes the setting to use CHECKSUM.

I strongly caution about running this in production without an outage window!  I make that recommendation for very simple reasons.  First, the change is to a production system.  Second, the change can have a temporary adverse effect.  Now before you get too excited about it, I have a short demonstration.

Here is a script broken out into three sections.

[codesyntax lang=”tsql”]


[codesyntax lang=”tsql”]


[codesyntax lang=”tsql”]


Sections one and three are the same.  This script is used to measure various memory components within SQL Server.  The second section is the change we will make to the msdb database.  The queries in the first and third section perform the following: retrieve memory clerk usage (aggregated to memory clerk type), retrieve total data pages stored in cache (aggregated by database), and retrieve the plan cache use (aggregated by database).

Now on to some pre and post change results.  First with what my results were prior to the change.

Memory Clerk Usage
Memory Clerk Type SPA Mem, Kb


Pages in Cache
Database Name Cached Size (MB)
msdb 12.265625


Plan Cache
DbName dbid TotalPlanCacheSize_in_MB
NULL 32767 42
msdb 4 13
ReportServer$ADMIN 5 0
MDW 28 8
AdminDB 14 0

And the following are the post change results.

Memory Clerk Usage
Memory Clerk Type SPA Mem, Kb


Data Pages in Cache
Database Name Cached Size (MB)
msdb 12.265625


Plan Cache
DbName dbid TotalPlanCacheSize_in_MB
NULL 32767 36
ReportServer$ADMIN 5 0
MDW 28 8
AdminDB 14 0

First observation I want to point out is with the second result for both the pre and post run.  Making this change will not affect the pages in cache.  This goes along with what we have been taught by Paul Randal – that a CHECKSUM is not performed immediately (I paraphrased).  You can read more about the CHECKSUM and some misconceptions about it here.

If we now turn our attention to the first and third result sets, we will see that there are changes in the memory clerks used and the plan cache.  Starting with the the third result set (both pre and post) we see that the ResourceDB decreased in total plan cache size.  The NULL item (adhoc queries not associated to a specific database) also decreased.  After that, the only change in size is the msdb database – disappeared from the results due to no plan cache in use associated to this database.  (Starting to see why I chose the msdb database for this demo?)

If you now look closer at the results for the first query on both sides of the change, you will see correlating changes to the plan cache.  Notice that CACHESTORE_SQLCP dropped by about 46MB (correlates to the null entry from query 3).  But of those clerks listed, you will see that only USERSTORE_DBMETADATA did not change in size.

Looking at these results should demonstrate why this change should be performed during a maintenance window.  There will be an effect on performance and I would rather you let the business know what is coming down the pipe.  This change is akin to running DBCC FLUSHPROCINDB(<db_id>);.  There are other database settings that will have the same effect.  You can read a little about that from Kalen Delaney – here.

Story the Second

This story is far less interesting and a whole lot shorter.  This falls into the category of professional development and fine tuning my skills.  I took the MCM lab exam during the PASS Summit.  I failed, not unlike many who have attempted it.  That is all fine and well. I learned some things about myself and I learned some areas that may need some resolution (sharpened focus).

So as more of a resolution upon which I have greater resolve than a New Years resolution, I will be retaking the Lab exam.  And I will be getting my MCM in the near future.  Just sayin’!

SSRS Subscriptions Report

As a part of my series leading up to Christmas 2012, I shared a script to Report on SSRS Subscriptions.  It was soon found to have a bug with SQL Server 2008 R2 SP2.  IN the comments on that post, I promised to post an updated script.  Here is that update – without the bug.


The inline code seen above likes to reformat and and will throw an error due to capitalization of the function value and text().  Download the script here: SSRS_SubscriptionsV1_5

T-SQL Tuesday #38 – Standing Firm

Comments: 29 Comments
Published on: January 2, 2013


Welcome back for the 38th installment in the wildly popular blog party for the SQL Server community.  This is the party that happens on the second Tuesday of each month.  The party was started by Adam Machanic (B|T) just over three years ago.

Each month a new host selects a theme and announces it about a week in advance.  And this month I will be hosting.


To kick off the new year (2013), we must first adhere to a little tradition.  This is not a T-SQL Tuesday tradition.  It is more of an annual tradition for all to welcome the new year.  Feel free to click the link and sing along!!

Now for the theme.  A common thing for many people to do this time of year is to do a little self reflection.  Some set meaningful goals for themselves.  Fewer actually accomplish those goals or even follow-up after initially setting the goal.

We are not going to set goals as a part of this T-SQL Tuesday – unless you want to.  I want to take a little different spin on the New Year’s “resolution” tradition.  So the theme this month is “Standing Firm.”

The idea for this theme is to start with a little self reflection.  Then to come up with a story relating to one of these words: resolve, resolution, or resolute.  Here are some examples of how these stories may be portrayed.

  • Resolve:  A system outage occurred and you “resolved” it.
  • Resolute:  You made an executive decision and did not waver from it.
  • Resolution:  You discovered a bug and documented a work-a-round resolution for it.
  • Resolution:  You have discovered certain T-SQL skills are fuzzy and want to sharpen your ability in that area.
  • Resolute:  You are determined to improve performance in your application.

All of these words are very closely related.  It is up to you to determine how you would like to apply them to your T-SQL world.  Your experiences and stories can be loosely or tightly coupled to T-SQL, it is up to you.

And since the theme requires a little bit of self-reflection first, bonus kudos to those that can tie a past experience to a future plan.

The Rules

I know, parties are not supposed to have rules.  Sadly, all parties have some rules – you just may not know them.  These rules are very simple.

  1. Your post must go live between 00:00:00 GMT on Tuesday the 8th of January 2013 and 00:00:00 GMT on Wednesday the 9th.
  2. Your post has to link back to the hosting blog, and the link must be anchored from the T-SQL Tuesday LOGO (found above) which must also appear at the top of the post.
  3. Trackbacks should work. However, all comments and trackbacks are moderated, so give me a few minutes. If you think you waited long enough and still don’t see yours, leave a comment below.
  4. Tweet your post to the #TSQL2sDay hash tag.
  5. Have Fun writing and participating.

Follow these rules, and your post will be included in the roundup that will be posted on the 15th or 16th of January.

page 1 of 1

January 2013
« Dec   Feb »

Welcome , today is Tuesday, April 7, 2020