{"id":804,"date":"2010-02-11T00:00:00","date_gmt":"2010-02-11T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/exceptional-powershell-dba-pt-3-collation-and-fragmentation\/"},"modified":"2021-08-24T13:40:31","modified_gmt":"2021-08-24T13:40:31","slug":"exceptional-powershell-dba-pt-3-collation-and-fragmentation","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/exceptional-powershell-dba-pt-3-collation-and-fragmentation\/","title":{"rendered":"Exceptional PowerShell DBA Pt 3 – Collation and Fragmentation"},"content":{"rendered":"
\n

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.<\/p>\n

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<\/em> in collecting historical data on periods of fragmentation for each index, thus helping us set appropriate fillfactors<\/p>\n

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:<\/p>\n

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

Checking Fragmentation, and Rebuilding\/Reorganizing Indexes<\/h2>\n

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<\/a>):<\/p>\n

Function Write-MSSQLWinEventLog()\r\n{\r\n\r\n    #requires -Version 2\r\n    \r\n    <#\r\n        .SYNOPSIS\r\n        Write to Application LocalComputer Eventlog\r\n        .DESCRIPTION\r\n        Write to Application LocalComputer Eventlog\r\n        .PARAMETER Source\r\n        Mandatory String\r\n        Source EventLog\r\n        .PARAMETER EventID\r\n        Mandatory Int\r\n        EventID Eventlog\r\n        .PARAMETER EntryType\r\n        Mandatory String\r\n        Entry Type to Eventlog . Can be Error, Information Warning\r\n        .PARAMETER Message\r\n        Mandatory String\r\n        Message to Display\r\n        .LINK\r\n        www.laertejuniordba.spaces.live.com\r\n        \r\n    #>\r\n    [CmdletBinding()]\r\n    \r\n    Param (\r\n    \r\n    [Parameter(Position=1,Mandatory=$true, \r\n            ValueFromPipelineByPropertyName=$true,HelpMessage=\"Source\")]\r\n    [Alias(\"SourceName\")]\r\n    [String] $Source ,\r\n\r\n    [Parameter(Position=2,Mandatory=$true, \r\n            ValueFromPipelineByPropertyName=$true,HelpMessage=\"EventID\")]\r\n    [Alias(\"EventIDNumber\")]\r\n    [int] $EventID ,\r\n    \r\n    [Parameter(Position=3,Mandatory=$true, \r\n            ValueFromPipelineByPropertyName=$true,HelpMessage=\"EntryType\")]\r\n    [Alias(\"EntryTypeString\")]\r\n    [ValidateScript({$_ -match \"Error|Warning|Information\"})]\r\n    [string] $EntryType ,    \r\n\r\n    [Parameter(Position=4,Mandatory=$true, \r\n            ValueFromPipelineByPropertyName=$true,HelpMessage=\"Message\")]\r\n    [Alias(\"MessageString\")]\r\n    [string] $Message \r\n\r\n\r\n    )\r\n    Process\r\n    {\r\n\r\n\r\n        if (!(test-path \r\n   \"HKLM:\\SYSTEM\\CurrentControlSet\\Services\\Eventlog\\Application\\$Source\"))\r\n        {\r\n            [System.Diagnostics.EventLog]::CreateEventSOurce($Source,\r\n                                                             \"Application\")\r\n        }    \r\n        \r\n        Write-EventLog -computername $env:computername -logname Application \r\n                       -source $source  -eventID $eventid \r\n                       -entrytype $EntryType -message $Message \r\n                       -ErrorAction SilentlyContinue\r\n    }    \r\n}    \r\n\r\nFunction Get-MSSQLIndexInfo ()    \r\n{\r\n    #Requires Powershell 2.0\r\n    \r\n    <#\r\n        .SYNOPSIS\r\n        Returns information about index\r\n        \r\n        .DESCRIPTION\r\n        Returns information about index\r\n        Version 1.0\r\n        Laerte Poltronieri Junior\r\n        www.laertejuniordba.spaces.live.com\r\n        \r\n        .PARAMETER TXTServersList\r\n        Optional String\r\n        Full SQL Server file list\r\n        \"C:\\<path>\\<FileName>.txt\"\r\n        If not informed, the current server is used.\r\n        \r\n        .LINK\r\n        www.laertejuniordba.spaces.live.com\r\n        \r\n        .EXAMPLE\r\n        Get-MSSQLIndexInfo \r\n        Get-MSSQLIndexInfo C:\\Servers.txt\r\n        \r\n    #>\r\n    \r\n    [CmdletBinding()]\r\n    \r\n    PARAM    (    \r\n    \r\n            [Parameter(Position=1,Mandatory=$False, \r\n     ValueFromPipelineByPropertyName=$true,HelpMessage=\"SQL Servers File\")]\r\n            [Alias(\"FullNameTXT\")]\r\n            [String] $TXTServersList = $env:COMPUTERNAME\r\n            )\r\n    Begin\r\n    {\r\n      [reflection.assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Smo\")\r\n                               | out-null \r\n    }    \r\n\r\n    Process\r\n    {\r\n        $verbosePreference=\"continue\" \r\n        $TodayDate = get-date -format \"yyyy-MM-dd hh:mm:ss\" \r\n        $Error.Clear()\r\n\r\n        if ($TXTServersList.substring($TXTServersList.length -4,4) \r\n            -eq \".TXT\")\r\n        {\r\n            try\r\n            {\r\n                $ServersList = get-content $TXTServersList    \r\n            } catch {\r\n                        $msg = $error[0]\r\n                        Write-Warning -Message $msg    \r\n                        Write-MSSQLWinEventLog \"Get-MSSQLIndexInfo\" 70 \r\n                                               \"ERROR\" $MSG\r\n                        break;\r\n            }\r\n        }    \r\n        else\r\n        {\r\n            $ServersList = $TXTServersList\r\n        }    \r\n        \r\n        $LineNumber = 1\r\n        $FinalResult = @()\r\n    \r\n        foreach ($svr in  $ServersList )\r\n        {\r\n        \r\n            try\r\n            {\r\n        \r\n                $Server=New-Object \r\n                         \"Microsoft.SqlServer.Management.Smo.Server\" \"$svr\"\r\n                $Server.Databases   | where-object {(!$_.IsSystemObject)  \r\n                          -and $_.IsAccessible -and $_.name -eq \"DBA\" } | \r\n                          foreach {\r\n                  $DatabaseName = $_.name\r\n                  foreach ($tables in $Server.Databases[$_.name].tables ){\r\n                        if (!$tables.IsSystemObject)\r\n                        {\r\n                            $TableName = $tables.name\r\n                            $TableRowCount = $tables.rowcount\r\n                            $TableIsHeap = !($tables.hasclusteredindex)\r\n                            foreach ($index in $tables.indexes)\r\n                            { \r\n                                $Enum = $index.EnumFragmentation(3) \r\n                                $Fragmentation = \r\n                                        $enum.rows[0].AverageFragmentation\r\n                                $PageCount = $enum.rows[0].Pages\r\n                                [String] $IndexedColumns = \r\n                                           $Index.IndexedColumns\r\n\r\n                                $ObjectIndex = New-Object PSObject\r\n                                               -Property @{\r\n                             LineNumber         = $LineNumber\r\n                             Date               = $TodayDate\r\n                             ServerName         = $Server.Name\r\n                             Databasename       = $DatabaseName\r\n                             Tablename          = $TableName\r\n                             TableisHeap        = $TableIsHeap\r\n                             TableRowCount      = $TableRowCount\r\n                             IndexName          = $Index.name\r\n                             IndexedColumns     = $IndexedColumns\r\n                             Fragmentation      = $Fragmentation\r\n                             PageCount          = $PageCount\r\n                             PhysicalPartitions = $Index.PhysicalPartitions\r\n                             FillFactor         = $Index.FillFactor\r\n                             ISclustered        = $index.ISclustered\r\n                             IsSystemObject     = $index.IsSystemObject\r\n                             SpaceUsed          = $index.SpaceUsed }\r\n                             $FinalResult += $ObjectIndex\r\n                             $LineNumber ++ \r\n                                    \r\n                                \r\n                            }    \r\n                        }\r\n                    }\r\n                \r\n                }    \r\n            }catch {\r\n                    $msg = $error[0]\r\n                    Write-Warning $msg \r\n                    Write-MSSQLWinEventLog \"Get-MSSQLIndexInfo\" 70 \r\n                                           \"Information\" $MSG\r\n                    continue\r\n            \r\n            }\r\n    \r\n        }    \r\n        Write-Output $FinalResult                \r\n    }\r\n}    <\/pre>\n
(This script as available for download at the bottom of this article.)<\/p>\n
As I mentioned, and as you can see above, the write-MSSQLWinEventLog <\/strong>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:<\/p>\n
help Write-MSSQLWinEventLog<\/pre>\n
Let’s see some examples in use :<\/p>\n
##All databases and all indexes from local server\r\nGet-MSSQLIndexInfo \r\n\r\n##All databases and all indexes from TXT\r\nGet-MSSQLIndexInfo C:\\TEMP\\Server.txt\r\n\r\n##Select some properties \r\nGet-MSSQLIndexInfo c:\\temp\\servers.txt | select servername,databasename,tablename,tablerowcount,indexname,IndexedColumns\r\n\r\n##Select some properties and sort \r\nGet-MSSQLIndexInfo c:\\temp\\servers.txt | Sort-Object Servername,DatabaseName, TableName,Isclustered   | select servername,databasename,tablename,indexname,IndexedColumns \r\n\r\n##Select some properties and condition to not heap tables \r\nGet-MSSQLIndexInfo c:\\temp\\servers.txt | Where-Object {!($_.TableisHeap)}| select servername,databasename,tablename,indexname ,IndexedColumns \r\n\r\n##Select some properties and condition to not only Clustered Indexes\r\nGet-MSSQLIndexInfo c:\\temp\\servers.txt | Where-Object {!($_.TableisHeap)}| select servername,databasename,tablename,indexname ,IndexedColumns \r\n\r\n##Select some properties and condition to  only Clustered Indexes\r\nGet-MSSQLIndexInfo c:\\temp\\servers.txt | Where-Object {$_.ISclustered}| select servername,databasename,tablename,indexname ,IndexedColumns \r\n\r\n##Select some properties and condition to FillFactor >70\r\nGet-MSSQLIndexInfo c:\\temp\\servers.txt | Where-Object {$_.fillfactor -gt 70}| select servername,databasename,tablename,indexname ,IndexedColumns,FillFactor\r\n<\/pre>\n
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<\/a>.<\/p>\n
Rebuilding \/ Reorganizing Indexes<\/h2>\n
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.<\/p>\n
###Rebuild\/Reorganize Indexes\r\nforeach ($index in Get-MSSQLIndexInfo c:\\temp\\servers.txt | Where-Object \r\n         {$_.Fragmentation -gt 10 -and $_.Pagecount -gt 1000}) {\r\n\r\n    if ($index.Fragmentation -lt 30)    \r\n    {\r\n        $SQL = \"Alter index \"  + $index.IndexName + \" on \" +  \r\n               $index.TableName + \"  Reorganize\"\r\n        \r\n        Invoke-Sqlcmd -ServerInstance $index.Servername -Database  \r\n        $index.Databasename -Query $sql\r\n        \r\n        $SQL = \"Update Statistic \"  +  $index.TableName \r\n\r\n\r\n        Invoke-Sqlcmd -ServerInstance $index.Servername -Database \r\n        $index.Databasename -Query $sql\r\n     else\r\n\r\n        $SQL = \"Alter index \"  + $index.IndexName + \" on \" +  \r\n               $index.TableName + \"  Rebuild\"\r\n\r\n        Invoke-Sqlcmd -ServerInstance $index.Servername -Database  \r\n        $index.Databasename -Query $sql\r\n\r\n    }\r\n}\r\n\r\n#Generate a error (put one server does not exists into servers.txt)\r\nGet-MSSQLIndexInfo C:\\TEMP\\Servers.txt\r\n<\/pre>\n
(This script as available for download at the top of this article.)<\/p>\n
Just remember, if you rebuild the clustered indexes, the non clustered ones are not<\/em> 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<\/a>.<\/p>\n
Output to SQL Server Table<\/h2>\n
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?<\/p>\n
When we were looking for information about our indexes, we selected the servername<\/strong>, databasename<\/strong>, tablename<\/strong>, indexname<\/strong>, IndexedColumns<\/strong> and FillFactor<\/strong> properties, so we’ll need to create a table to receive this data…<\/p>\n
CREATE TABLE tblIndexInfo ( ServerName SYSNAME,\r\nDatabasename SYSNAME,\r\nTablename SYSNAME,\r\nIndexName SYSNAME,\r\nIndexedColumns VARCHAR(100),\r\n[FillFactor] VARCHAR(100)\r\n)\r\n    <\/pre>\n
… And then execute this code:<\/p>\n
Get-MSSQLIndexInfo c:\\temp\\servers.txt | select servername,databasename,\r\ntablename,indexname ,IndexedColumns,FillFactor | Export-Csv -Path \r\nC:\\TEMP\\teste.csv -Delimiter \",\" -NoTypeInformation $SQL = \"BULK INSERT \r\ntblIndexInfo   FROM 'c:\\temp\\teste.csv'  WITH   ( firstrow = 2,   \r\nFIELDTERMINATOR =',' )\" Invoke-Sqlcmd -ServerInstance $env:computername \r\n-Database  DBA  -Query $sql\r\n<\/pre>\n
The result will be:<\/p>\n
\"944-SQL_output1.jpg\"<\/p>\n
Looking good so far; let’s just clean the data by removing the double quotes:<\/p>\n
UPDATE tblIndexInfoSET \r\nServername = REPLACE(servername,'\"',''),\r\nDatabasename = REPLACE(databasename,'\"',''),\r\nTablename = REPLACE(tablename ,'\"',''),\r\nIndexName = REPLACE(IndexName ,'\"',''),\r\nIndexedColumns = REPLACE(IndexedColumns ,'\"',''),\r\n[Fillfactor] = REPLACE([Fillfactor] ,'\"','')\r\n<\/pre>\n
And we’ve finished the job :<\/p>\n
\"944-SQL_output2.jpg\"<\/p>\n
Error Handling with EventLog<\/h2>\n
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.<\/p>\n
To demonstrate, let’s create an error situation; I’ll execute the Get-MSSQLIndexInfo<\/strong> 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.<\/p>\n
\"944-PowerShell_ExceptionWarning.jpg\"<\/p>\n
\"944-PowerShell_ErrorView.jpg\"<\/p>\n
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.<\/p>\n
Change columns’ collation<\/h2>\n
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”.<\/p>\n
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<\/em> 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<\/em> of confidence are the same thing, and partly because haste is the enemy of perfection. Anyway, let me share one more scene with you:<\/p>\n
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:<\/p>\n
“Laerte, my dear DBA, when you’re free, I need a big favor.”<\/strong>  – <\/em>I look to the side with wide, frightened eyes, thinking “it’s a trap!”<\/em> “Could you help the people from the General Support Department to make a change collation? Because they do need lots of help… ” <\/strong> – With a heart full of relief and eyes full of joy, I answer: “<\/em>Yes my dear, benevolent, caring boss.<\/em>” I love my boss *Sniff sniff<\/em>*<\/p>\n
In this case we will work with both the two error-handling processes I’ve demonstrated across this series of articles (MSSQLWinEventLog<\/strong> and MSSQLMsg<\/strong>). 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<\/em> 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.<\/p>\n
Function Write-MSSQLWinEventLog()\r\n{\r\n\r\n    #requires -Version 2\r\n    \r\n    <#\r\n        .SYNOPSIS\r\n        Write to Application LocalComputer Eventlog\r\n        .DESCRIPTION\r\n        Write to Application LocalComputer Eventlog\r\n        .PARAMETER Source\r\n        Mandatory String\r\n        Source EventLog\r\n        .PARAMETER EventID\r\n        Mandatory Int\r\n        EventID Eventlog\r\n        .PARAMETER EntryType\r\n        Mandatory String\r\n        Entry Type to Eventlog . Can be Error, Information Warning\r\n        .PARAMETER Message\r\n        Mandatory String\r\n        Message to Display\r\n        .LINK\r\n        www.laertejuniordba.spaces.live.com\r\n        \r\n    #>\r\n    [CmdletBinding()]\r\n    \r\n    Param (\r\n    \r\n    [Parameter(Position=1,Mandatory=$true, \r\n            ValueFromPipelineByPropertyName=$true,HelpMessage=\"Source\")]\r\n    [Alias(\"SourceName\")]\r\n    [String] $Source ,\r\n\r\n    [Parameter(Position=2,Mandatory=$true, \r\n            ValueFromPipelineByPropertyName=$true,HelpMessage=\"EventID\")]\r\n    [Alias(\"EventIDNumber\")]\r\n    [int] $EventID ,\r\n    \r\n    [Parameter(Position=3,Mandatory=$true, \r\n            ValueFromPipelineByPropertyName=$true,HelpMessage=\"EntryType\")]\r\n    [Alias(\"EntryTypeString\")]\r\n    [ValidateScript({$_ -match \"Error|Warning|Information\"})]\r\n    [string] $EntryType ,    \r\n\r\n    [Parameter(Position=4,Mandatory=$true,\r\n            ValueFromPipelineByPropertyName=$true,HelpMessage=\"Message\")]\r\n    [Alias(\"MessageString\")]\r\n    [string] $Message \r\n\r\n\r\n    )\r\n    Process\r\n    {\r\n\r\n\r\n        if (!(test-path \r\n   \"HKLM:\\SYSTEM\\CurrentControlSet\\Services\\Eventlog\\Application\\$Source\"))\r\n        {\r\n            [System.Diagnostics.EventLog]::\r\n                                  CreateEventSOurce($Source,\"Application\")\r\n        }    \r\n        \r\n        Write-EventLog -computername $env:computername -logname Application\r\n                       -source $source  -eventID $eventid \r\n                       -entrytype $EntryType -message $Message \r\n                       -ErrorAction SilentlyContinue\r\n    }    \r\n}    \r\n\r\nFunction Save-MSSQLMsg () \r\n{\r\n    #requires -Version 2\r\n    \r\n    <#\r\n        .SYNOPSIS\r\n        Save  log in file\r\n        .PARAMETER NamePS1\r\n        File Name \r\n        .PARAMETER Server\r\n        Server name\r\n        .PARAMETER databasename\r\n        DatabaseName\r\n        .PARAMETER Message\r\n        Message To Log\r\n        .PARAMETER PathFileLog\r\n        Path to generate file \r\n        Default = C:\\temp\r\n        .PARAMETER TodayDate\r\n        Date to Log\r\n    #>\r\n\r\n\r\n\r\n    [CmdletBinding()]\r\n    \r\n    Param (\r\n    [Parameter(position=1,Mandatory = $true )][String] $NamePS1,\r\n    [Parameter(position=2,Mandatory = $true )][String] $Server,\r\n    [Parameter(position=3,Mandatory = $false )][String] $DatabaseName = \"\",\r\n    [Parameter(position=4,Mandatory = $false )][String] $Message = \"\" ,\r\n    [Parameter(position=5,Mandatory = $false )][String] $PathFileLog = \r\n                                                                 \"C:\\temp\",\r\n    [Parameter(position=6,Mandatory = $false )][String] $TodayDate = \r\n                                        (Get-Date -Format \"yyyyMMddhhmmss\")\r\n    )\r\n    process \r\n    {\r\n    \r\n      #test if path wich will contains the error file exists. if not create \r\n        \r\n        if (!(Test-Path -path $PathFileLog))\r\n        {\r\n            try {\r\n               New-Item $PathFileLog -itemtype directory -ErrorAction Stop\r\n                                      | Out-Null\r\n            }\r\n            catch {\r\n                Write-Host \"Can not create log file path\"\r\n                break;\r\n            }\r\n       } \r\n    \r\n    \r\n       $NameFileFull = Join-Path $PathFileLog \"$NamePS1$TodayDate.log\" \r\n        \r\n       $TDate = $TodayDate.Substring(0,4) + \"-\" + $TodayDate.Substring(4,2)\r\n                 + \"-\"     + $TodayDate.Substring(6,2) \r\n        \r\n       \"Server : \" + $Server + \" Database : \" + $DatabaseName + \" Date : \"\r\n       + $TDate + \" Message: \" + $Message | Out-file $NameFileFull -append \r\n    } \r\n}\r\n\r\nFunction Set-MSSQLCollation ()    \r\n{\r\n    #Requires Powershell 2.0\r\n    \r\n    <#\r\n        .SYNOPSIS\r\n        Set up New Collation\r\n        \r\n        .DESCRIPTION\r\n        Set up New Collation\r\n        Version 1.0\r\n        Laerte Poltronieri Junior\r\n        www.laertejuniordba.spaces.live.com\r\n        \r\n        .PARAMETER Servername\r\n        Mandatory String\r\n        Server name\r\n                \r\n        .PARAMETER DatabaseName\r\n        Mandatory String\r\n        Database Name\r\n        \r\n        .PARAMETER Collation\r\n        Mandatory String\r\n        New Collation\r\n        \r\n        .LINK\r\n        www.laertejuniordba.spaces.live.com\r\n        \r\n        .EXAMPLE\r\n        Set-MSSQLCollation Server1 Database1 SQL_Latin1_General_CP1_CI_AS\r\n        \r\n        \r\n    #>\r\n    \r\n    [CmdletBinding()]\r\n    \r\n    PARAM  (    \r\n    \r\n           [Parameter(Position=1,Mandatory=$False, \r\n         ValueFromPipelineByPropertyName=$true,HelpMessage=\"ServerName\")]\r\n           [Alias(\"FullServername\")]\r\n           [String] $ServerName ,\r\n           \r\n           [Parameter(Position=2,Mandatory=$False, \r\n         ValueFromPipelineByPropertyName=$true,HelpMessage=\"DatabaseName\")]\r\n           [Alias(\"FullDatabasename\")]\r\n           [String] $DatabaseName ,\r\n           \r\n           [Parameter(Position=3,Mandatory=$False, \r\n         ValueFromPipelineByPropertyName=$true,HelpMessage=\"NewCollation\")]\r\n           [Alias(\"NewCollation\")]\r\n           [String] $Collation \r\n            \r\n            \r\n            )\r\n    Begin\r\n    {\r\n      [reflection.assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Smo\")\r\n                               | out-null \r\n    }    \r\n\r\n    Process\r\n    {\r\n    \r\n        Try\r\n        {\r\n            $verbosePreference=\"continue\" \r\n            $TodayDate = get-date -format \"yyyy-MM-dd hh:mm:ss\" \r\n            $TodayDateErr = get-date -format \"yyyyMMddhhmmss\" \r\n            $Error.Clear()\r\n    \r\n            $Server=New-Object Microsoft.SqlServer.Management.Smo.Server \r\n                               \"$ServerName\"\r\n            \r\n        \r\n            $Server.Databases | where-object {$_.IsSystemObject -eq $FALSE\r\n            -and $_.IsAccessible -eq $TRUE -and $_.Name -eq $Databasename }\r\n            | foreach {\r\n                foreach  ($table in $Server.Databases[$_.name].tables ) {\r\n                    $db = $Server.Databases[$_.name]\r\n                    if (!$table.IsSystemObject)\r\n                    {\r\n                      $tableName = $table.name\r\n                                            \r\n                      $table.columns | Where-Object {$_.datatype -match \r\n                      \"char|varchar|nvarchar|text|ntext|nchar\"} | foreach {\r\n                    \r\n                            $_.Collation = $Collation\r\n                            Try\r\n                            {\r\n                                $_.Alter()\r\n                            } Catch {\r\n                                $Err = \" Table $tableName  $Error\"\r\n                                Save-MSSQLMsg \"Set-MSSQLCollation\" \r\n                                \"$ServerName\" \"$DatabaseName\" \"$Err\" \r\n                                \"C:\\TEMP\" \"$TodayDateErr\"\r\n                            } Finally {\r\n                                Continue\r\n                            }    \r\n                        }\r\n                    }\r\n                }\r\n            }\r\n        } catch {\r\n            $Err = $Error[0]\r\n            Write-MSSQLWinEventLog \"Get-MSSQLIndexInfo\" 70 \"ERROR\" $MSG\r\n            break;\r\n        }\r\n    }\r\n}   <\/pre>\n
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:<\/p>\n
#Set up New Collation\r\nSet-MSSQLCollation $Env:computername \"DBA\" \"SQL_Latin1_General_CP1_CI_AI\"<\/pre>\n
… 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.<\/p>\n
\"944-PowerShell_Log.jpg\"<\/p>\n
Summary<\/h2>\n
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<\/em> to be in the curriculum of production DBAs.<\/p>\n
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<\/strong>, 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<\/a>.<\/p>\n
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<\/a>. 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<\/em> be Exceptional DBAs.<\/p>\n
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.<\/p>\n
Related Links<\/h2>\n
Jeffery Hicks<\/strong> The Lonely Administrator<\/a><\/strong><\/p>\n
Chad Miller<\/strong> http:\/\/chadwickmiller.spaces.live.com\/<\/strong><\/a> CODEPLEX – SQLPSX SQL Server PowerShell EXtensions<\/strong><\/a><\/p>\n
Jonathan Medd<\/strong> http:\/\/www.jonathanmedd.net\/<\/strong><\/a><\/p>\n
Steven Murawski<\/strong> Use PowerShell<\/a><\/strong><\/p>\n
Scripting Guys<\/strong> Technet Script Center<\/a><\/strong><\/p>\n
MSDN<\/strong> Windows PowerShell Getting Started Guide<\/a><\/strong><\/p>\n
Window PowerShell Team Blog<\/strong><\/a><\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
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.…<\/p>\n","protected":false},"author":221715,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[5116,4170,5024,5115,5065,4635,4150,4151],"coauthors":[6819],"class_list":["post-804","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-collation","tag-database-administration","tag-exceptional-dba","tag-fragmentation","tag-indexes","tag-powershell","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/804","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221715"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=804"}],"version-history":[{"count":16,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/804\/revisions"}],"predecessor-version":[{"id":73720,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/804\/revisions\/73720"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=804"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=804"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=804"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=804"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}