Skip to content

Instantly share code, notes, and snippets.

Avatar

Peter Schott paschott

View GitHub Profile
@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
View Create and Populate Calendar Table
--Adapted from http://www.chilledsql.com/welcome/tip_category_dateandtime/tip_detail_dateandtime_createmastercalendartable
--If this is an existing table, do not drop/recreate the table, just adjust dates and run the appropriate population script
--Adjust code to ignore dates already in the calendar table.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON
View RunAllSQLScripts.ps1
Import-Module dbatools
$scripts = get-childitem "C:\ScriptsToRun\" -Filter *.sql | sort-object Name
$servers = "servername"
$database = "databasename"
foreach ($script in $scripts) {
$OutputFile = $script.directoryname + "\" + $script.basename + ".txt"
Write-Host $OutputFile
Invoke-DbaQuery -SqlInstance $servers -File $script.FullName -Database $database -MessagesToOutput | Out-File -FilePath $OutputFile
@paschott
paschott / ChangeSSISProtectionLevel.ps1
Created Mar 9, 2020
Changes SSIS Protection Level for all packages and project
View ChangeSSISProtectionLevel.ps1
#PowerShell script
################################
########## PARAMETERS ##########
################################
$projectFolder = "C:\SSIS\myProject\myProject"
$dtutilPath = "C:\Program Files\Microsoft SQL Server\130\DTS\Binn\dtutil.exe"
# The number changes per SQL Server version
# 130=2016, 120=2014, 110=2012
# Also check the drive where SQL Server is
# installed
@paschott
paschott / File_Autogrowth.sql
Last active Nov 23, 2019
Generates TSQL to update File Auto-Growth for SQL Server for files using the old defaults
View File_Autogrowth.sql
DECLARE @NewDataFileGrowth varchar(100) = '500MB'
DECLARE @NewLogGrowth varchar(100) = '100MB'
select
CASE WHEN physical_name like '%.ldf'
THEN 'ALTER DATABASE [' + DB_NAME(mf.database_id) + '] MODIFY FILE (NAME = [' + mf.name + '], FILEGROWTH = ' + @NewLogGrowth + ')'
ELSE 'ALTER DATABASE [' + DB_NAME(mf.database_id) + '] MODIFY FILE (NAME = [' + mf.name + '], FILEGROWTH = ' + @NewDataFileGrowth + ')'
END as ChangeAutoGrowSettings,
DB_NAME(mf.database_id) database_name,
mf.name logical_name,
View ExamineAzureSQLAudit.sql
--replace the URL in the function with the appropriate URL for your file
SELECT TOP 1000 *
FROM sys.fn_get_audit_file('https://subscription.blob.core.windows.net/paths/auditfile.xel', default, default)
WHERE (event_time <= '2019-02-20T21:56:36.631Z')
/* additional WHERE clause conditions/filters can be added here */
and action_id IN ('DBAF') --look specifically for auth failures
ORDER BY event_time DESC
You can’t perform that action at this time.