Audit Database File Size Changes

dbsizechange

Recently I shared an article on how to track the growths and shrinks that occur within database files.  I shared that article here, you should read it before proceeding with today’s article.

Near the end of that article I declared that it was a really good method to track that information within SQL Server 2008.  What if you happen to be on SQL 2012 or SQL 2014 (as of this writing)?  Will it work there too?

Does it Work Past 2008?

It is a good question.  Does that extended event session I shared work in SQL Server 2012 or 2014?  Let’s take a quick look.  I am skipping the setup scripts for that XE session in this article, so you will need to get them from the previous article in order to follow along in this segment.

Before we can investigate if this extended event session will work, let’s take a quick look to confirm that the session is running on the server.  We can do that with a query similar to the following.

Running that query will produce results very similar to the following.

SessionCheck

This is good news.  If you noticed, I am querying a couple of views to get this information.  In the server_event_sessions catalog view I can determine if the event exists.  When checking the dm_xe_sessions DMV, I can see if the session is running or not by whether or not the session exists in the view.  When the session is enabled and running, then the DMV will return a record for it. Otherwise, the DMV does not hold a record for the session.

With a session running, we can now validate if it is running properly by running any script that will cause the files to grow or shrink.  Recall that in the previous article, it was shown that any growth or shrink operation will cause an event to fire with this session.  Here is a sample of the query I am running.

That query has three distinct segments.  The first is just to check my file sizes.  The second segment performs my file shrink operations.  And the final segment checks the file sizes again.  Here is what the first and third segments would look like on the Sandbox2 database that I used in the previous article.

filesizes_vold

With the evidence that we have the session running and that there was indeed a file size change, let’s now check the event session data and confirm whether or not the session is working on this SQL 2014 server.

The preceding is the query I am using to query the session data.  Running that query will produce the following results.

nodata

That’s right!  There is no session data despite the event having occurred and despite the session running currently.  We also know that this session works (we demonstrated it on SQL 2008).  So there must be a bug, something is broken.  Right?

What Now?

Since the extended event obviously no longer works, we are stuck with few options.  We could always try resorting back to the default trace.  After all, I demonstrated that the default trace is already trapping information about file shrinks.  That was discussed in the prior article and here as well.

So, what if we tried to go and capture all of the same information from the default trace?  We could certainly try that.  Assuming that the default trace is still running on the server, this query could get us pretty close.

And this does a fairly decent job of getting the info we seek.  Sadly, though, it does not trap all of the necessary information.  Only the DBCC event (event 116) traps the sql statement that triggered the event to be recorded in the default trace.  But for the most part it can be a decent swing at getting the information.  Without the sql statements tied to the event, I’d rather not use it because it really just shows me how many times the size changed, what time the event occurred, and the size of the change.

There has got to be some other way of getting this to work in extended events.  A good question to ask is “Why does the extended event no longer work?”

A little digging, and one might eventually find a document that can shed some light on the problem.  Reading this document, we can see why the event no longer works.  It has been deprecated.  What?  After one release, they decide to take away a critical piece of information?  How can that be?

Time to back up those findings with something a little more authoritative such as this.  Looking at this article, we see that indeed the event was deprecated.  But wait a minute, the event was not just deprecated, it was also replaced with a new event.  We are in business so let’s do some querying within event sessions.

Back in Business

We could have probably spared some time by checking the available events in SQL Server by using this next query.  However, the events used in the previously used event session still exist.  If they did not exist, the session creation would have failed.  This can be a bit misleading, so it is good to have the information from Microsoft that the events have been deprecated and merged into a single event.

This produces the desired results with the new event name specified in that Microsoft article.

2012filesizeevent

 

Based on this information, a rewrite of the extended event session is possible and necessary.  We can update the extended event session that audits when a database file changes in size.  This will look something like the following session.

And since I happened to have that session also running at the same time as the shrinkfiles that were run previously in this article.  So, I can go ahead and check to see if anything was captured.  To check the session data, I will use the following query.

In this new event for 2012 and beyond, there is different data that is captured.  This means that I have access to better information about what is happening to my database files with regards to the size changes (growths and shrinks).

Conclusion

If you just so happen to be running on SQL Server 2012 or later, you will need to change your event sessions that were tracking file changes.  It is a bit of an exercise to make the change and can be frustrating, but it is well worth it.  The improved data that can be captured is going to help better control and oversee the environment.

TSQL Tuesday #60: Something Learned This Way Comes

Comments: 4 Comments
Published on: November 11, 2014

TSQL2sDay150x150It is once again time to come together as a community and talk about a common theme.  This monthly gathering of the community has just reached it’s 5th anniversary.  Yes, that’s right.  We have been doing this for 60 months or five years at this point.  That is pretty cool.

