PowerShell to Backup XE Session

Quite some time ago, I shared a few articles that peeled back the top layer of how to use PowerShell (PoSh) with Extended Events (XEvents). Among those articles, I showed how to retrieve the metadata, predicates and actions, and targets (to mention a few). Those are prime examples of articles showing some of the basics, which means there is plenty of room for some deeper dive articles involving both PoSh and XEvents. One topic that can help us bridge to the deeper end of the XEvents pool is how to generate scripts for our XEvent Sessions.

In this article, I will venture to show how to generate good backup scripts of our sessions using PoSh. That said, there are some caveats to using PoSh to generate these scripts and I will share those as well.

Wading to the Deeper End!

PoSh provides many methods and classes that allow us to more easily do various things. One of my favorite uses for PoSh is to automate tasks across the enterprise database server footprint. Nothing like creating a script that can effectively hit all of the servers one manages in one fell swoop to help minimize effort and time spent touching all of the servers. Afterall, a prime directive of the effective DBA is to be lazy.

So, when I look at creating a script in PoSh, I also look at how can I make this script function for a single server just the same as it would work for hundreds of servers. That will be one of the “nice to have” add-ons that you will see in this script. But, how did we get there in the first place?

A critical component of using PoSh to generate the scripts for each of the XEvent Sessions on the server is the GetScript(). GetScript() can be called for many different objects within PoSh. Another critical component is the XEvent SMO classes that were added as of SQL Server 2012. Take a mental note of that because it will come into play in a bit.

In addition to these critical components, I used some of the stuff that I mentioned in the previous articles as well as my article on XEvent management with PoSh. As I have said many times, these articles are building blocks and do rely upon many fundamentals divulged in previous articles.

Getting into the Script

Given this is a script that interacts with SQL Server, we need to ensure the SqlServer module is installed and loaded. It if is already installed, that is great. During my testing, I found that one of my servers, I ran this locally on, did not have it installed. With that discovery, I figured it is just easier to implement a check. If the module is not installed, then install it.

The next major component is how I fetch my list of servers. I prefer to have all of my enterprise SQL Servers listed within SQL Server. I keep a table with all of the servers and designate various attributes for the Servers (e.g. prod or dev, business unit, decommissioned etc).

Once, I know which servers need to be queried, I need to make sure I set my PoSh location properly.

With this script, I also have the ability to script out a specific XEvent session or to script every XEvent session deployed on the server. When scripting all sessions on the server, I opted to put all of the sessions into a single output script file. Since, this script can be run against multiple servers, I name each output according to the Server Name and the Instance on that server followed by the XEvent Session name (or all_sessions in the event all sessions are being returned).

Since I prefer to use a custom event_file path, I also wanted to ensure my script would produce a means to ensure the custom file path is created. I needed to validate that this only gets added to those sessions that had an event_file added to it.

There are a few more nuggets throughout that help bring this whole thing together. This is what the completed script looks like.

If you follow along on that script, you will see that I concatenate the sessions together and then force overwrite the output file. This ensures that I am not appending to an existing file and also ensures that I have the most current version of the XEvent session script. I configure this to output to the local server from where the script is executed.

Caveats

Using PoSh to generate scripts is extremely handy especially when working across numerous servers. That said, there are certain problems for which one must be aware. First is that the SMO classes for PoSh were introduced as of SQL Server 2012. These objects are not backwards compatible.

The second issue I have may be less of a concern to you than me, but it goes well with concerns I mentioned in the article I wrote about how to do this same task in TSQL. The script generated via PoSh adds the events in alphabetical order. I have also seen on some cases where the predicate is not in the exact order that was originally created. Predicate order is essential! Event order may not be an essential thing for anything other than Source Control or for the demanding OCD DBA. Your mileage may vary.

Final Thoughts

Bringing PoSh to the world of XEvents can open up your eyes to a world of better automation and DBA efficiency as you manage your enterprise. PoSh also can help us dive deeper into the world of XEvents as we will see in some upcoming articles.

Working with Extended Events will help you become a better DBA. Working with PoSh can also help you in many various tasks to become a better DBA. Combine the two and you just might have a super weapon.

Interested in exploring the vast world of Extended Events? Check these out! The library of articles is pretty large and continues to grow.

Get a List of Files with Data

Categories: News, Professional, Scripts, SSC
Comments: No Comments
Published on: December 30, 2019

Suppose you have a directory on your server that houses over 300,000 csv files. These files are automatically created by an automated process related to your SQL Server operations and are automatically created when the process runs. The process runs on a schedule every 15 minutes.

