Skip to content

Instantly share code, notes, and snippets.

Avatar

Peter Schott paschott

View GitHub Profile
View ScriptDBUserPermissions.ps1
Import-Module dbatools
Import-Module sqlserver #used for Invoke-SqlCmd to run scripts w/ the "GO" batch separator
$CurrentDate = (Get-Date).ToString("yyyyMMdd_hhmmss")
$Servername = "localhost"
$databases = 'CSV_Set_Of_DB_Names'
#Get permissions for databases
View FindUnusedIndexes.sql
SELECT
objects.name AS Table_name,
indexes.name AS Index_name,
dm_db_index_usage_stats.user_seeks,
dm_db_index_usage_stats.user_scans,
dm_db_index_usage_stats.user_updates
FROM
sys.dm_db_index_usage_stats
INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
View SSRS_Subscription_Jobs.sql
--Get SQL Agent Job names for SSRS Subscriptions
SELECT Schedule.ScheduleID AS JobName,
[Catalog].Name AS ReportName,
Subscriptions.Description AS Recipients,
[Catalog].Path AS ReportPath,
StartDate,
Schedule.LastRunTime
FROM [ReportServer].dbo.ReportSchedule
INNER JOIN [ReportServer].dbo.Schedule
ON ReportSchedule.ScheduleID = Schedule.ScheduleID
@paschott
paschott / New-SQLServerCertificate.ps1
Created Dec 10, 2020
Passes a CSR to the Certificate Authority, downloads the certificate, passes it to the target server, then imports and sets it for SQL Server
View New-SQLServerCertificate.ps1
#requires dbatools
$server = "servername"
$localCertPath = "C:\CertificateRequests"
$remoteCertPath = "C:\CertificateRequest\"
$adminuser = Import-Clixml C:\user.cred #stored credentials to access remote server
# Generate the CSR and download locally
$session = New-PSSession $server -Credential $adminuser
@paschott
paschott / Generate-CSR.ps1
Created Nov 24, 2020
PowerShell script to generate a Certificate Request for a server using certain criteria
View Generate-CSR.ps1
#Create new Certificate Request for SQL Server security
# Should be made into a function at some point
# Needs to be able to handle Cluster names/IP addresses
#Set location of the server
$Location = "City"
$State = "State"
$OU = "OU"
$Company = "Organization"
@paschott
paschott / usp_PurgeSSISCatalogLogs.sql
Created Oct 28, 2020
Creates stored procedure to purge SSIS catalog in batches of rows from child to parent.
View usp_PurgeSSISCatalogLogs.sql
use SSISDB
GO
CREATE PROC dbo.usp_PurgeSSISCatalogLogs
@RowsToDelete int = 5000
AS
BEGIN --Proc
/*
Script name: Purge SSIS Catalog log tables
Author: Tim Mitchell (www.TimMitchell.net)
@paschott
paschott / InstallApps.ps1
Created Jul 17, 2020
Use Chocolatey to install some common apsp for MS SQL work and related
View InstallApps.ps1
#Note - will want to install "Chocolatey" first in order to use this file to install software.
# https://chocolatey.org/docs/installation
# Open a command prompt (cmd) and run the following:
# @"%SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe" -NoProfile -InputFormat None -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"
# ..
# or open a PowerShell Admin prompt and run:
# Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))
# After Chocolatey is installed, open a PS prompt in Administrator mode,
@paschott
paschott / TSQLDeleteData.sql
Last active Jun 15, 2020
Delete rows from a table in batches. Do not run during set times. Pause between loops.
View TSQLDeleteData.sql
DECLARE @Time TIME,
@RowsToDelete INT,
@PurgeDataPriorToDate DATETIME2,
@RowsDeleted INT,
@EndLoopTime TIME,
@StartLoopTime TIME
SELECT @RowsToDelete = 5000
SELECT @EndLoopTime = '20:00:00'
SELECT @StartLoopTime = '08:00:00'
View GetMostRecentBackupHistory.sql
--TableName:Backup_MostRecent
;with
db as (
select [Instance] = @@SERVERNAME,
[Database] = name,
[RecoveryMode] = DATABASEPROPERTYEX(name, 'Recovery'),
[CreationTime] = crdate,
[Status] = DATABASEPROPERTYEX(name, 'Status')
from master..sysdatabases
where name!='tempdb'
@paschott
paschott / Get-Mirroring-Information.sql
Created May 20, 2020
Mirroring information for all databases on a SQL Server
View Get-Mirroring-Information.sql
SELECT DISTINCT
CAST(SERVERPROPERTY('ServerName') as nvarchar(100)) AS Principal,
m.mirroring_partner_instance AS Mirror,
DB_NAME(m.database_id) AS DatabaseName,
SUM(f.size*8/1024) AS DatabaseSize,
CASE m.mirroring_safety_level
WHEN 1 THEN 'HIGH PERFORMANCE'
WHEN 2 THEN 'HIGH SAFETY'
END AS 'OperatingMode',
RIGHT(m.mirroring_partner_name, CHARINDEX( ':', REVERSE(m.mirroring_partner_name) + ':' ) - 1 ) AS Port