The Long and Winding Road

It is time for another installment in the monthly blog party for SQL Server professionals known as TSQL Tuesday.

This month we have the pleasure of being hosted by Wendy Pastrick (blog | twitter).  The topic for the month requires a bit of introspection (almost like the self-evaluation piece of an annual review).  Quoting direct from her blog, here is the gist of the topic:

Here’s what I thought it would be fun to share with the community this time around – we all experience change in our work lives. Maybe you have a new job, or a new role at your company. Maybe you’re just getting started and you have a road map to success in mind. Whatever it is, please share it next week, Tuesday May 14th. Make sure you note what technologies you find are key to your interests or successes, and maybe you will inspire someone to look down a road less traveled.

For me, this is an interesting topic.  It was my theme of choice last month with a major announcement (see here).  And because of that, I am even using the same image – slightly changed.  Only this time, I will go back a bit further into my career and the road I traveled to get to today.

I am going to go back to a decision point in my career that had a huge impact on where I am now.  That decision point was shortly after having moved to Las Vegas about four years ago.  After having moved to Las Vegas, I made the decision to become more active in the SQL Community.  The first step was to regularly attend the user group meetings.

Prior to moving to Las Vegas, I was a member of PASS.  I had been to SUMMIT.  I knew of the local user group meetings in the Salt Lake City area.  I just never forced the issue due to timing etc.  This was something that I felt needed to change.

By making that conscientious decision, I became more involved in the online community. I soon started presenting.  And before long, I was involved in the scheduling of speakers for the Las Vegas UG.

By becoming more active in the community, my skillset started to rapidly grow.  I found myself blogging more and researching more about SQL Server.  I really started to learn about SQL thanks to that decision.  Prior, I feel I was good.  Now, I feel I am much better because I invested more time and effort and I am trying to share the skills that I have learned.

I have said it before and it is worth saying again.  If you really want to learn a technology, try teaching it to somebody.  By taking on that extra step, you will find yourself researching a bit more and you will find that you may have to answer questions about it that you had never considered until you tried to teach it.  Being active in the community has helped me to become better at my trade.  I am sure it will help others as well.

With that scent in the air, we have a nice juicy topic coming up this month for any and all that are interested.

Chad Crawford will be presenting to the group on the tastiness that is Service Broker.

Service Broker in Action

SQL Server Service Broker is a messaging framework built into the SQL Server engine. It enables SQL Server to handle messaging between servers and applications with light setup and overhead. The flexibility of the framework enables Service Broker to queue event notifications, task execution requests or other messages while leveraging the strength of SQL Server transaction management, reliability and recoverability. In this session we will see how to set up Service Broker, discuss case studies where it has been implemented in industry, and step through a practical example implementing an audit log.

Session Level: Intermediate

Chad Crawford’s BIO

Chad has been working with database engines for 14 years, the last 12 focused specifically on SQL Server. He has filled a variety of roles spanning architecture, development and administration. Chad is currently the Database Architect at Henry Schein Practice Solutions in American Fork. When he isn’t optimizing a query, you will find him running, dreaming about airplanes, or looking for a new strategy board game.

Meeting Details

Attendee URL: https://www.livemeeting.com/cc/UserGroups/join?id=KWRMQ3&role=attend

Meeting ID: KWRMQ3

Today, I am going to discuss some things you may do to get around this problem.

The first thing that may come to mind when you encounter this is “Oh crap!”  Well, no need to get too terribly worried (not unless you really hosed something up and you are trying to fix it real quick before the boss notices).

The next thing you may think of trying is how to circumvent the single_user mode.  And during that thought process you may be thinking that single_user does not really mean single_user so you might try something like start a DAC session.  Well, let’s go through that and see what would happen in a DAC session if your single_user session is stolen.

I am going to skip the part of setting a database into single_user mode because we are presuming that the condition already exists.  To start a DAC session, I am going to point you to a previous article I did on the topic – here.

To ensure I am using a DAC session, I am going to issue the following query.  This will ensure I am in the right session and that DAC is in use.

Code block