Now suppose that you have been tasked with figuring out which of the files have data of value in them and which can be ignored. The files that can be ignored will all be either 0k or 1k in size. However, some of the 1k files can also be ignored because they only contain a header. Every csv file should have a header. So, we must figure out a way to filter out those files that are 1k or less and only have a header row.

Filtering out these files with this criteria will allow for us to find files that have useful data in them. Now why do you need to figure out which ones have useful data? Let’s just say that maybe some of the files were created with some bad data in them and you need to figure out which files may have bad data so those particular files can be regenerated. As luck would have it, the automated process does not have any sort of logging and does the bare minimum to create the files in the first place. (Sometimes you just inherit a flawed process where inadequate thought was given.)

Finding the Files

The hard part has been done at this point. At least you know some of the attributes that will help distinguish wanted files from the unwanted files. Sorting through these files by hand could be rather bothersome and cumbersome. The trick here is to find a way to search all of these files quickly and filter easily the bad from the good. Sounds like an opportunity for another automation or script. I have just the powershell script for that.

With this script, I have a couple of things to help me find files of interest quickly. First, I have a filter in place to allow me to search a specific number of days worth of files. Second, I have a filter in place to ensure it only returns file names where there is more than just a header row present.

Now, instead of spending hours perusing files trying to find something of value, I have reduced my time spent to just a mere fraction of that. Next steps after this would be to go and add some additional logging and better robustness to the initial process to reduce the chance of bad data being put into the files in the first place.

Put a bow on it

Automated processes are fantastic. We as DBAs strive to have more tasks automated than not. That said, automation without planning is just creating a time sink later on in the job. At some point, you may need to employ some powershell script similar to this in order to find where your automated process has gone wrong (if you did not plan that process well enough from the start).

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the sixth article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

PowerShell ISE Crashes

Working with PowerShell brings a lot of advantages and power to help manage a server. The more current your PoSh version, the more efficiently you will be able to manage your server. Sometimes getting to the current PoSh versions comes with a little pain such as ISE crashes.

I recently had the mis-adventure of working through some ISE crashes after bringing some systems up to PoSh 5.1 that were either PoSh 2.0 or 3.0. It’s not a very fun situation to run a WMI update and then run into a crash of any type when testing if it worked. Your first thought is something terrible has happened.

As it stands, the problem is more of a nuisance than a critical failure. That said, it is enough of a problem that anyone who uses the ISE or .Net applications may experience a slight cardiac event.

Fonts

As you work to quickly recover from your missed heart beat, you start digging through logs and then hitting good old trusty google.

Diving through the logs, you might just happen across an error similar to the following:

Problem signature: Problem Event Name: PowerShell NameOfExe: PowerShell_ISE.exe FileVersionOfSystemManagementAutomation: 6.1.7600.16385 InnermostExceptionType: System.Xml.XmlException OutermostExceptionType: System.Reflection.TargetInvocation
DeepestPowerShellFrame: indows.PowerShell.GuiExe.Internal.GPowerShell.Main DeepestFrame: indows.PowerShell.GuiExe.Internal.GPowerShell.Main ThreadName: unknown.

Maybe the first error you encounter might look like this one instead:

System.TypeInitializationException

“FileFormatException: No FontFamily element found in FontFamilyCollection
that matches current OS or greater: Win7SP1”.

Inner exception originates from: CompositeFontParser

Either way, the error shoots us back to the same fundamental problem. The ISE won’t load, you get an error message and you can’t confirm that the WMI patch was applied properly.

As you work your fingers faster and faster through the pages on google, you discover that this problem is caused more explicitly by a patch for the .Net framework and not necessarily the work to upgrade your PoSh version. It only waited to manifest itself after the upgrade.

That’s gravy and all, but how does one fix the problem? For me, the quickest and most reliable fix was to simply jump straight to the root of the problem – fonts. The ISE is a WPF application and it also requires a fallback font (if a character isn’t present in your font set, then the app chooses a substitute from the fallback font – or something like that).

The fix is extremely simple and really underscores why this is merely a nuisance issue and not a critical problem. Thus it shouldn’t cause any sort of sinking internal feelings of any sort. There are a few plausible fixes floating around out there. I recommend just doing a manual font replacement. It is all but three simple steps:

  1. Download GlobalUserInterface.CompositeFont
  2. XCOPY the font to %windir%\Microsoft.NET\Framework\v4.0.30319\WPF\Fonts
  3. XCOPY the font to %windir%\Microsoft.NET\Framework64\v4.0.30319\WPF\Fonts

