Skip to content

Instantly share code, notes, and snippets.

@sirsql
sirsql / Get-NewPassword.ps1
Created May 4, 2022
Creates a new password of specified length
View Get-NewPassword.ps1
#Creates a new password of specified length using characters within the random character set
function Get-NewPassword ([int32]$PasswordLength) {
if (!$PasswordLength) { $PasswordLength = 42 }
[string]$CharacterSet = 'abcdefghijklmnoprstuvwxyzABCDEFGHIJKLMONPQRSTUVWXYZ!@#$%^&*()[]{},.<>/?'
$RandomChar = 1..$PasswordLength | ForEach-Object { Get-Random -Maximum $CharacterSet.length }
$ofs = ""
[string]$CharacterSet[$RandomChar]
}
@sirsql
sirsql / GetSqlServerColumnUse.sql
Last active Apr 18, 2022
Return a list columns in the database and for each column a comma delimited list of tables in which that column appears
View GetSqlServerColumnUse.sql
/* Return a list columns in the database and for each column a comma delimited list of tables in which that column appears */
SELECT
c.name AS ColumnName
, STRING_AGG(CONCAT( QUOTENNAME(s.name), '.', QUOTENAME(t.name)), ', ')
WITHIN GROUP (ORDER BY CONCAT(QUOTENAME(s.name), '.', QUOTENAME(t.name))) AS TableListForColumn
, COUNT(*) AS TableUsageCountForColumn
FROM sys.tables AS t
JOIN sys.columns AS c ON c.object_id = t.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
GROUP BY c.name
@sirsql
sirsql / Parse Backup Perf Tests.ps1
Created Aug 27, 2020
When used with "Backup Test Harness.sql" will parse the results into an Excel worksheet for evaluation. Related to blog post https://sirsql.net/2012/12/13/20121212automated-backup-tuning/
View Parse Backup Perf Tests.ps1
<#
.SYNOPSIS
Reads a SQL job log containing the output from backup performance tests and displays the results in excel.
.DESCRIPTION
This script is designed to read the output from a logfile that contains multiple backups which use
different settings of MAXTRANFERSIZE & BUFFERCOUNT. This logfile will have been created by executing the job
created using "Backup Test Harness.sql".
It will read in the log and output the results to excel (you must have this installed). It will also create
two charts which provide a visual representation of the current, optimal and best possible backup performance
@sirsql
sirsql / CreateOrphanedContainedUsersTest.sql
Created Sep 4, 2019
DBATools issue 5887 SQl side test code
View CreateOrphanedContainedUsersTest.sql
/* Set the sysconfig setting to support contained databases if not already set - assumes SQL 2012 or newer */
if (select value_in_use from sys.configurations where name = 'contained database authentication') <> 1
begin
exec sp_configure 'contained database authentication', 1;
reconfigure
end
GO
/* Create the ContainedTest database where we'll be doing the work */
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'ContainedTest')
@sirsql
sirsql / GetAGLatency.sql
Created Dec 20, 2018
Grabs AG latency information. Run on primary to get information for all replicas and all databases, run on a secondary to get information for just that server. *note: MintoRedo is approximate based on the size of the queue and rates, this fluctuates so it not always accurate
View GetAGLatency.sql
SELECT DB_NAME(database_id),
a.replica_server_name,
database_id AS di,
last_redone_time,
DATEDIFF(MINUTE, last_sent_time, last_received_time) AS ReceiveLatencyS,
DATEDIFF(SECOND, last_sent_time, last_redone_time) AS RedoLatencyS,
CASE
WHEN redo_queue_size = 0 THEN
0
WHEN redo_rate = 0 THEN
@sirsql
sirsql / GetProcPerfAgainstHistory.sql
Created Nov 28, 2018
Provides the ability to assess stored procedure performance against history using data in the SentryOne database
View GetProcPerfAgainstHistory.sql
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO
IF NOT EXISTS
(
SELECT *
FROM [sys].[procedures]
WHERE [object_id] = OBJECT_ID(N'[dbo].[GetProcPerfAgainstHistory]', 'P')
View Backup Test Harness.sql
/********************************************************************************************************** *
* *
* Name: Backup Test Harness.sql (version 1.0 2012-12-11) *
* *
* Author: Nicholas Cain, http://sirsql.net *
* *
* Purpose: This script is written for the purposes of performing multiple backups of a database using *
* different MAXTRANSFERSIZE & BUFFERCOUNT values as well as different numbers of backup files. *
* This is done in an attempt to identify the optimal settings that can be used to minimize backup *
* times. *
@sirsql
sirsql / GhostRecordStatusPerAGReplica.sql
Created Nov 16, 2016
Gets the status of ghost rows and the low watermark per replica in AGs
View GhostRecordStatusPerAGReplica.sql
;
WITH PrimaryStats
AS ( SELECT DB_NAME(database_id) AS DatabaseName ,
low_water_mark_for_ghosts ,
ar.replica_server_name ,
ar.availability_mode_desc
FROM sys.dm_hadr_database_replica_states hdrs
JOIN sys.availability_replicas ar ON hdrs.replica_id = ar.replica_id
WHERE ar.replica_server_name = @@SERVERNAME
)
View Get-TraceFlags.ps1
<#
.SYNOPSIS
Designed to retrieve global trace flags that are set on a server.
.DESCRIPTION
Designed to retrieve global trace flags that are set on a server. It will also return whether those trace flags are startup flags, or enabled post startup.
.PARAMETER SqlServer
Required: The SQL Server instance to query
View Get-SqlErrorLog.ps1
<#
.SYNOPSIS
Get-SqlErrorLog is designed to quickly retrieve data from SQL Server error logs, negating the slowness of SSMS and awkwardness of manually crawling files.
.DESCRIPTION
Get-SqlErrorLog is designed to quickly retrieve data from SQL Server error logs, negating the slowness of SSMS and awkwardness of manually crawling files.
It is designed to be quite light in it's process, and should be quick (loading SQLPLS being the exception.