This month Chris Yates (blog | twitter) has taken the helm to lead us in our venture to discuss all the wonderful things that we have learned.  Well, maybe not all the things we have learned, but at least to discuss something we have learned.

Here are some details from the actual invite that you can read here.

Why do we come to events, webinars, sessions, networking? The basic fundamental therein is to learn; community. With that said here is this month’s theme. You have to discuss one thing, few things, or many things on something new you’ve learned recently. It could be from a webinar, event, conference, or colleague. The idea is for seasoned vets to new beginners to name at least one thing; in doing so it might just help one of your fellow SQL friends within the community.

The topic is straight forward but can be a bit difficult at times.  This is a pretty good topic to try and discuss.  I know I have been struggling for content for the topic.  Which makes it that much better because it provides a prime example of how to think about and discuss some pretty important things, while trying to compile that into a recap of one’s personal progress.

Let’s think about the topic for a bit and the timing of the topic.  This comes to us right on the heels of PASS Summit 2014 and in the middle of SQL Intersections in Las Vegas.  We might as well throw in there all of the other things like SQL Saturdays that have been happening leading up to and following those major conferences.

There has been ample opportunity over the past few weeks to learn technical content.  When networking with people there are ample opportunities at these major conferences to also learn about other people and about one’s self.  A good example of that can be seen in a blog post I wrote while attending PASS Summit, which you can read here.

The biggest learning opportunity that evolved from PASS Summit 2014 for me was the constant prodding in various sessions to break out the debugger and become more familiar with what is happening in various cases.  I saw the debugger used in three of the sessions I attended.  There are some great opportunities to learn more about SQL Server by taking some trinket of information from a session and trying to put it to use in your development environment.  This is where learning becomes internalized and gives a deeper understanding.

I hope you have been able to pick up on some tidbit that can be used to your advantage to get a deeper understanding of SQL Server.

Summit 2014 – Next Impressions

Comments: 4 Comments
Published on: November 7, 2014

As Summit 2014 begins to wind down, it is time for some more impressions from the week.  The week has been good so far.  It has been very busy and also can be quite a drain mentally and physically from everything that has transpired.

If you are interested, I have written about some of my other impressions from the week, here.

Several years ago, I blogged about an incident with plagiarism with both an original post and a follow-up.  I bring that up, not to rehash the negative, but instead to discuss an impression from this week.  If you read the follow-up, you will see that I had a chat, at that time, with Steinar (twitter) about the problem and how to resolve it.  I met Steinar for the first time this week.  And to be honest, I had forgotten about the conflict and had removed the RSS feed since the original domain had gone down.

Anyway, Steinar and I had the chance to chat for the first time face to face this week.  Steinar, in my opinion, is a pretty cool guy that made a simple RSS mistake.  The impression is that he remembered me for how I treated him several years ago and was very appreciative of that.  How cool is that?  I really appreciate the opportunity to chat with him and that something I did left a positive impact on him.

Another opportunity is to be able to perform random acts of kindness or service while at Summit.  Much like helping Paul White learn how to use a smart phone, I had the even more rare opportunity to help Kalen Delaney (blog | twitter) out of a sticky situation.  It was a minor but frustrating thing that all of us run into from time to time.  The zipper on her Surface case had become stuck, so I helped her with that.  It’s a little thing but it is the type of thing that, if you are watching, you will see happening all over the place during the week of PASS Summit.

So, the next time you are at Summit, and while back in your local communities after the week has ended for Summit, keep an eye out for those little acts of kindness.  But at the same time, keep an eye out for those that might be watching you.  What kind of impression are you leaving for them or for the SQL Community?

Summit 2014 – Early Impressions

Categories: News, Professional, SSC
Comments: 3 Comments
Published on: November 5, 2014

Summit 2014 is upon us.  Unless you are still under a rock, you probably know that.  And if you are under that rock, I am curious how you are reading this.

While it is early on in the week for the PASS Summit, things have really been going since Sunday for many.  A lot has happened.  A lot has already been learned.  And yes, some new people have already been met.  So far so good.

Now is a good time for me to just jot down some of my early impressions from the week.

It has been nice to receive a couple of compliments this week from a few people on various things.  It is waaaaay cool to hear things such as the following from community members.  Here are some samples.

“You taught me something.” Paul Randal (blog | twitter) in reference to a recent blog post that you can read here.  It is great to hear somebody learned something.  That is a primary driver for putting up content on the web and trying to help in the community where possible.

“Your index script has saved my bacon several times!” Stuart Ainsworth (blog | twitter) talking about my missing index script here.  Again, totally cool.  I am happy to hear about successes from code that I have put out there.

Those are two big impressions that would be great takeaways for the week.

But then we have to start throwing in the learning that is part of the week is.  I have attended a couple of sessions and found myself inspired by some of the content as well as hopeful by some of the other content.