After you have copied the font to those two directories, then all that is needed to be done is launch the ISE. I ran into the same problem on three or four servers and the fix took no more than 5 minutes on each of the servers.

Conclusion

I previously mentioned that I have been working more and more with PoSh to try and improve my skillset there. This is one of those very low-level trinkets that I ran into as I have been working to hone my skills in that tech. For other, possibly, interesting articles about my experiences with PowerShell, you can check out these articles.

Given this job is tightly related to the system_health black box sessions (sp_server_diagnostics and system_health xe session), I recommend fixing the job. In addition, I also recommend reading the following series about XE and some of those black box recorder sessions – here.

Syspolicy Phantom Health Records

SQL Server comes with a default SQL agent job installed (for most installations) to help manage the collection of system health data. I would dare say this job is ignored by most people and few probably even know it exists.

This topic is not new to me. You may recall a previous article I wrote entailing one type of failure and how to resolve that failure. That article can be found here.

I recently ran into a variant of the problem outline in that previous article that requires just a bit of a different approach. The errors turn out to be similar on the surface but really are different upon closer inspection.

Phantom Health Records

If you are unfamiliar with the topic, I recommend reading the previous article. Then after reading that article, maybe brush up a little bit on the SQL Agent. The failures we will be looking at are within the SQL Agent and come from the job called: syspolicy_purge_history.

For this latest bout of failure, I will start basically where I left off in the the last article. The job fails on a server and I have access to other servers of the same SQL version where the job works.

Before diving any further into this problem, let’s look at what the error is:

A job step received an error at line 1 in a PowerShell script.
The corresponding line is ‘set-executionpolicy RemoteSigned -scope process -Force’.
Correct the script and reschedule the job. The error information returned by PowerShell is:
‘Security error. ‘. Process Exit Code -1. The step failed.

Having the error in hand, and knowing that the job works elsewhere, my next logical step (again based on experience from the last article with this job) is to script the job from another server and use it to replace the job on the server where it fails. In principle this is an AWESOME idea.

 

Sadly, that idea was met with initial failure. As it turns out, the error remained exactly the same. This is good and unfortunate at the same time. Good in that I was able to confirm that the job was correctly configured with the following script in the job:

Since the step fails from SQL Server let’s see what else we can do to make it run. Let’s take that code and try it from a powershell ise. So, for giggles, let’s cram that script into powershell and see what blows up!

Now isn’t that a charming result! This result should be somewhat expected since the code I just threw into the ISE is not entirely powershell. If you look closer at the code, you will notice that it is using sqlcmd like conventions to execute a parameterized powershell script. Now, that makes perfect sense, right? So let’s clean it up to look like a standard PoSH script. We need to replace some parameters and then try again.

This will result in the following (resume reading after you scratch your head for a moment):

The key in this failure happens to be in the sqlserver. PoSH thinks we are trying to pass a drive letter when we are just trying to access the SQLServer stuff. Depending on your version of server, SQL Server, and PoSH you may need to do one of a couple different things. For this particular client/issue, this is what I had to try to get the script to work.

If you read the previous article, you may notice this command looks very much like the command that was causing the problems detailed in that previous article. Yes, we have just concluded our 180 return to where we started a few years back. Suffice it to say, this is expected to be a temporary fix until we are able to update the system to PoSH 5 and are able to install the updated sqlserver module.

As is, this script is not quite enough to make the job succeed now. To finish that off, I created a ps1 file to house this script. Then from a new step (defined as a sqlcmd step type) in the syspolicy purge job, I execute that powershell script as follows:

Tada, nuisance job failure alert is resolved and the system is functioning again.

Conclusion

I dare say the quickest resolution to this job is to probably just disable it. I have seen numerous servers with this job disabled entirely for the simple reason that it fails frequently and just creates noise alerts when it fails. Too many fixes abound for this particular job and too few resolve the failures permanently.

I would generally err on the side of fixing the job. Worst case, you learn 1000 ways of what not to do to fix it. 😉

Given this job is tightly related to the system_health black box sessions (sp_server_diagnostics and system_health xe session), I recommend fixing the job. In addition, I also recommend reading the following series about XE and some of those black box recorder sessions – here.

Simplified Session Backups

Comments: No Comments
Published on: October 12, 2015

