sirsql

#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

/* 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

<#
.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

/* 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

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

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')

sirsql

/**********************************************************************************************************	 *
* 																											 *
* 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

;
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
             )

sirsql

<#
	.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
	

sirsql

<#
	.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.