Skip to content

Instantly share code, notes, and snippets.

Avatar

nanoDBA nanoDBA

View GitHub Profile
@nanoDBA
nanoDBA / Get-SqlAgentHistoryDetailsConsole.ps1
Created March 23, 2023 16:37
Console Output RED/GREEN failures for SQL Agent job history, multiple key property sorts. Attempting to imitate the output of the SQL Server Management Studio Job History window in a console.
View Get-SqlAgentHistoryDetailsConsole.ps1
# Console Output RED/GREEN failures for SQL Agent job history, multiple key property sorts
# attempting to imitate the output of the SQL Server Management Studio Job History window
$paramHash = @{
SqlInstance = 'YOURSERVER01','YOURSERVER02' # comma separated list of SQL Server instances
StartDate = "$(((Get-Date).AddDays(-.1) ))" # 0.1 days ago - ARE YOU SURE? This does not mean 1 day. It means 2.4 hours ago.
# StartDate = "$(((Get-Date).AddDays(-90) ))" # 90 days ago
EndDate = "$((Get-Date ))" # now
Job = 'Some SQL Agent Job Name goes here' # name of SQL Agent job goes here
ExcludeJobSteps = $false
@nanoDBA
nanoDBA / Proposed SQL Server Patching Approach.txt
Last active February 26, 2023 19:37
An approach I've been using for the last 4 years or so to deploy SQL Server Cumulative Updates
View Proposed SQL Server Patching Approach.txt
Proposed SQL Server Patching Approach
We do not patch to the absolute latest cumulative update(CU).
Rather, we strive to patch to the cumulative update(CU) that
was released without any intermediate releases(such as hotfixes)
between it and the most recent CU. Basically N-1 as long as
there aren't any hotfixes. If there were hotfixes then we'll wait
until two CU subsequent releases have occurred
without hotfixes in between the two to deploy the CU.
@nanoDBA
nanoDBA / Remove-SoundAndStillness.ps1
Last active February 18, 2023 06:05
FFmpeg Remove video frames without motion and remove audio track
View Remove-SoundAndStillness.ps1
$sourceFrameRate = 10.74
$filename = "C:\Users\kilroy\2023-02-17_17-37-36_boxstarter_2_of_2.mp4"
$newFilename = "$(dir $filename | foreach { Join-Path -Path $_.Directory -ChildPath $_.BaseName })_remove_nonmotion_mute_audio.mp4"
ffmpeg.exe -i "$($filename)" -vf "select=gt(scene\,0.0002),setpts=N/($($sourceFrameRate)*TB)" -an "$($newFilename)"
<# -an remove audio #>
<# -vf "select=gt(scene\,0.001),setpts=N/(<framerategoeshere>*TB)" #>
<# https://superuser.com/questions/984841/ffmpeg-remove-parts-without-motion/1029175#1029175
View 2021-boxstarter-package-Lars_2023-02-16.ps1
<#
.SYNOPSIS
BoxStarter script to configure Windows 10 development PC.
.DESCRIPTION
You might need to set:
Set-ExecutionPolicy RemoteSigned
Set-ExecutionPolicy Unrestricted
Set-ExecutionPolicy Bypass
@nanoDBA
nanoDBA / Get-Process-SSMSUsers.ps1
Created February 6, 2023 22:50
Shows users currently using process Ssms.exe in a loop executing every 55 minutes
View Get-Process-SSMSUsers.ps1
do {
$ssms = Get-WmiObject -Class Win32_Process -Filter "name = 'Ssms.exe'"
<# Timer/time bomb stub #> $now = Get-Date
$timespan = New-TimeSpan -Start $now -End (Get-Date).AddMinutes(55) # '20:25') #'2077-10-09 19:30'
Write-Host -ForegroundColor magenta "SSMS active user count: $(($ssms.GetOwner()).User.count)"
Write-Host -ForegroundColor magenta "SSMS active users: $(($ssms.GetOwner()).User)"
Write-Host -ForegroundColor magenta "Sleeping until $((Get-date).AddSeconds($timespan.TotalSeconds)) ..."
Start-Sleep -Seconds $timespan.TotalSeconds
}
while ( ($ssms.GetOwner()).User.count -GT 0 )
@nanoDBA
nanoDBA / Get-Command_Line_History_for_all_users.ps1
Last active January 25, 2023 13:49
Loop through PSReadline histories for all user profiles and copy them to the clipboard
View Get-Command_Line_History_for_all_users.ps1
### Get Command Line History for all users
# source: https://gist.github.com/nanoDBA/48f2cd1eab1e4433a97bd106e0dac08d
$dirs = (Get-ChildItem -Directory (Split-Path $env:USERPROFILE)).FullName | Sort-Object
$allHistories = foreach($profileDir in $dirs){
Write-Output "### $profileDir"
$historySavePath = "$profileDir\AppData\Roaming\Microsoft\Windows\PowerShell\PSReadLine\ConsoleHost_history.txt"
if(!(Test-Path ($historySavePath) )) {
Write-Output "### $historySavePath does not exist"
}
@nanoDBA
nanoDBA / Get-Daylight.ps1
Created December 22, 2022 14:04
How many minutes of daylight are there in a given day?
View Get-Daylight.ps1
# Yesterday was the winter solstice, so I am looking forward to more daylight!
# https://stackoverflow.com/questions/63236774/powershell-return-only-sunrise-sunset-time/63237047#63237047
# using API from https://sunrise-sunset.org/api
$lat = 35.787743 ; $long = -78.644257 # substitute desired latitude/longitude
# date range: 2 days ago until tomorrow and Loop de Loop using foreach
-2..1 | foreach {
$Date = (Get-Date).AddDays($_) | Get-Date -Format "yyyy-MM-dd"
$Daylight = (Invoke-RestMethod "https://api.sunrise-sunset.org/json?lat=$lat&lng=$long&formatted=0&date=$Date").results
View ServerCore-DefaultShell.ps1
# DANGER # Server Core? ---OPTIONAL--- Setting PowerShell as the Default Shell Manually
#If you've only got one server, a couple of servers or maybe your Server Core machines are workgroup members so you can't use Group Policy and if any of these are true, the manual method is for you. It's a simple PowerShell one-liner:
# source: https://richardjgreen.net/setting-powershell-default-shell-server-core/
Set-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon' -Name Shell -Value 'PowerShell.exe -NoExit'
View Get-CommandHistory.ps1
<#what else has been run as this user? #>Get-Content (Get-PSReadlineOption).HistorySavePath | Set-Clipboard
View AG-Resume.sql
--AG-Resume.sql for SQL Agent job
--Check Sync State and Execute Resume
DROP TABLE IF EXISTS #agAynamicSqlResume
DECLARE @sqlCommand NVARCHAR(max)
SELECT ';ALTER DATABASE [' + Db_name(DRS.database_id) + '] SET HADR RESUME' AS [resume_sql],
AGS.name AS AGGroupName,
AGL.dns_name AS Listener_dns_name,