posh_DBPowerShell exposes scads of information and provides the professional with a formidable tool for the tool-belt. Over the past several articles, I have demonstrated how this tool can be used to uncover useful information and metadata.

The groundwork has been laid to be able to mine information about each of the core components for Extended Events Sessions. These core components are Targets, Events, Actions, and Predicates. Being able to query this metadata and understand what it represents is an essential skill to more effective Extended Event Session management.

Thus far, I have demonstrated how to perform metadata inquiries from both PowerShell and TSQL. I even showed how to map this metadata between script and the GUI screens. As I concluded the metadata discovery via TSQL segment, I demonstrated a very useful script, to generate backup scripts of deployed sessions, using all of the concepts discussed to that point. That script can be found here. In this article, I will share how to do the same thing via PowerShell.

Backstory

Before I go into depth on the script within PowerShell that can be used to backup a deployed session, I want to share a bit of a story.

Many years ago while still in High School, I had a really tough teacher for Calculus. He had a reputation going back to long before I took any of his classes of being a very hard teacher. I enjoyed this teacher quite a bit. My group of friends and I even enjoyed giving him a hard time because it seemed like he could take it as well as dish it out a little.

This teacher had a few philosophies that I didn’t really much care for at the time, but I can sure appreciate now. Truth be told, I gained an appreciation for his teaching philosophies within the first year out of High School. One of his philosophies was to make the High School classes at least on par with what could be expected in College. This made college significantly easier.

Another of his philosophies was to teach core concepts first then to build on those concepts. While teaching the core concepts, he always showed the hard way of doing things first and sometimes an equally hard method as the secondary approach. But there was always an easy way of doing things that would be eventually taught. Sometimes it is essential to a solid foundation to know how to solve a problem the hard way before learning how to do it the easy way.

Correlating that to SQL Server, there are many times that the first approach to do something may be a more difficult approach. As the skills are learned, then maybe an easier solution may present itself, but there is (hopefully there is) a solid foundation there first to help support and enable the learning of an easier method.

Simplified Session Backups

In the aforementioned article, I shared a script that would recreate a deployed session via TSQL. That script was lengthy and required a few tricks to get things to work out just right (e.g. concatenations). Now I will show how to do the same thing in a sickeningly simpler way.

The example that I am about to share requires that SQLPS be loaded and the “demosession” Session be loaded into an object. If a recap of how to do that is needed, I recommend reading this article prior to proceeding.

With the “demosession” Session loaded into an object, I also want to load the name of the session into a parameter to simplify things just a bit.

I can confirm the value of the $sessionname parameter if I choose. In this case, the value does return as “demosession” and is the correct value needed. With that, now I can run the following script to create a backup of my deployed session:

That is the entire script! Just one line! This script could have been even smaller, but I felt it necessary to dump the script out to a SQL file in order to have the script available for future needs – should it be needed. One caveat here is that the directory path must exist or an error will be thrown. To create the directory path, it is as simple as the old DOS commands of old to create that directory.

While the script produced is perfectly viable to recreate a deployed session, it is important to note that the Events listed in this script will be in the order of Package then Event sorted alphabetically. Recall from the prior article on Session backups that this is the default script order used by SSMS as well and is not necessarily the same order that the original Session was created.

How did I arrive at this method to create this script? This goes back to repeating some of the processes I have used throughout these articles on PoSH and Extended Events. Starting with a quick exploration into the $sessionobject object.

This would lead me to seeing the following results:

session_script

Seeing ScriptCreate as an option is a great indicator that I can do something with this Session to quickly recreate the session script. If I run that with the session obj, then I get the next clue.

session_scriptnocommands

I highlighted the interesting clue to this. Even though it says ScriptCreate, it apparently needs to have an additional command in order for it to do anything. With that in mind, a quick Get-Methods on ScriptCreate() will produce insight that there are commands that can be utilized. One of those commands happens to be GetScript(). GetScript seems pretty self-explanatory, so I would settle on that and voila the script can be reproduced.

session_scriptgetscript

There it is. I have shown two methods to create backups of deployed sessions. The first being the more complex solution and then this extremely simple solution. Both are valid means to produce the backup scripts and it now just boils down to a choice as to which to use.

 

This is the latest article in the 60 days of XE series. If you have missed any of the articles, there is a recap for the series. To recap or reread the articles in this series, please visit the table of contents.

Deployed Target Metadata with PoSH

Comments: No Comments
Published on: October 9, 2015