SELECT s.group_id,e.name, CAST(g.name AS NVARCHAR(20)) AS ResourceGroup, s.session_id 
		, s.login_time, CAST(s.HOST_NAME AS NVARCHAR(20)) AS HostName 
		, CAST(s.program_name AS NVARCHAR(20)) AS ProgramName 
		,s.original_login_name 
		,s.is_user_process 
		,s.STATUS 
	FROM sys.dm_exec_sessions s 
		INNER JOIN sys.dm_resource_governor_workload_groups g 
			ON g.group_id = s.group_id 
		INNER JOIN sys.dm_exec_connections ec 
			ON s.session_id = ec.session_id 
		LEFT OUTER JOIN sys.endpoints e  
			ON ec.[endpoint_id]=e.[endpoint_id] 
WHERE s.session_id = @@SPID 
ORDER BY g.NAME; 
GO

In my case, this results in an endpoint with the name “Dedicated Admin Connection” and a spid of 84.  Good, I am in the correct session for the rest of this test.  Next, I will issue a Use database statement.  I have created a test database called ClinicDB.  So I will issue the following.

Code block

USE ClinicDB;
GO

I get the following result.

Code block

Msg 924, LEVEL 14, STATE 1, Line 1
DATABASE 'ClinicDB' IS already OPEN and can ONLY have one USER AT a TIME.

So, that blows that idea right out of the water.  It shouldn’t really have been a consideration in the first place because single_user really means just that – single_user.

Now What?

Well, what do you think we could do now to circumvent this little problem and get that single_user session back?

That requires a little investigative work.  It is time to find out who has taken the single_user session and politely ask them to give it up.  To make that task a little easier, we could modify the previous query to find out who has that single_user session (thus limiting how many people we have to ask).  I have modified the following query to use sys.sysprocesses so I could limit the results to the ClinicDB.  This is a limitation of SQL 2008 R2 and older versions.  Getting the database reliably means using sysprocesses.  Despite the database_id being available in other related DMVs, it’s just not that easy.  One would think you could use sys.dm_exec_requests.  But if a request is not active, an entry won’t exist for that session.  This problem is fixed in SQL 2012 since the sys.dm_exec_connections DMV now has the database_id field.  Enough of that birdwalk and on to the query.

Code block

SELECT s.group_id,e.name, CAST(g.name AS NVARCHAR(20)) AS ResourceGroup, s.session_id ,DB_NAME(r.dbid) AS DBName
		, s.login_time, CAST(s.HOST_NAME AS NVARCHAR(20)) AS HostName
		, CAST(s.program_name AS NVARCHAR(20)) AS ProgramName 
		,s.original_login_name 
		,s.is_user_process 
		,s.STATUS 
	FROM sys.dm_exec_sessions s 
		INNER JOIN sys.dm_resource_governor_workload_groups g 
			ON g.group_id = s.group_id 
		INNER JOIN sys.dm_exec_connections ec 
			ON s.session_id = ec.session_id 
		INNER JOIN sys.sysprocesses r
			ON r.spid = s.session_id
		LEFT OUTER JOIN sys.endpoints e  
			ON ec.[endpoint_id]=e.[endpoint_id]
	WHERE DB_NAME(r.dbid) = 'ClinicDB'
ORDER BY g.NAME; 
GO

I chose not to do an entirely new query to simply demonstrate that it was possible with a very small tweak to what has been already used.

Now that you know (in my case I can see that I have a session open with ID = 80 that is connected to that single_user database), I can walk over to the person (knowing his/her login id and computer name) and politely ask them to disconnect.

In the end, this is really an easy thing to resolve.  Sure it may take some people skills – but that doesn’t make the task too terribly difficult.  Next time this happens to you, just remember you can run a quick query to find who has sniped that single_user session.

The first attempt was a little crushing.  A mix of emotions concerning the lab came over me because I felt I knew the technology.  While the results were not desirable – the end effect was desirable and I am glad I did not succeed on that first attempt.

