nanoDBA

--Get-SPIDs_last_batch.sql 
-- https://stackoverflow.com/questions/212596/how-to-detect-sqlserver-connection-leaks-in-a-asp-net-applications/12428235#12428235

SELECT S.spid, S.login_time, S.last_batch, S.status, S.hostname, S.program_name, S.cmd, S.loginame,
last_sql = ( select text from sys.dm_exec_sql_text(S.sql_handle) ) 
, database_name = DB_NAME(dbid)
FROM sys.sysprocesses S
INNER JOIN sys.dm_exec_sessions sx
on sx.session_id = S.spid
and sx.is_user_process = 1

nanoDBA

#requires -version 5.0
#requires –runasadministrator

#region overview
<#
'Practical PowerShell for The Busy DBA' – this will be hands-on (lab + presentation)

-	Basic overview of PowerShell and getting started (WMF 5.1 and other versions: prereqs, cmdlets, 
     transcription, console navigation, and getting quick help/examples)
-	Installing Modules with PowerShell 5.0 and up

nanoDBA

# How will the backup headers be scanned?  SQL Server and Backup Path
$BkParamHash = @{
    SqlInstance = "localhost"
    Path        = "D:\Share01\Demo", "D:\Share02\Demo"
}

$BackupMetaData = Get-DbaBackupInformation @BkParamHash -Verbose

# Let's restore 11 databases
$DbSuffixRange = 500..510

nanoDBA

# Restoring Databases from Files in Parallel

# Let's restore 11, 21, 41 databases!
     $DbSuffixRange = 500..510
    # $DbSuffixRange = 500..520
    # $DbSuffixRange = 500..540

$RSJobparamHash = @{
    Throttle        = 8
    ModulesToImport = "dbatools"

nanoDBA

--How far behind is Secondary in AlwaysOn replica ? ( LAG )
--http://blogs.extremeexperts.com/2013/11/04/sql-server-alwayson-how-far-behind-is-secondary/
--"There are a lot of times customers ask me how much time is my secondary behind my primary?
--Such a simple question can be answered easily using some good DMVs."

SELECT AGS.name                       AS AGGroupName, 
       AGL.dns_name			          AS Listener_dns_name, 
       AGL.port				          AS Listener_port, 
       AGL.ip_configuration_string_from_cluster    AS Cluster_IP_addresses, 
       AR.replica_server_name         AS InstanceName, 

nanoDBA

<# Install Nuget for Allusers if it's not already there #>if (-not (Get-PackageProvider -Name NuGet -ListAvailable -ErrorAction:SilentlyContinue )) { Install-PackageProvider -Name NuGet -Force -Scope AllUsers }
# modified from https://gist.github.com/RobCannon/12f338f00ce906fb4d43ee472c6a1389

nanoDBA

#Requires -Modules PoshRSJob
break; #Safety First - These bits of script are meant to match a user name
# and LOGOFF the RDP sessions of that user when passed a list of servers
# So... potentially very dangerous       

# source: https://4sysops.com/archives/how-to-force-a-user-logoff-remotely-with-powershell/
$results = Start-RSJob -Throttle 20 <#-ModulesToImport dbatools#> -InputObject $TargetServerstoKILL_RDP_SESSIONS -Verbose -ScriptBlock { $username = 'kill.this.username'; Write-Output "$($_)..."; $sessionId = 7777777; Write-Output "`$sessionId = $($sessionId)"; quser /server:"$($_)" | Where-Object { $_ -match $userName } ; $sessionId = ((quser /server:"$($_)" | Where-Object { $_ -match $userName }) -split ' +')[2] ; Write-Output "`$sessionId = $($sessionId)" ; IF ($sessionId -NE "SESSIONNAME" -AND $sessionId -NE $null -AND $_ -NE $($env:COMPUTERNAME)) { logoff $sessionId /server:"$($_)" } ELSE { Write-Output "No match found for $username" }  } | Wait-RSJob -Timeout 72 -ShowProgress -Verbose | Receive-RS

nanoDBA

<# To compare two server configs ( sp_configure ) #>

# https://sqldbawithabeard.com/tag/sql-dba/
# https://dbatools.io/functions/get-dbaspconfigure/

$oldProps = Get-DbaSpConfigure -SqlInstance 'SERVER01' # | Sort-Object ConfigName
$newProps = Get-DbaSpConfigure -SqlInstance 'NEWSERVER-02'  #| Sort-Object ConfigName

$propCompare = foreach ($prop in $oldprops) {
    if (($prop2   = $newprops | Where-Object DisplayName -EQ $prop.DisplayName) -AND ($prop2.RunningValue -NE $prop.RunningValue)) {

nanoDBA

## Create AES key with random data and export to file
<#
Sometimes a script needs to use a password, but you want it stored securely.  Passing a credential by utilizing 2 things:

1.	A password file that contains the encrypted file
2.	A key file that contains a randomly generated AES key

allows for utilization of credentials within scripts by different users and servers.  
**The following method is only as secure as the locations of the files(password and key) themselves.**

nanoDBA

<# download and install SentryOne Plan Explorer: 2018-07-16 release #>$PlanEXP_filename = "$env:USERPROFILE\downloads\PlanExplorerInstaller.exe"; Start-BitsTransfer -Source 'https://downloads.sentryone.com/downloads/sqlsentryplanexplorer/x64/PlanExplorerInstaller.exe' -Destination $PlanEXP_filename; & $PlanEXP_filename /install /passive /norestart