posh_DBHaving just covered the methods to discover the metadata for a deployed session including the details about the session settings, events, actions, and predicates, I am nearly finished with the session metadata. All that remains is to dive into the metadata for Targets.

Through the articles thus far, I have shown some of the nuances in dealing with PowerShell to retrieve this metadata and how it differs a bit from performing the same inquiries via TSQL. PowerShell exposes Extended Events differently than what one would expect due to familiarity with Extended Events through the catalog views and the DMVs I have previously discussed.

Along with the nuances associated with accessing this metadata via PoSH, I have also showed how some of this metadata is different. The dive into the metadata about Targets will continue to reinforce and embrace these differences.

Regrouping

Picking up from where I left off, and following the same pattern as has already been target_metadataestablished, it is time to dive into the metadata for the Predicates and Actions that are tied to an Event deployed with a session using PowerShell. This means that I will need to ensure I have a session established that has the SQLPS module loaded. Then I need to ensure I browse back to the sessions folder.

Just in case, here is the working directory for the sessions out of the default instance.

Also, from this point, I will go ahead and load the “demosession” session into an object again for demonstration purposes throughout the remainder of this article.

With that loaded, I just need to remember to reference the Events object (as was introduced in the prior article).

Targets

While the Actions, Predicates, and Events are all tied tightly together (Actions and Predicates have to be attached to an Event), Targets are somewhat independent. The Target is directly attached to the Session in the same way that an Event would be tied directly to the session. Looking at the Metadata for the Session attached to an object, it will show the Targets object being similar to the Events object. Let’s see that in action:

That script will produce the following:

session_psmetadata

Reverting back to the patterns used so far through this series of posts, I will explore what is available to me for the Targets that may be deployed to the session of interest. The following query would be the first step:

This query will result in the following:

ps_targets

Following the established patterns and results, I can see some very common ground here. The results of this query are strikingly similar to the results seen when querying Actions and Events where I would also see the “name”, package and a description. This is a pretty basic demonstration into viewing the targets tied to a session. To get a better view of the Targets attached to a session, I need to dig a little bit deeper.

This will produce the following useful information:

ps_targetsmethods

From those results, I can see that I would immediately be interested in the TargetFields (recall the similar property from the Events) and the Properties property. The first of these that I want to explore is the Properties property.

Note the trick I am using again to dump the results into a quasi-table format. And the results will look like this:

ps_targeteav

Once again, the results should seem somewhat familiar. These properties underscore one of the nuances being the EAV model with how PoSH accesses the Extended Event properties. With this kind of query, I can see what kind of targets have been deployed to the Session and get a little bit of information as to the type(s) of target(s) they are.

This is all really cool. The real meat of the metadata for Targets is in the “Set” options that have been configured. The “Set” options are the configurations that are exposed via the TargetFields property previously seen. With that said, it is time to dive into these configuration options.

This query returns this rather useful data:

ps_targetsettings

And to make this slightly more useful and more valuable, I need to know which target has which setting. For a more usable query, I can resort back to the table output. Instead of just using the data directly accessible from the previous query, I will use the Parent property to get the name of the Target.

And now I will get something like this:

ps_targetsettingswname

This is good and useful information. Being able to tie the target type to the specific setting is crucial to better understanding how the metadata is interrelated. This is also something pretty easy to do via TSQL as I demonstrated in this article.

In this article I have covered the core concept of Targets. I showed how to access the Target metadata which includes the “Set” operations for each of the Targets. This is the last of the core concepts to discuss the means to access the metadata via PowerShell. I do have one more item I wish to discuss via the means of PowerShell and I will be discussing that in the next article.

Stay tuned as I continue to work through another trick that can be done via PowerShell in the next article. To recap the series, please visit the table of contents.

Deployed Action and Predicate Metadata with PoSH

Comments: No Comments
Published on: October 8, 2015

posh_DBI have recently shown that using PowerShell can be extremely powerful in obtaining insight into how to investigate deployed Extended Event Sessions. Throughout the demos I have used, I hope that it has also shown that PowerShell can be very easy to use.

Querying this metadata through TSQL is easy, but using PowerShell does seem to simplify this just a step more. The simplicity of PowerShell is a different kind of easy over TSQL. For me, the simplicity of PowerShell comes in the quantity of code. That said, I want to reiterate what I said previously – there is a bit of a learning curve with PowerShell. PowerShell exposes Extended Events differently than what one would expect due to familiarity with Extended Events through the catalog views and the DMVs I have previously discussed.

