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
#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] | |
} |
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
/* 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 |
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
<# | |
.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 |
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
/* 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') |
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
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 |
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
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') |
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
/********************************************************************************************************** * | |
* * | |
* 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. * |
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
; | |
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 | |
) |
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
<# | |
.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 | |
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
<# | |
.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. |
NewerOlder