By attending a session about Extended Events, I learned about an API that can expose some XE data via powershell to extend the possibilities and uses of XE.  Since the referenced blog does not contain any of the proposed material (slides or demos) it is hard to do much more with it just yet.  I will continue to check the referenced site as well as the session information on the Summit website.

By attending another session, I learned about a new feature called the Query Store.  It is basically a “Hammer that can make a lot of things look like nails!”  That session was presented by Conor Cunningham.  And while the Query Store has some extended events that are exposed in 2014, the XEs are useless and do nothing until SQL v.Next.  It would be totally awesome to have it back ported but that has no chance of happening.

And to top all of it off, it was great to sit down and get a couple of client issues fixed during the lunch break.

This is what Summit is, a huge chance to recharge, learn and to get excited about the technology and what is coming.  Oh and every now and again, one might get the chance to teach Paul White how to use a smart phone.

Ghosts – an eXtrasensory Experience

ghostrip_fireThis is the last article in a mini-series diving into the existence of ghosts and how to find them within your database.

So far this has been a fun and rewarding dive into Elysium to see and chat with these entities.  We have unearthed some means to be able to see these things manifesting themselves in the previous articles.  You can take a look at the previous articles here.

For this article, I had planned to discuss another undocumented method to look into the ghost records and their existence based on what was said on an msdn blog.  But after a lot of research, testing and finally reaching out to Paul Randal, I determined that won’t work.  So that idea was flushed all the way to Tartarus.

Let it be made very clear that DBTABLE does not offer a means to see the ghosts.  Paul and I agree that the other article that mentioned DBTABLE really should have been referring to DBCC Page instead.

Despite flushing the idea to Tartarus, it was not a fruitless dive.  It just was meaningless for the purpose of showing ghosts via that DBCC command.  I still gained value from the dive!!

All of that said, the remainder of the plan still applies and it should be fun.

Really, at this point what is there that hasn’t been done about the ghosts?  Well, if you are well tuned to these apparitions, you may have received the urge to explore them with Extended Events – sometimes called XE for short.

As has been done in the past, before we board Charon’s boat to cross the River Styx to Hades to find these ghosts in Elysium, one really needs to run the setup outlined here.

With the framework in place, you are now ready to explore with XE.

Look at that! There are several possible events that could help us track these ghosts.  Or at the least we could get to know how these ghosts are handled deep down in the confines of Hades, err I mean the database engine.

Ghost_XE

 

From these possible events, I opted to work with ghost_cleanup and ghost_cleanup_task_process_pages_for_db_packet.  The sessions I defined to trap our ghost tracks are as follows.

You can see there are two sessions defined for this trip down the Styx.  Each session aptly named for our journey.  The first (GhostHunt) is defined to trap ghost_cleanup and sends that information to a histogram target.  The second (SoulSearch) is defined to use the other event, and is configured to send to the ring_buffer.  Since the second event has a “count” field defined as a part of the event, it will work fine to just send it to the ring buffer for later evaluation.

Once I have the traps, I mean event sessions defined, I can now resume the test harness from the delete step as was previously done in previous articles.  The following Delete is what I will use.

Prior to running that delete though, I checked the Event Session data to confirm a starting baseline.  Prior to the delete, I had the following in my histogram target.

 

predelete_count

 

After running the delete, and checking my histogram again, I see the following results.

post_count

 

You can see from this that in addition to the 25 pre-existing ghosts, we had another 672 ghosts (666 of which were from the delete).

This is how I was able to investigate the GhostHunt Extended Event Histogram.

But what about looking at the other event session?

Let’s look at how we can go and investigate that session first and then look at some of the output data.

ghostclean

 

Cool!  Querying the SoulSearch session has produced some information for various ghosts in the database.  Unlike the histogram session that shows how many ghosts have been cleaned, this session shows us some page ids that could contain some ghosts – in the present.  I can take page 1030111 for instance and examine the page with DBCC PAGE as follows.

 

 

pagealtLook at that page and result!! We have found yet another poltergeist.

RIP

Once again we have been able to journey to the depths of the Database engine and explore the ghosts that might be there.  This just happens to illustrate a possible means to investigate those ghosts.  That said, I would not necessarily run these types of event sessions on a persistent basis.  I would only run these sessions if there seems to be an issue with the Ghost cleanup or if you have a strong penchant to learn (on a sandbox server).

Some good information can be learned.  It can also give a little insight into how much data is being deleted on a routine basis from your database.  As a stretch, you could even possibly use something like this to get a handle on knowing the data you support.  Just be cautious with the configuration of the XE and understand that there could be a negative impact on a very busy server.  And certainly proceed at your own risk.

«page 1 of 83






Calendar
November 2014
M T W T F S S
« Oct    
 12
3456789
10111213141516
17181920212223
24252627282930
Content
SQLHelp

SQLHelp


Welcome , today is Thursday, November 27, 2014