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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 |
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 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:
|
1 |
help Write-MSSQLWinEventLog |
Let’s see some examples in use :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
##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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
###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…
|
1 2 3 4 5 6 7 |
CREATE TABLE tblIndexInfo ( ServerName SYSNAME, Databasename SYSNAME, Tablename SYSNAME, IndexName SYSNAME, IndexedColumns VARCHAR(100), [FillFactor] VARCHAR(100) ) |
… And then execute this code:
|
1 2 3 4 5 6 |
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:
|
1 2 3 4 5 6 7 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 |
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:
|
1 2 |
#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
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
Load comments