I have demonstrated how to access session information as well as how to access Event metadata. That said, I left the discussion of Actions and Predicates for this article. As I expose this information, the simplicity of PowerShell will continue to be reinforced – along with the patterns that I hope have started to become apparent in the previous articles.

Regrouping

Picking up from where I left off, and following the same pattern as has already been 3d_toolestablished, it is time to dive into the metadata for the Predicates and Actions that are tied to an Event deployed with a session using PowerShell. This means that I will need to ensure I have a session established that has the SQLPS module loaded. Then I need to ensure I browse back to the sessions folder.

Just in case, here is the working directory for the sessions out of the default instance.

Also, from this point, I will go ahead and load the “demosession” session into an object again for demonstration purposes throughout the remainder of this article.

With that loaded, I just need to remember to reference the Events object (as was introduced in the prior article).

Predicates

Looking back at some of the metadata that was revealed for the Events object in the previous article, I have the following:

ps_eventgm

If I query the Events directly, I should be able to see that there is a Predicate returned. Unfortunately, the formatting of the output is not very friendly and much of the information is truncated. Seeing the Predicate listed as another item that can be queried direct is somewhat useful. In addition there is a PredicateExpression property that could be useful.

In this following image, I show both the queries used for each (Predicate and PredicateExpression) as well as the corresponding output.

ps_predicates

Notice how the PredicateExpression is a bit more familiar in the output? With the wonky formatting of the output for the predicate, one could also presume that this output would be the XML type of formatting for a pred_compare object. With a little extra effort, this can be confirmed.

And with better formatting, here are those results:

ps_predcompare

These results would reinforce the prior presumption. And it also helps to reinforce the differences between how the Predicate is represented in PowerShell over the sys.server_event_session_events view where the predicate column is closer in value to the PredicateExpression in PowerShell. Additionally predicate_xml in the the view more closely fits the Predicate property within PowerShell. As long as one can keep these differences in mind, it will make working with PowerShell and Extended Events a tad easier.

Actions

Accessing the metadata for Actions via PowerShell is a little bit more straightforward in that there aren’t multiple objects like with Predicates. Unfortunately, with Actions, it is right back to the EAV data model with the Actions object which can cause a different level of complexity and possibly confusion.

Starting with a very basic query to the Actions:

And the results of that query:

ps_basicActions

In addition to a problem noted with the Events (package name being listed twice), there is an additional problem with the initial output here. The problem with the default output is that the Event name is missing. Granted, this is also a problem with the Predicates examples too. This is one of those areas where extra effort has to be made when using PowerShell to explore Extended Events. Luckily, the data is there. All that is needed is to know how to get to the data.

So how does one figure out the Event for which the Action (or Predicate) was added? I have that in this next example:

This query is simply requesting the Parent (Event) of the Action, and then the Action name to be returned in a table format. Here is what the output should look like:

ps_actiontable

Having the Event name is a critical piece, especially in a more complex Session such as the system_health default session. After-all, an Action (yes it must be repeated because it is a core concept) is only attached to an Event and has to be configured for each individual Event.

Circling back around to the EAV concept. One of the areas within Actions where this is more evident is when trying to look at the properties of the Actions module. Starting with a sample query such as this:

Again, I am instructing the results to be returned in a table format. If I don’t do that, then everything is returned in a single column and it is far more difficult to pick out the needed bits of information. Forcing the results to a table format, I get the following:

ps_actioneav

Looking at these results, I see Name as a value under the Name column and the associated value for it under the Value Column. Then there is a lot of repeating. This means that a little extra work is going to need to be done to parse things a bit further.

In this article I have covered two of the core concepts related to deployed sessions in Extended Events – Actions and Predicates. To be able to effectively use PowerShell, I have also covered a few of the nuances necessary in figuring out where essential metadata is exposed via this tool.

Stay tuned as I continue to work through some of the Target metadata in the next article. To recap the series, please visit the table of contents.

Exposing Deployed Event Metadata with PoSH

Comments: No Comments
Published on: October 7, 2015

posh_DBIn the last article I introduced a power tool that can be used to help manage Extended Events. That tool is PowerShell. In that article, I focused primarily on introducing PowerShell as a power tool to help in discovering the Extended Event Sessions deployed to the server as well as some of the settings that are associated with a session. This is the same as thing as calling these settings the Session metadata introduced here.

Now that some of the basics concerning how to access Extended Events via PowerShell have been covered, it is appropriate to start digging in a little deeper. This deeper dive will continue in the direction of metadata discovery and familiarity with this powerful tool.

