Exceptional PowerShell DBA Pt 3 – Collation and Fragmentation

In this final look into his everyday essentials, Laerte Junior provides some useful scripts for the DBA that use an alternative way of error-logging. He shows how to use a PowerShell script to check and, if necessary, to defragment your indexes, write data to a SQL Server table, and change the collation for a table. Being an exceptional DBA just got a little easier.

Recently, I’ve been demonstrating just a handful of ways you can take a proactive approach to your daily checklists, and how I’ve used PowerShell to make these processes more or less run themselves. In this third and final installment of my little PowerShell series on DBA checklists and procedures, I’ll show you a slightly different approach to outputting data into SQL Server tables, and I’ll explain why it’s sometimes a good idea to not error-log into flat files, but to use the EventViewer on the local workstation (i.e. the machine running the PowerShell scripts) instead.

But first, let’s look at one of the basic and routine tasks that we DBAs have to perform in any environment: defragmenting indexes. PowerShell can help us both in this task itself and in collecting historical data on periods of fragmentation for each index, thus helping us set appropriate fillfactors

As before, there’s no a lot of preamble or dissection of ideas here – just good, solid PowerShell code to make your life easier, and some examples of how to use it. Let us return to our headquarte… I mean, Data Center:

“Soldier Laerte! (again) Wake up! It’s already 5am and we have work to do.” – Yes, Master Major General Overlord! What’s happened? “Have you checked the fragmentation of the indexes of our 6.02×1023 servers?” – Of course Sir. I’m an exceptional Soldie… DBA and use PowerShell

Checking Fragmentation, and Rebuilding/Reorganizing Indexes

To start with, I’ll show you some PowerShell to return various pieces of information about your indexes, and which will also log any errors that occur in the local EventViewer (I’ll mention why I think this is a good idea a little later). Hopefully you have already read the earlier parts of this series and so are already stars at creating the module functions, we’ll dive directly into code (if you need a reminder about modules, you can find it in Part 1 of the series):

(This script as available for download at the bottom of this article.)

As I mentioned, and as you can see above, the write-MSSQLWinEventLog function records any problems that occurred during the execution of the script in the local Windows EventViewer for you to pick over at your convenience. If you want help with the parameters or what it does , just type:

Let’s see some examples in use :

This function is not returning all the properties of the index class of SMO, but can easily be changed. If you want to learn more about it, take a look here.

Rebuilding / Reorganizing Indexes

For this task, we will use a fairly well known rule of thumb: If your fragmentation is greater than 10% and less than 30%, Reorganize and run Update Statistics. If it’s greater than 30%, Rebuild. We could have used the SMO to do this operation, but personally I think it’s more productive to use T-SQL. The index rebuild has a number of arguments (fillfactor, online,all etc. ..) that, in my opinion, are easier to work with in T-SQL. I’ve also put in a new condition, so that this function only returns indexes with a pagecount greater than 1000.

(This script as available for download at the top of this article.)

Just remember, if you rebuild the clustered indexes, the non clustered ones are not rebuild unless the ALL parameter is specified. To find out more about index rebuilding and the various parameters you should be aware of, take a look at the article on Microsoft TechNet.

Output to SQL Server Table

Now that we’ve got a various PowerShell functions running and returning data, we’d really like to have that data in a SQL Server table. So, why don’t we change the output XML to a CSV file, and upload it to SQL Server?

When we were looking for information about our indexes, we selected the servername, databasename, tablename, indexname, IndexedColumns and FillFactor properties, so we’ll need to create a table to receive this data…

… And then execute this code:

The result will be:


Looking good so far; let’s just clean the data by removing the double quotes:

And we’ve finished the job :


Error Handling with EventLog

I firmly believe that some errors you can send to the Event Viewer, though by no means all error messages fit into this category. If I know that a process will generate a lot of error information, logging to EventViewer is probably not something I want to do. However In the case of the functions we’ve just looked at, I do not see many problems. Why do I think you should consider logging with EventViewer? Well, in my case we have a tool that monitors the EventLog and manages alerts that way, which I find incredibly useful, so I think this is definitely something worth considering.