When I took the knowledge exam, I took my time going through the questions and examined the questions.  I think that was a good method for that exam – I was trying to ensure I understood the question and didn’t miss anything that was stated.  Though I took it slowly, that is not an indictment to difficulty for the exam.  I was well prepared and did not want to make any invalid assumptions.

Fast forward a little bit to the first attempt at the lab exam.  I had a plan going into the exam.  I felt confident in my skills.  I felt relaxed with the technology.  Then the exam started and my plan went out the window.  I started making assumptions about the exam that I really should not have done.  I rushed a few scenarios that I should not have rushed because I could have done them better.  In the end, I was my own worst enemy during the exam.  This is not a characteristic of an MCM.  Even when the pressure is on, doing what you know and being methodical is a wonderful asset.

Having failed the first time, I wondered if I was ready to be an MCM.  In retrospect, I was not.  Leading up to the exam, I did feel that I was ready for the exam.  I had read several articles such as the following to try and figure out if I was ready.  In the end, it’s more of a gut check and a leap of faith for some.

Gavin Payne http://gavinpayneuk.com/2012/05/01/knowing-when-youre-ready-to-attempt-to-become-an-mcm-of-sql-server/

Joe Sack http://blogs.msdn.com/b/joesack/archive/2010/11/21/how-do-you-know-if-you-re-ready-for-sql-mcm.aspx

I even perused some of the resource type of articles such as the following.

Nick Haslam http://nhaslam.com/blog/2011/11/19/mcmprep-88-970-sql-mcm-knowledge-exam-sqlmcm/

MCM Blog http://blogs.technet.com/b/themasterblog/archive/tags/sql+server+mcm/

Robert Davis Amazon MCM Reading List http://www.amazon.com/Official-Server-Certified-Master-Readiness/lm/R3RB13PQ7D8TKB

Brent Ozar MCM Articles http://www.brentozar.com/sql-mcm/