More Power

Picking up from where I left off, and following the same pattern as has already been established, I want to now 3d_toolstart diving into the metadata for the events that are tied to a deployed session using PowerShell. This means that I will need to ensure I have a session established that has the SQLPS module loaded. Then I need to ensure I browse back to the sessions folder.

Just in case, here is the working directory for the sessions out of the default instance.

Also, from this point, I will go ahead and load the “demosession” session into an object again for demonstration purposes throughout the remainder of this article.

In the previous article I noted that within the metadata for the session, there is a listing of some objects that can be further perused, which are related to the session itself. Those objects are Targets and Events (shown in the next image). I want to immediately jump into the Events object and see what needs to be done within PowerShell to access the metadata for the deployed Events.

session_psmetadata

Luckily, as with how things work from TSQL queries into the metadata, there are patterns that can be established within PowerShell as well. In this case, to get to the Events within the Deployed Sessions, I would need to “query” that object by adding it to the “query” that I use to list the contents of the “Sessions” object already declared (the $sessionobj instantiated previously). This is a lot easier to explain via the code.

See the pattern? This is the same sort of pattern that was used when querying the various deployed Session settings in the previous article. With the Session loaded in an object, I can query properties or sub-objects by appending that “object” or “property” at the end of the $sessionobj object I created. Running that last snippet will produce the following output.

ps_eventlist

Immediately visible are a couple of interesting tidbits. Beyond the query returning the list of all of the events in the session, I can see the package names, the predicates and the descriptions of the events. This is similar to the sys.server_event_session_events catalog view excluding the descriptions. Unfortunately, the formatting is a bit awkward so extra measures would be needed in order to get that description to be useful – just the same as would be needed when querying via TSQL.

Additionally, there is a bit of redundancy with this object. There is a listing for the package name for each of the events. Now, take a look at the event names in these results. I cheated a little bit and highlighted the interesting parts. Each event will have the package name as part of the event name within the PowerShell results. Again, just a note because it is one of those things that could cause a bit of grief later when trying to build more complex queries via PowerShell.

Looking a little bit further, if I continue to follow patterns established thus far, I can check the Metadata for  this “object” and learn a little bit more.

And the results:

ps_eventmetadata

This should come as no surprise. Actions are tied to specific events so it makes sense that it is an object accessible via the events object. In addition, I can see how the “SET” operations are tied into the Event. The difference here being that the object is called “EventFields”. This should make sense since this metadata is exposed in TSQL via the sys.server_event_session_fields catalog view.

And if I invoke a Get-Methods on the Events Object as follows:

I will get a clearer picture of the metadata and what I can do with the Events.

ps_eventgm

In addition to the Actions and EventFields objects, I can see the Name and Predicate properties just the same as I would if I were to query the session events through the catalog view – sys.server_event_session_events.

With the connection to the event fields being so much more obvious through PowerShell and thanks to the prior article on the event fields / “SET Operations”, I want to explore this metadata at this point.

The results of this will be:

ps_eventfieldmeta

From these results, I can see that the “customizable” data point “collect_database_name” for this particular event has been enabled.

In this article I have shown how to begin the exploration into the metadata for deployed Session Events. Additionally, I demonstrated how to quickly get to the set operations tied to those events. I have not yet covered the core concepts of Actions and Predicates which were exposed through some of the queries demonstrated in this article. Tune in for the next article where I will cover both of those topics in greater detail.

With all of the information that has been revealed through this series, it is easy to have either missed some  of the information or to have sensed a bit of information overload. If a recap is needed for one of these or any other reason, feel free to catch up here.

Extended Event Management Power Tools

Comments: No Comments
Published on: October 6, 2015

So far in this series I have introduced a couple of tools that are suitable for helping with the management of Extended Events. The tools introduced so far are the GUI and TSQL scripts. While the GUI is suitable, using a script really is a far better means of managing Extended Events.

That said, these are not the only tools that could be in the shed for helping in the posh_DBmaintenance and management of Extended Events. One really cool tool that can be added is PowerShell. Isn’t that just amazing?

PowerShell can pack a pretty big punch when dealing with Extended Events. With using PowerShell to manage Extended Events, there could be a bit of a learning curve, and it may not be the right tool all the time. I will cover some uses of PowerShell with XEvents over the next handful of articles.

Management Tools

