PowerShell’s cmdlets can be pretty powerful on their own, but that’s only part of the power of PowerShell. The .NET Framework is the basis of the PowerShell environment. As such, you have a vast library of operations at your disposal. Learn to use its abilities and save the day without needing a monkey with a bucket.
I want to thank the local PASS chapter HASSUG, the organizers Nancy Wilson (blog | twitter), Jonathan Gardner (blog | twitter), Malik Al-Amin (twitter), the volunteers, the other speakers, and the attendees. It was a great event with a great venue.
I especially want to thank those attendees who chose my sessions out of the many other great ones.
Those of us admins that download each SQL Cumulative Update when it comes out may find this script handy. When you request a CU from Microsoft, you get this plain text email with links and passwords. Each link is to a different self-extracting zip file with the corresponding password below it.
When a CU has many files, it becomes a long series of point-and-click to download them all. Then you have to manually put in the password to each file in order to extract it. I wanted an easier solution.
I was playing around with BITS transferring backups between servers and discovered that BITS has PowerShell cmdlets. I was previously using the BITSAdmin Tool which was easy, but I wanted to find out more about the PoSh cmdlets. After reading the docs, I decided to use these cmdlets in my MCM video download script.
BITS stands for Background Intelligent Transfer Service. It is a service built into windows since XP designed to transfer files in the background without interrupting normal user network usage. Windows Update uses BITS to download all the patches that need to be applied to your computer. As stated above, you too can also use BITS to transfer files around. If you regularly transfer large files over the network and get annoyed by how it affects your computer experience, look into replacing the normal copy, xcopy, robocopy commands with it.
My first attempt queued all the files into one BITS transfer. The upside is only one BITS job to manage. The downside is the filenames aren’t written until the whole job completes. Long download jobs often get cancelled and the user would want to be able to listen to or watch the files that had already finished. I wanted the opposite, so I went for one job per file.
I wanted a way to parse through my SQL logs and be able to look at one particular spid or ProcessInfo class such as Server, Backup, Logon, etc. I came up with the following PowerShell script: *You must have either trace flag 1204 and/or 1222 turned on. (More info)
If you dump your log file via the following command, this works really well. sqlcmd -S MyServer\Inst1 -Q "xp_ReadErrorLog" -o myserver.log
Once you have it, just call the script and specify the -Path parameter as such: & '.\Split SQL Log by ProcessInfo.ps1' -Path C:\path\to\myserver.log
It looks at each line and figures either the spid or processinfo class and puts the line into a file with that string in its name. If it sees a blank line, it will put it into the previous file. WARNING: This could result any many files.
Yes, you could dump this to SQL or parse this some other way, but I wanted to split it into files so I can run it through another process to get all deadlock information that I’ll be posting later.
Microsoft has changed their SQL MCM program to allow more people access to the course and certification. In doing so, they are recording much of the classroom training that was previously required and putting it up on TechNet. We’re talking close to 60 videos already. Sure, you could stream them one at a time, but I wanted to be able to download them all and watch them offline without a bunch of clicking. So I built the below PowerShell scripts. They’re really simple to use, and only have one external requirement that makes dealing with web pages much easier.
You’ll need to download the Html Agility Pack library from here.
After watching Paul Randal’s (blog | twitter) MCM video on log files, I wanted to find a quick way to get VLF (Virtual Log File) statistics across multiple databases and servers. I went two routes, PowerShell and T-SQL. Our internal monitoring uses T-SQL already, so I wanted to be able to easily work with the existing framework. I wanted to create a PowerShell version, as some others might find it useful and I just like working with PowerShell.
We have several active / active clusters with multiple instances per node in our environment. We want to ensure the instances are spread as evenly as possible across the nodes. I wrote a quick PowerShell script to go through a text file and get the physical node where the instance is running, how much physical memory the node has and how much memory the instance is configured to use.
Just create a text file “servers.txt” with the list of instances on a cluster and run the following.