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.02x1023 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):
Function Write-MSSQLWinEventLog()
{
#requires -Version 2
<#
.SYNOPSIS
Write to Application LocalComputer Eventlog
.DESCRIPTION
Write to Application LocalComputer Eventlog
.PARAMETER Source
Mandatory String
Source EventLog
.PARAMETER EventID
Mandatory Int
EventID Eventlog
.PARAMETER EntryType
Mandatory String
Entry Type to Eventlog . Can be Error, Information Warning
.PARAMETER Message
Mandatory String
Message to Display
.LINK
www.laertejuniordba.spaces.live.com
#>
[CmdletBinding()]
Param (
[Parameter(Position=1,Mandatory=$true,
ValueFromPipelineByPropertyName=$true,HelpMessage="Source")]
[Alias("SourceName")]
[String] $Source ,
[Parameter(Position=2,Mandatory=$true,
ValueFromPipelineByPropertyName=$true,HelpMessage="EventID")]
[Alias("EventIDNumber")]
[int] $EventID ,
[Parameter(Position=3,Mandatory=$true,
ValueFromPipelineByPropertyName=$true,HelpMessage="EntryType")]
[Alias("EntryTypeString")]
[ValidateScript({$_ -match "Error|Warning|Information"})]
[string] $EntryType ,
[Parameter(Position=4,Mandatory=$true,
ValueFromPipelineByPropertyName=$true,HelpMessage="Message")]
[Alias("MessageString")]
[string] $Message
)
Process
{
if (!(test-path
"HKLM:\SYSTEM\CurrentControlSet\Services\Eventlog\Application\$Source"))
{
[System.Diagnostics.EventLog]::CreateEventSOurce($Source,
"Application")
}
Write-EventLog -computername $env:computername -logname Application
-source $source -eventID $eventid
-entrytype $EntryType -message $Message
-ErrorAction SilentlyContinue
}
}
Function Get-MSSQLIndexInfo ()
{
#Requires Powershell 2.0
<#
.SYNOPSIS
Returns information about index
.DESCRIPTION
Returns information about index
Version 1.0
Laerte Poltronieri Junior
www.laertejuniordba.spaces.live.com
.PARAMETER TXTServersList
Optional String
Full SQL Server file list
"C:\<path>\<FileName>.txt"
If not informed, the current server is used.
.LINK
www.laertejuniordba.spaces.live.com
.EXAMPLE
Get-MSSQLIndexInfo
Get-MSSQLIndexInfo C:\Servers.txt
#>
[CmdletBinding()]
PARAM (
[Parameter(Position=1,Mandatory=$False,
ValueFromPipelineByPropertyName=$true,HelpMessage="SQL Servers File")]
[Alias("FullNameTXT")]
[String] $TXTServersList = $env:COMPUTERNAME
)
Begin
{
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
| out-null
}
Process
{
$verbosePreference="continue"
$TodayDate = get-date -format "yyyy-MM-dd hh:mm:ss"
$Error.Clear()
if ($TXTServersList.substring($TXTServersList.length -4,4)
-eq ".TXT")
{
try
{
$ServersList = get-content $TXTServersList
} catch {
$msg = $error[0]
Write-Warning -Message $msg
Write-MSSQLWinEventLog "Get-MSSQLIndexInfo" 70
"ERROR" $MSG
break;
}
}
else
{
$ServersList = $TXTServersList
}
$LineNumber = 1
$FinalResult = @()
foreach ($svr in $ServersList )
{
try
{
$Server=New-Object
"Microsoft.SqlServer.Management.Smo.Server" "$svr"
$Server.Databases | where-object {(!$_.IsSystemObject)
-and $_.IsAccessible -and $_.name -eq "DBA" } |
foreach {
$DatabaseName = $_.name
foreach ($tables in $Server.Databases[$_.name].tables ){
if (!$tables.IsSystemObject)
{
$TableName = $tables.name
$TableRowCount = $tables.rowcount
$TableIsHeap = !($tables.hasclusteredindex)
foreach ($index in $tables.indexes)
{
$Enum = $index.EnumFragmentation(3)
$Fragmentation =
$enum.rows[0].AverageFragmentation
$PageCount = $enum.rows[0].Pages
[String] $IndexedColumns =
$Index.IndexedColumns
$ObjectIndex = New-Object PSObject
-Property @{
LineNumber = $LineNumber
Date = $TodayDate
ServerName = $Server.Name
Databasename = $DatabaseName
Tablename = $TableName
TableisHeap = $TableIsHeap
TableRowCount = $TableRowCount
IndexName = $Index.name
IndexedColumns = $IndexedColumns
Fragmentation = $Fragmentation
PageCount = $PageCount
PhysicalPartitions = $Index.PhysicalPartitions
FillFactor = $Index.FillFactor
ISclustered = $index.ISclustered
IsSystemObject = $index.IsSystemObject
SpaceUsed = $index.SpaceUsed }
$FinalResult += $ObjectIndex
$LineNumber ++
}
}
}
}
}catch {
$msg = $error[0]
Write-Warning $msg
Write-MSSQLWinEventLog "Get-MSSQLIndexInfo" 70
"Information" $MSG
continue
}
}
Write-Output $FinalResult
}
}
(This script as available for download at the top 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:
help Write-MSSQLWinEventLog
Let's see some examples in use :
##All databases and all indexes from local server
Get-MSSQLIndexInfo
##All databases and all indexes from TXT
Get-MSSQLIndexInfo C:\TEMP\Server.txt
##Select some properties
Get-MSSQLIndexInfo c:\temp\servers.txt | select servername,databasename,
tablename,tablerowcount,indexname,IndexedColumns
##Select some properties and sort
Get-MSSQLIndexInfo c:\temp\servers.txt | Sort-Object Servername,
DatabaseName, TableName,Isclustered | select servername,databasename,
tablename,indexname,IndexedColumns
##Select some properties and condition to not heap tables
Get-MSSQLIndexInfo c:\temp\servers.txt | Where-Object {!($_.TableisHeap)}
| select servername,databasename,tablename,indexname ,IndexedColumns
##Select some properties and condition to not only Clustered Indexes
Get-MSSQLIndexInfo c:\temp\servers.txt | Where-Object {!($_.TableisHeap)}
| select servername,databasename,tablename,indexname ,IndexedColumns
##Select some properties and condition to only Clustered Indexes
Get-MSSQLIndexInfo c:\temp\servers.txt | Where-Object {$_.ISclustered}
| select servername,databasename,tablename,indexname ,IndexedColumns
##Select some properties and condition to FillFactor >70
Get-MSSQLIndexInfo c:\temp\servers.txt | Where-Object {$_.fillfactor -gt 70}
| select servername,databasename,tablename,indexname ,IndexedColumns,
FillFactor
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.
###Rebuild/Reorganize Indexes
foreach ($index in Get-MSSQLIndexInfo c:\temp\servers.txt | Where-Object
{$_.Fragmentation -gt 10 -and $_.Pagecount -gt 1000}) {
if ($index.Fragmentation -lt 30)
{
$SQL = "Alter index " + $index.IndexName + " on " +
$index.TableName + " Reorganize"
Invoke-Sqlcmd -ServerInstance $index.Servername -Database
$index.Databasename -Query $sql
$SQL = "Update Statistic " + $index.TableName
Invoke-Sqlcmd -ServerInstance $index.Servername -Database
$index.Databasename -Query $sql
else
$SQL = "Alter index " + $index.IndexName + " on " +
$index.TableName + " Rebuild"
Invoke-Sqlcmd -ServerInstance $index.Servername -Database
$index.Databasename -Query $sql
}
}
#Generate a error (put one server does not exists into servers.txt)
Get-MSSQLIndexInfo C:\TEMP\Servers.txt
(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...
CREATE TABLE tblIndexInfo ( ServerName SYSNAME,
Databasename SYSNAME,
Tablename SYSNAME,
IndexName SYSNAME,
IndexedColumns VARCHAR(100),
[FillFactor] VARCHAR(100)
)
... And then execute this code:
Get-MSSQLIndexInfo c:\temp\servers.txt | select servername,databasename,
tablename,indexname ,IndexedColumns,FillFactor | Export-Csv -Path
C:\TEMP\teste.csv -Delimiter "," -NoTypeInformation $SQL = "BULK INSERT
tblIndexInfo FROM 'c:\temp\teste.csv' WITH ( firstrow = 2,
FIELDTERMINATOR =',' )" Invoke-Sqlcmd -ServerInstance $env:computername
-Database DBA -Query $sql
The result will be:
Looking good so far; let's just clean the data by removing the double quotes:
UPDATE tblIndexInfoSET
Servername = REPLACE(servername,'"',''),
Databasename = REPLACE(databasename,'"',''),
Tablename = REPLACE(tablename ,'"',''),
IndexName = REPLACE(IndexName ,'"',''),
IndexedColumns = REPLACE(IndexedColumns ,'"',''),
[Fillfactor] = REPLACE([Fillfactor] ,'"','')
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.
Function Write-MSSQLWinEventLog()
{
#requires -Version 2
<#
.SYNOPSIS
Write to Application LocalComputer Eventlog
.DESCRIPTION
Write to Application LocalComputer Eventlog
.PARAMETER Source
Mandatory String
Source EventLog
.PARAMETER EventID
Mandatory Int
EventID Eventlog
.PARAMETER EntryType
Mandatory String
Entry Type to Eventlog . Can be Error, Information Warning
.PARAMETER Message
Mandatory String
Message to Display
.LINK
www.laertejuniordba.spaces.live.com
#>
[CmdletBinding()]
Param (
[Parameter(Position=1,Mandatory=$true,
ValueFromPipelineByPropertyName=$true,HelpMessage="Source")]
[Alias("SourceName")]
[String] $Source ,
[Parameter(Position=2,Mandatory=$true,
ValueFromPipelineByPropertyName=$true,HelpMessage="EventID")]
[Alias("EventIDNumber")]
[int] $EventID ,
[Parameter(Position=3,Mandatory=$true,
ValueFromPipelineByPropertyName=$true,HelpMessage="EntryType")]
[Alias("EntryTypeString")]
[ValidateScript({$_ -match "Error|Warning|Information"})]
[string] $EntryType ,
[Parameter(Position=4,Mandatory=$true,
ValueFromPipelineByPropertyName=$true,HelpMessage="Message")]
[Alias("MessageString")]
[string] $Message
)
Process
{
if (!(test-path
"HKLM:\SYSTEM\CurrentControlSet\Services\Eventlog\Application\$Source"))
{
[System.Diagnostics.EventLog]::
CreateEventSOurce($Source,"Application")
}
Write-EventLog -computername $env:computername -logname Application
-source $source -eventID $eventid
-entrytype $EntryType -message $Message
-ErrorAction SilentlyContinue
}
}
Function Save-MSSQLMsg ()
{
#requires -Version 2
<#
.SYNOPSIS
Save log in file
.PARAMETER NamePS1
File Name
.PARAMETER Server
Server name
.PARAMETER databasename
DatabaseName
.PARAMETER Message
Message To Log
.PARAMETER PathFileLog
Path to generate file
Default = C:\temp
.PARAMETER TodayDate
Date to Log
#>
[CmdletBinding()]
Param (
[Parameter(position=1,Mandatory = $true )][String] $NamePS1,
[Parameter(position=2,Mandatory = $true )][String] $Server,
[Parameter(position=3,Mandatory = $false )][String] $DatabaseName = "",
[Parameter(position=4,Mandatory = $false )][String] $Message = "" ,
[Parameter(position=5,Mandatory = $false )][String] $PathFileLog =
"C:\temp",
[Parameter(position=6,Mandatory = $false )][String] $TodayDate =
(Get-Date -Format "yyyyMMddhhmmss")
)
process
{
#test if path wich will contains the error file exists. if not create
if (!(Test-Path -path $PathFileLog))
{
try {
New-Item $PathFileLog -itemtype directory -ErrorAction Stop
| Out-Null
}
catch {
Write-Host "Can not create log file path"
break;
}
}
$NameFileFull = Join-Path $PathFileLog "$NamePS1$TodayDate.log"
$TDate = $TodayDate.Substring(0,4) + "-" + $TodayDate.Substring(4,2)
+ "-" + $TodayDate.Substring(6,2)
"Server : " + $Server + " Database : " + $DatabaseName + " Date : "
+ $TDate + " Message: " + $Message | Out-file $NameFileFull -append
}
}
Function Set-MSSQLCollation ()
{
#Requires Powershell 2.0
<#
.SYNOPSIS
Set up New Collation
.DESCRIPTION
Set up New Collation
Version 1.0
Laerte Poltronieri Junior
www.laertejuniordba.spaces.live.com
.PARAMETER Servername
Mandatory String
Server name
.PARAMETER DatabaseName
Mandatory String
Database Name
.PARAMETER Collation
Mandatory String
New Collation
.LINK
www.laertejuniordba.spaces.live.com
.EXAMPLE
Set-MSSQLCollation Server1 Database1 SQL_Latin1_General_CP1_CI_AS
#>
[CmdletBinding()]
PARAM (
[Parameter(Position=1,Mandatory=$False,
ValueFromPipelineByPropertyName=$true,HelpMessage="ServerName")]
[Alias("FullServername")]
[String] $ServerName ,
[Parameter(Position=2,Mandatory=$False,
ValueFromPipelineByPropertyName=$true,HelpMessage="DatabaseName")]
[Alias("FullDatabasename")]
[String] $DatabaseName ,
[Parameter(Position=3,Mandatory=$False,
ValueFromPipelineByPropertyName=$true,HelpMessage="NewCollation")]
[Alias("NewCollation")]
[String] $Collation
)
Begin
{
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
| out-null
}
Process
{
Try
{
$verbosePreference="continue"
$TodayDate = get-date -format "yyyy-MM-dd hh:mm:ss"
$TodayDateErr = get-date -format "yyyyMMddhhmmss"
$Error.Clear()
$Server=New-Object Microsoft.SqlServer.Management.Smo.Server
"$ServerName"
$Server.Databases | where-object {$_.IsSystemObject -eq $FALSE
-and $_.IsAccessible -eq $TRUE -and $_.Name -eq $Databasename }
| foreach {
foreach ($table in $Server.Databases[$_.name].tables ) {
$db = $Server.Databases[$_.name]
if (!$table.IsSystemObject)
{
$tableName = $table.name
$table.columns | Where-Object {$_.datatype -match
"char|varchar|nvarchar|text|ntext|nchar"} | foreach {
$_.Collation = $Collation
Try
{
$_.Alter()
} Catch {
$Err = " Table $tableName $Error"
Save-MSSQLMsg "Set-MSSQLCollation"
"$ServerName" "$DatabaseName" "$Err"
"C:\TEMP" "$TodayDateErr"
} Finally {
Continue
}
}
}
}
}
} catch {
$Err = $Error[0]
Write-MSSQLWinEventLog "Get-MSSQLIndexInfo" 70 "ERROR" $MSG
break;
}
}
}
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:
#Set up New Collation
Set-MSSQLCollation $Env:computername "DBA" "SQL_Latin1_General_CP1_CI_AI"
... 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.
Summary
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
Shay Levy
If you repeat it, $CRIPT it!
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