Since I will be discussing the use of PowerShell to help manage Extended Events, a working knowledge of PowerShell would be somewhat advantageous. If PowerShell is a new endeavor, then I recommend checking out a few tutorials prior to proceeding. Here are a couple of options: Microsoft PowerShell Introduction, Month of Posh by Wayne Seffield, or this Month of SQLPS by Mike Fal (as of publication of this article, this series is ongoing). For my demos, I will be using the Windows PowerShell ISE. Feel free to use whichever tool you feel most comfortable, whether it be an ISE or direct to PowerShell.exe.

After spinning up my ISE, I am going to dive straight into SQLPS.

start_sqlps

The first thing I need to do is to change the security policy so I can load the SQLPS. After loading SQLPS, I then revert the security policy back to what it was prior to the change. Here is that code snippet:

As shown in the previous image, I now have SQLPS loaded. Notice the directory change after the module load, going from C:\Windows\System32> to SQLSERVER:\>. With SQLPS loaded, I can start exploring a little bit.

sqlps_xeventsession

Part of the exploration technique is to figure out what is available at each level. In this case, I use dir to figure out what is available to me as I progress through the structures. After the first dir from the SQLSERVER:\ , I can see the possible paths to follow and that one of them is XEvent. That is the path (for the default instance) that I want to follow, and I did as shown with this particular script.

Now, after I run another dir, I can see the beginnings of a wealth of information. I will get the names of all Extended Event Sessions, if the session is started and when that session was started. This is pretty good news. Seeing the list of sessions, I will pick a session and explore further.

Using the Get-Member method against the object I created into which I attached the session of interest, I can peruse the options available to work with the session.

session_gm

Unlike doing this from within TSQL where views are used to expose the metadata, PoSH takes advantage of Methods and Properties that will permit the perusal of the various components of a session. To explore a little further, I can take advantage of the Metadata property.

session_psmetadata

Because of the way Extended Events is architected, there is some consistency between the way investigating Extended Events via PoSH vs. TSQL. Here, attached as an object to the Session, there are the Events and Targets objects. This also tells me that I can browse from within the session object down through the Events and the Targets.

To investigate the properties, like I did in the article on exploring session metadata, I can do so in PoSH by doing something like this (bear in mind, that these are scriptlets designed to work with prior examples):

And this would show me the following output:

session_psproperties

These properties should be rather familiar. These are the same settings (the ones in grey outline) that are exposed via the sys.server_event_sessions catalog view. The variable here being that the StartTime property is not one of the properties in that view. Rather, this particular information can be handy and is easily accessed via PowerShell. The ID on the other hand, though not a setting that can be configured, is exposed via that view and is the same ID whether seen through the view or through PowerShell (just as are the properties outlined in grey).

There is a lot of information that is exposed through PowerShell for Extended Events. I have only begun to tap into the available information as I have begun to introduce this tool. I have just shown how to expose the various session settings as was done previously via TSQL in the aforementioned article.

This is just another quick tutorial in the series about Extended Events. Feel free to peruse the growing recap – here.

Powershell, Me and the MCM part II

Comments: No Comments
Published on: December 20, 2010

Last week I posted the first part of this series.  I thought it would be a good idea to give a little follow-up on how my foray into Powershell as I attempted to download this videos.

Attempt 1:  Failed miserably.  I did not have enough disk space and thus only downloaded about half of the videos.  I tried to remedy this by changing the path to a different volume but that threw out some error messages.  The script works best when continuing to use the $env variable in the path for the location to dump the files.

Attempt 2:  From a different machine this was working beautifully until I noticed that every once in a while a file would error out and the download would resume with the next video in the list.  When I attempted to look up those particular videos, they were all present.  So I restarted thinking it was a local environment problem.

Attempt 3:  I figured out that attempt 2 was failing because my laptop was going to standby – duh!!!  I disabled the standby and finally got the download to work from start to finish without fail.

Now, I can place these videos on my Android and watch/listen from anywhere I have my phone.  Since I have an auxiliary port in my car, I can just plug the phone in, let it charge, and listen to the videos while I commute – through the car speakers.  It is nice to have such a high-level of training material available for a quick refresher or for that first time through.  I recommend people get on board and take advantage of what Microsoft, Paul Randal, and others have given to the community through this training.  Since I pay out of pocket for my training – this is the right price for me.

Thanks for the Christmas Gift.

«page 1 of 2

Calendar
January 2020
M T W T F S S
« Dec    
 12345
6789101112
13141516171819
20212223242526
2728293031  

Welcome , today is Monday, January 27, 2020