This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--https://www.brentozar.com/blitz/page-verification/ | |
--An alternative to sp_msforeachdb | |
--https://johnmccormack.it/2015/12/an-alternative-to-sp_msforeachdb/ | |
SET NOCOUNT ON; | |
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp; | |
SELECT name, 0 AS completed | |
INTO #tmp | |
FROM sys.databases AS s | |
WHERE s.page_verify_option_desc <> 'CHECKSUM' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
## 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.** |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$cred = Get-Credential 'domain\someAccount' | |
$connFakeClient = Connect-DbaInstance -SqlInstance YOURSQLSRVR -SqlCredential $cred -ClientName "Test App" | |
Invoke-DbaQuery -SqlInstance $connFakeClient -Query "WAITFOR DELAY '00:02:00'" -MessagesToOutput |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# adapted from https://gist.github.com/potatoqualitee/20a3f84e987cafe03e0ebe3b4d593c65#file-process-insert-ps1 | |
# Specify your servers | |
$servers = "sql2014","sql2012","sql2016","sql2017" | |
# Setup the T-SQL | |
$sql = "SELECT SERVERPROPERTY('ServerName') AS SqlInstance, login_name as [Login], [host_name] as Host, | |
DB_NAME(p.dbid) as [Database], s.[program_name] as Program, s.login_time as LoginTime | |
FROM sys.dm_exec_sessions s inner join sys.sysprocesses p on s.session_id = p.spid | |
WHERE p.dbid is not NULL | |
and DB_NAME(p.dbid) != 'tempdb' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$someThingsArray = ($HereString = @" | |
one | |
two | |
three | |
"@ | |
).split("`n").TrimEnd("`r") <# Converting a Here-String to an Array of Strings https://web.archive.org/web/20201027204331/https://gallery.technet.microsoft.com/scriptcenter/Tip-of-the-Week-Converting-221aab3f #> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Find all the SQL Agent Jobs by querying groups | |
# of instances listed in the CMS | |
# in parallel, then output a spreadsheet | |
# | |
# Modules used: PoshRSJob, dbatools, ImportExcel | |
# | |
# Stop & remove all PoshRSJobs - Are you sure you want to do this? | |
# Get-RSJob | Stop-RSJob; Get-RSJob | remove-rsjob | |
# Use CMS server and optional group name to identify SQL instance names, remove duplicates |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Run a query against multiple databases | |
# by querying groups of instances | |
# listed in the CMS in parallel, | |
# then output a spreadsheet | |
# | |
# Modules used: PoshRSJob, dbatools, ImportExcel | |
# | |
# Stop & remove all PoshRSJobs - Are you sure you want to do this? | |
# Get-RSJob | Stop-RSJob; Get-RSJob | remove-rsjob |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<# clone latest master #> | |
$gitHubLink = "https://github.com/powerline/fonts.git" | |
$gitHubFolderName = (split-path $gitHubLink -Leaf).Replace('.git','') | |
$targetFolder = "$env:USERPROFILE\downloads\Repos\$gitHubFolderName" | |
<# *** This path and all subdirs are about to be deleted - Are you sure? *** #> | |
If(Test-Path $targetFolder) {Remove-Item -Recurse -Force $targetFolder\* -Confirm} | |
If(!(Test-Path $targetFolder)) {mkdir $targetFolder } | |
git clone -b master $gitHubLink $targetFolder | |
Invoke-Item $targetFolder |