To demonstrate, let’s create an error situation; I’ll execute the Get-MSSQLIndexInfo function against a server which does not exist in my txt list. First a warning message shows up, and then the function logs the error data in EventViewer.



In this case, information was generated and logged into EventViewer thanks to the Try-Catch block within the code, and script execution continued. In a situation here we have, say, 10 servers but only 1 has a problem, the one problem-server will have its failure logged, and the other 9 will still be analyzed.

Change columns’ collation

This last script is one that I really like to use. If you’ve ever needed to change the collation of a table, you surely must have noticed that although you changed the table’s collation, the collation of the columns hasn’t changed. Unfortunately this is “by design”.

Well the good news is that, with the use of PowerShell, this collation can be done in a very simple and uniform procedure. There are some undocumented procedures to make this change, but I personally prefer to do a little more work than just running an unknown procedure in production, and I like to think that you feel the same. Partly because there is a saying that overconfidence and a lack of confidence are the same thing, and partly because haste is the enemy of perfection. Anyway, let me share one more scene with you:

It being close to Christmas, the heart of our beloved manager is full of patience and serenity. Imagine the scene. I sat down, not realizing that he was beside me, I hear these kind words:

“Laerte, my dear DBA, when you’re free, I need a big favor.” I look to the side with wide, frightened eyes, thinking “it’s a trap!” “Could you help the people from the General Support Department to make a change collation? Because they do need lots of help… ” – With a heart full of relief and eyes full of joy, I answer: Yes my dear, benevolent, caring boss.” I love my boss *Sniff sniff*

In this case we will work with both the two error-handling processes I’ve demonstrated across this series of articles (MSSQLWinEventLog and MSSQLMsg). Error logs will be sent to a file if there is a problem in trying to change the collation, given that the T-SQL has some limitations such as the fact that the column cannot be a Primary Key, have related indexes, etc.. However, we don’t want to have our EventViewer overloaded with information about the collation not changing. What we really have EventLog for is if, by chance, the server is down or there’s a problem with the database or something like that. Between them, these two error-handling functions cover anything that could go wrong with this procedure, and they are included in the script below.

In terms of how to use this script, let’s say I want to change the collation of all tables in the ‘DBA’ Database on the ‘SERVER1’ server:

… and all procedural errors are logged in EventLog, and change collation errors in a log file. To see the later, just open the file in Notepad.



Over the course of these articles, we have seen how PowerShell can take the drudgery out of a lot of our day-to-day DBA tasks and furnish us with uniform, repeatable and consistent solutions. It is a fantastic technology that, in my humble view, has to be in the curriculum of production DBAs.

If you really want to learn and use PowerShell, I suggest you just download this fabulous open source project called SQLPSX SQL Server PowerShell Extensions, which I now have the honor of being a part of (making this a shameless plug). There, you will find everything from backup functions to replication – It’s a complete, one-stop-shop PowerShell resource.

Many thanks to Brad McGee, for letting me share the name of his best-selling book, “How To Became an Exceptional DBA”, which can be downloaded here. I believe that after you read this fantastic handbook, you will want to try and put its values into practice, as I try to do every day. With some hard work, studying and commitment, hopefully one day we all be Exceptional DBAs.

This is also a good point for me to thank the PowerShell community, especially Shay Levy, Chad Miller, Jonathan Medd ,Jeffery Hicks, Steven Murawsk ,Scripting Guys and everyone in the PowerShell community for just generally being awesome. They are professionals who make this community thrive, and are always ready to help.

Related Links

Jeffery Hicks The Lonely Administrator

Chad Miller http://chadwickmiller.spaces.live.com/ CODEPLEX – SQLPSX SQL Server PowerShell EXtensions

Jonathan Medd http://www.jonathanmedd.net/

Steven Murawski Use PowerShell

Scripting Guys Technet Script Center

MSDN Windows PowerShell Getting Started Guide

Window PowerShell Team Blog