In the end, this post by Rob Farley sums it up nicely for me (http://sqlblog.com/blogs/rob_farley/archive/2012/12/23/the-mcm-lab-exam-two-days-later.aspx).  Rob references an article by Tom LaRock and some of what Tom did and didn’t do.  As well as some theory on how to approach the exam at certain stages.  Of all those things, I think the best advice I read as well as I could recommend is to get a study buddy.

It’s not sooo much to have somebody to bounce ideas off of, as it is to have somebody to try and teach.  I worked with Wayne Sheffield as we prepped for our retakes.  The biggest benefit was, as I said, in that I could pick a topic and try to teach Wayne.  He did the same to me.  And then, we could question each other on what-if type questions about our selected topics.  Another benefit is to have a heat-check so to speak.  Having a study buddy can help you from straying too far off into tangents or maybe keeping you from wasting too much time on a topic they might feel you understand exceptionally well.

If you are reading this far, you probably have figured that I have taken the Lab a second time.  I was debating whether to do it this soon due to life and family.  My wife continued to push me to do it (much as Nic Cain experienced here - http://sirsql.net/blog/2012/3/26/achievement-unlockedmcm-sql-server-2008.html).  Without Krista pushing me, I might have delayed even longer.  It was also helpful to have a co-worker and friend pushing me along too (I’m a little competitive).  Thanks again Wayne!  You can read his experiences here.

I received my notification email while driving home from Vegas last week.  Seeing that email pop up from boB Taylor gets your heart racing a little bit – especially after you have failed the lab previously.  I glanced at the email and only need to go to the first word – “CONGRATULATIONS!”

Oh yeah!  Vindication, satisfaction, elation, joy and general happiness set in quickly.  Memories of that old “Wide World of Sports” show from a long time ago spilled in with the words “Agony of Defeat, Thrill of Victory.”

I am glad I took the time to pursue this certification.  This adventure has helped me to learn and grow as a DBA.  I am also grateful to those that helped or pushed in some way or another – whether they knew it or not at the time.  Last but not least, I am thankful for my employer Ntirety.  They understood the importance of this for me and were very supportive in this endeavor.

As one final take away, I was recently shown this link with some interesting questions to help you decide if you feel you are ready to take the exams.

AlwaysOn – Finally, A usable ‘mirror’!

In the past, High Availability and Disaster Recovery (HADR) had many limitations. Clustering and Mirroring are great, but the mirror/secondary database is not very usable since it is not online. Finally, AlwaysOn solves this limitation by merging both multi-node Clustering and mirroring. AlwaysOn also allows the secondary database to remain ONLINE, so we can use it for reporting purposes without resorting to a Snapshot! Come see this lively session with extensive demos of setting up, configuring and testing AlwaysOn. We’ll also test automatic fail-over using a real web app to see how well this feature works.

Jim Murphy

Jim Murphy has been using Microsoft SQL Server since version 6.0 back in the 90′s. He is the President/CEO of SQLWatchmen, LLC., a managed DBA service provider for smaller companies who do not need a full-time DBA. He runs the Austin SQL Server User Group: CACTUSS Central and sits on the board of SQLPOSSE.com. He has been a professional DBA and developer for over 20 years and has run a consulting company full-time for over a decade.

LiveMeeting Information:

Attendee URL:https://www.livemeeting.com/cc/UserGroups/join?id=834ZNP&role=attend 

Meeting ID:834ZNP

Backstory

Each month the SQL community comes together for an important party.  This is the blog party that was the brain child of Adam Machanic (Twitter) known as T-SQL Tuesday.

The party is a very good collaboration among data professionals on a pre-determined topic.  This month, for TSQL Tuesday #40, the topic is on Files and Filegroups.  The host du mois is Jen McCown (Twitter).

This month, I had the luck of encountering something this past week that is right up the alley of this topic.  I love it when sysadmins help create learning opportunities for me (e.g. blog material).

Production Down

I was recently given the following concerning a client server issue:

The log file for database ‘xxx’ is full. Back up the transaction log for the database to free up some log space.

That was followed by a short description stating that the sysadmin had tried to expand the log file and that they also tried to run a full backup.  The output of the full backup was as follows.

BACKUP DATABASE [xxx] To Disk=’blah’ WITH NOFORMAT, NOINIT, NAME = N’blah’, SKIP, REWIND, NOUNLOAD, STATS = 10
” failed with the following error: “The backup of the file or filegroup “sysft_FTS” is not permitted because it is not online.
BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.

Now this makes things more interesting.  The sysadmin at least tried to do a full backup and then handed off when it got too deep.

The client server is a SQL 2005 box.  Fulltext was enabled for the database on that box. And we have seen plenty of issues related to Fulltext in SQL 2005.  Somehow, I feel that none of them really pertained to this opportunity.  From all appearances, there was either a disk issue (no history in the logs but client said there was) or somebody deleted the directory (there was a login at the time the issue started and there was a service restart at that time).  In either case, the folder for the fulltext filegroup was no longer present.  But I am getting a little ahead of myself.

When querying the sys.database_files catalog view, I was able to confirm the directory path that should have been in place for the filegroup and that the filegroup was indeed OFFLINE.  Results and query to follow, with filepaths redacted intentionally.

Code block

SELECT FILE_ID,type_desc,name
		,physical_name --intentionally omitted in result set
		,state_desc
	FROM sys.database_files;

So, indeed I do have a problem with the filegroup and I need to get it back online in order to resume backups and get this database back online and able to perform backups.

Some suggestions out there would be to rebuild the fulltext catalog in order to bring it back online.  Well, the files are no longer present on the filesystem, so this didn’t work too well.

Code block

ALTER FULLTEXT CATALOG [FTS] REBUILD
/*
Full-text catalog 'FTS' is in an unusable state. Drop and re-create this full-text catalog.
*/

The notes in the code block represent the outcome.  And the output makes sense if you ask me.  But when trying to drop and recreate, I ran into some more fun.

Code block

DROP FULLTEXT CATALOG [FTS]
--or
SP_FULLTEXT_DATABASE @ACTION= 'disable'
 
/*
Cannot drop full-text catalog 'FTS' because it contains a full-text index.
*/

Once again, that makes sense.  I had hoped that it would drop everything for me.  So, time to try dropping the indexes and recreating them.  For this, I took screenshots of each index in question.  Then tried to drop them.  Once again – another error.

property fulltextindexsize is not available

Despite that error, the indexes were gone and the catalog dropped.  Since I had disabled FT on the database, I needed to re-enable it in order to recreate the catalog and indexes (I had scripts for the catalog and screenshots for the indexes).

Code block

SP_FULLTEXT_DATABASE @ACTION= 'enable'

Now issuing a rebuild against that catalog works as expected.  Additionally, backups work as expected.  And to confirm that all is well, query sys.database_files once again to see that the filegroup is online.

Between the Lines

I breezed through what got this filegroup back online so database activity could resume.  One thing that I skipped over was a step I took trying to recover without dropping and recreating.  Since the directory was not present, and there was a full backup from the same day that had the filegroup in a working state, I tried to recover the filegroup manually.  Restore the database, copy the folder structure into the appropriate filepath and run an alter database statement.  Since it didn’t work, I am not going into deep details on it.  The short of it is that since the structure disappeared off disk, there was some corruption related to it internally in the database.  That needed fixed and in this case it meant to drop the indexes and catalog in order to recreate it.

This month we will be getting back on schedule with the usual meeting time and place.  We had a slight divergence last month due to that heartsy holiday.

We are excited about some of the upcoming speakers and feedback we have been getting of late.  We are feverishly working to get some presenters on Big Data and Hadoop.  If you are in the market to present on these topics, ping me.

Here are some of the details for the meeting.

Presenting is Jason Kassay

Jason Will be presenting to us on the topic of “The Accidental Programmer.

Most people in the database world have heard of the term, “The Accidental DBA”, those programmers who have to work with databases. How about the other side of the coin? What about DBA’s an Dev’s who have to write code or have to work closely with programmers? This presentation is a best practices guide for working with SQL Server in a .Net environment. You will learn how to recognize when bad code is written that interacts with the database, how to track it down, and most importantly how to fix it. On top of that you will also receive an introduction to object oriented programming concepts such as data abstraction, encapsulation, tier architecture, and class objects so that you can better communicate with your programmers.

BIO

Jason has been a software developer for over 10 years and currently works at EZFacility, providing scheduling, management, full-service billing, and membership solutions for the sports, health, and fitness industries. He has worked primarily with .Net (C# and VB) and SQL Server as well as HTML, CSS, and Javascript. When he is not coding or spending time with his awesome family, you will find him either running (to train for the zombie apocalypse) or stopping hockey pucks as a goaltender.

LiveMeeting Information:

VERY IMPORTANT

The meeting location has changed.  We will no longer be meeting at The Learning Center.  New meeting location is M Staff Solutions & Training / 2620 Regatta Drive Suite 102 Las Vegas, NV 89128.
We are also moving to meetup for meeting management. Please join us for free at http://www.meetup.com/SQL-Server-Society-of-Las-Vegas/

It also happens that this week we have a group meeting for the Las Vegas User Group. We normally meet on the second Thursday of each month.  It just so happens that this month that lands on the 14th of February.  If you can’t fathom why we changed our meeting date, maybe you should Google the date or something (just joking).

This month we have a first time presenter.  I have been bugging her for months to try to get her to come out of her shell to present.  And now she is doing it so be gentle and check out the meeting.

Presenting this month is Terrie White.  She will be presenting on Replication Technologies and High Availability solutions.

Here are some of the details for the meeting.

Presenting is Terrie White

Terrie White Will be presenting to us on replication technologies and high availability.

LiveMeeting Information:

VERY IMPORTANT

The meeting location has changed.  We will no longer be meeting at The Learning Center.  New meeting location is M Staff Solutions & Training / 2620 Regatta Drive Suite 102 Las Vegas, NV 89128.
We are also moving to meetup for meeting management. Please join us for free at http://www.meetup.com/SQL-Server-Society-of-Las-Vegas/

You decide to do a little investigation and find that you have your recovery model set to full and you are performing full backups.  That should cover it right?  No, that is not right!

What is this?  Now you have to do something more?  Yes, that is correct.  Performing full backups is not always enough to recover your critical data.  You need to know the recovery requirements for the database / server in question.  But since you have the recovery model set to full, let’s just talk about what else you should be doing.

The first step should be to run a quick script to determine what databases you have in full recovery and which of those databases do not have a LOG backup.  You see, when a database is in full recovery, you should also backup your transaction log on a regular schedule too.  That schedule is to be determined as a part of the second step (and I will only talk about the first two and only briefly about the second step).

In that first step, you can query your msdb database to help generate a report of which databases have had a transaction log backup.  That should be easy enough to do.  Despite the ease, it should not lessen the importance by any degree.  Here is the script that I wrote recently to help determine which databases were in need of a log backup.

Code block

SELECT T1.Name AS DatabaseName
		,ISNULL(t2.database_name, 'No Backup Taken') AS LogBackupAvail
		,T1.recovery_model_desc
		,'Log' AS BackupType 
		,ISNULL(CONVERT(VARCHAR(23), CONVERT(DATETIME, MAX(T2.backup_finish_date), 131))
				, CASE WHEN T1.recovery_model_desc = 'Simple'
						THEN 'N/A'
						ELSE 'Backup Not Taken'
						END
		) AS LastBackUpTaken
	FROM sys.databases T1
		LEFT OUTER JOIN msdb.dbo.backupset T2
			ON t1.name = t2.database_name
			AND t2.type = 'L'
	GROUP BY T1.Name,T2.database_name,T1.recovery_model_desc

You will likely notice that I am querying both sys.databases out of the master database as well as dbo.backupset out of msdb.  Look more closely and you will see the employ of a Left Outer Join with two conditions on the Join.  In this case, both conditions are required to produce the Outer Join effect that I was seeking.  Had I used a script like the following:

Code block

SELECT T1.Name AS DatabaseName
		,ISNULL(t2.database_name, 'No Backup Taken') AS LogBackupAvail
		,T1.recovery_model_desc
		,'Log' AS BackupType 
		,ISNULL(CONVERT(VARCHAR(23), CONVERT(DATETIME, MAX(T2.backup_finish_date), 131))
				, CASE WHEN T1.recovery_model_desc = 'Simple'
						THEN 'N/A'
						ELSE 'Backup Not Taken'
						END
		) AS LastBackUpTaken
	FROM sys.databases T1
		LEFT OUTER JOIN msdb.dbo.backupset T2
			ON t1.name = t2.database_name
		WHERE t2.type = 'L'
	GROUP BY T1.Name,T2.database_name,T1.recovery_model_desc

You would see a considerably different result set.  The reason for this different result set is tied to the predicate used and the Join conditions.  And when one examines the execution plan, the difference becomes a little more evident.

That WHERE predicate converted our Left Outer Join to an Inner Join.  Now, if I had only wanted to return results for databases that had log backups, that might be fine.  I want to report on all databases and find not only the last log backup for a database, but I also want to find if a log backup is missing.  Therefore, I need to ensure that both conditions are declared as part of my Left Outer Join.

Running a query such as this will now provide us with some knowledge as to the database recovery models as well as which databases have had a log backup, have not had a log backup, and which do not need a log backup (based on recovery model).

Armed with this information, it is imperative to do a bit more digging now.  Knowing what the business deems as an acceptable data loss is important.  Find that out and then create transaction log backups accordingly so the business can recover appropriately in the event of a disaster.

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.

Code block

USE AdminDB;
GO
SET NOCOUNT ON;
 
DECLARE @ConfigLastUpdateDate	DATETIME
		,@PreviousPollDate		DATETIME
		,@MaxPollDate			DATETIME
		,@PATH					NVARCHAR(260);
 
SELECT @PATH = REVERSE(SUBSTRING(REVERSE([PATH]), 
						CHARINDEX('\', REVERSE([path])), 260)) + N'LOG.trc'
	FROM sys.traces 
	WHERE is_default = 1;
 
IF OBJECT_ID('tempdb..#DBCCConfig') IS NOT NULL DROP TABLE #DBCCConfig;
	CREATE TABLE #DBCCConfig(
		[ParentObject] VARCHAR (100),
		[Object]       VARCHAR (100),
		[Field]        VARCHAR (100),
		[Value]        VARCHAR (100)); 
 
INSERT INTO #DBCCConfig (ParentObject, Object, Field, Value)
	EXECUTE ('DBCC CONFIG WITH TABLERESULTS');
 
WITH cte AS
(
SELECT cfgupddate = MAX(CASE WHEN t1.Field = 'cfgupddate' THEN t1.Value ELSE NULL END),
        cfgupdtime = MAX(CASE WHEN t1.Field = 'cfgupdtime' THEN t1.Value ELSE NULL END)
FROM #DBCCConfig t1
WHERE   Field IN ('cfgupddate', 'cfgupdtime')
)
SELECT  @ConfigLastUpdateDate = CONVERT(DATETIME,t3.configure_upd_dt)
FROM    cte t1
        CROSS APPLY (SELECT cfgupddate = DATEADD(DAY, CONVERT(INT, t1.cfgupddate), '1900-01-01')) t2
        CROSS APPLY (SELECT configure_upd_dt = DATEADD(ms, CONVERT(INT, t1.cfgupdtime)*3.3, t2.cfgupddate)) t3;
 
IF NOT EXISTS (SELECT Name 
					FROM sys.objects 
					WHERE name = 'SysConfigAudit')
	CREATE TABLE SysConfigAudit (
		configuration_id	int
		,name	NVARCHAR(256)
		,value	sql_variant
		,minimum	sql_variant
		,maximum	sql_variant
		,value_in_use	sql_variant
		,description	NVARCHAR(MAX)
		,is_dynamic	bit
		,is_advanced	BIT
		,PollDate		DATE
		,LastConfigUpdtDate	DATETIME)
 
IF NOT EXISTS (SELECT Name 
					FROM sys.objects 
					WHERE name = 'SysConfigChangeLog')
	CREATE TABLE SysConfigChangeLog (
		configuration_id	int
		,name	NVARCHAR(256)
		,CurrValue	SQL_VARIANT
		,PrevValue	SQL_VARIANT
		,description	NVARCHAR(MAX)
		,PollDate		DATE
		,LastConfigUpdtDate	DATETIME
		,PrevConfigUpdtDate	DATETIME
		,ChangeDate	DATETIME
		,ChangeBy	NVARCHAR(256)
		,HostName	NVARCHAR(256)
		,ChangeSPID	SQL_VARIANT
		,Changedata	NVARCHAR(2000)
		,ApplicationName NVARCHAR(256)
		,Severity INT
		,ERROR SQL_VARIANT
		,ChangeBySessionLogin	NVARCHAR(256))
IF NOT EXISTS (SELECT Name 
					FROM sys.key_constraints 
					WHERE name ='PK_SysConfigChangeLog' 
						AND OBJECT_NAME(parent_object_id) = 'SysConfigChangeLog')
BEGIN
ALTER TABLE dbo.SysConfigChangeLog ADD CONSTRAINT
	PK_SysConfigChangeLog PRIMARY KEY CLUSTERED 
	(
	configuration_id,
	ChangeDate DESC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
		ON Admin_Data
 
END
 
SET @MaxPollDate = ISNULL((SELECT TOP 1 PollDate
								FROM dbo.SysConfigAudit 
								ORDER BY PollDate DESC),'1/1/1900')
 
/* Write the configurations out to audit table 
*/
IF CONVERT(DATE,GETDATE()) <> @MaxPollDate
BEGIN
	INSERT INTO SysConfigAudit
			( configuration_id ,
			  name ,
			  value ,
			  minimum ,
			  maximum ,
			  value_in_use ,
			  description ,
			  is_dynamic ,
			  is_advanced,
			  PollDate,
			  LastConfigUpdtDate
			)
	SELECT configuration_id,name
			,value
			,minimum,maximum
			,value_in_use
			,description
			,is_dynamic,is_advanced
			,GETDATE(),@ConfigLastUpdateDate
		FROM master.sys.configurations;
END
 
/* Recast MaxPollDate */
SET @MaxPollDate = ISNULL((SELECT TOP 1 PollDate
								FROM dbo.SysConfigAudit 
								ORDER BY PollDate DESC),'1/1/1900')
 
SET @PreviousPollDate = ISNULL((SELECT TOP 1 PollDate 
									FROM dbo.SysConfigAudit 
									WHERE Polldate <> @MaxPollDate 
									ORDER BY PollDate DESC),'1/1/1900');
 
/*	A configuration has changed and a reboot has occurred 
	causing the updtdate to be written to the config block of the page 10
	The configuration may not be written to the page, but we will write it to
	the table anyway and then compare to the default trace file in all cases.
	If there are any values changed, then write those to the change log with
	the login of the person who changed the value
*/
 
BEGIN
 
WITH presel AS (
SELECT df.LoginName,df.TextData,df.StartTime,df.HostName,df.Severity,df.DatabaseName,df.SPID,df.ERROR
		,df.SessionLoginName,df.ApplicationName
		,SUBSTRING(df.TextData
					,CHARINDEX('''',df.TextData)+1
					,CHARINDEX('''',df.TextData
								,CHARINDEX('''',df.TextData)+1)-CHARINDEX('''',df.TextData)-1)
				AS ConfigName
		,ROW_NUMBER() OVER (PARTITION BY SUBSTRING(df.TextData
													,CHARINDEX('''',df.TextData)+1
													,CHARINDEX('''',df.TextData
															,CHARINDEX('''',df.TextData)+1)-CHARINDEX('''',df.TextData)-1) 
								ORDER BY df.StartTime DESC) AS RowNum
	FROM ::fn_trace_gettable( @path, DEFAULT )  df
	WHERE 1=1
		AND df.TextData LIKE '%Configuration option%'
		AND df.ApplicationName NOT IN ('SQLAgent - Initial Boot Probe','SQLAgent - Enabling/disabling Agent XPs')
		AND df.Severity IS NOT NULL
)
 
INSERT INTO SysConfigChangeLog (configuration_id,name,CurrValue,PrevValue,description,PollDate	
							,LastConfigUpdtDate	,PrevConfigUpdtDate	,ChangeDate,ChangeBy
							,HostName,ChangeSPID,Changedata,ApplicationName,Severity
							,ERROR,ChangeBySessionLogin)
SELECT CUR.configuration_id,cur.NAME,cur.VALUE AS CurrValue,Prev.VALUE AS PrevValue,cur.description
		,cur.polldate, cur.LastConfigUpdtDate,prev.LastConfigUpdtDate AS PrevConfigUpdtDate
		,df.StartTime AS ChangeDate,df.LoginName AS ChangeBy,df.HostName,df.SPID AS ChangeSPID
		,df.TextData AS ChangeData,df.ApplicationName,df.Severity,df.ERROR
		,df.SessionLoginName AS ChangeBySessionLogin
	FROM AdminDB.dbo.SysConfigAudit CUR
		INNER JOIN AdminDB.dbo.SysConfigAudit Prev
			ON CUR.configuration_id = Prev.configuration_id
			AND CUR.PollDate = @MaxPollDate
			AND Prev.PollDate = @PreviousPollDate
		CROSS APPLY presel  df
	WHERE df.RowNum = 1
		AND df.ConfigName = CUR.NAME
		AND CUR.VALUE <> Prev.VALUE	
		AND NOT EXISTS (SELECT configuration_id
							FROM SysConfigChangeLog
							WHERE Name = Cur.NAME
								AND CurrValue = CUR.VALUE
								AND PrevValue = Prev.VALUE
								AND ChangeDate = df.StartTime
								AND ChangeBy = df.LoginName)
		;
END
 
SELECT *
	FROM dbo.SysConfigChangeLog
	ORDER BY ChangeDate DESC;

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.