mbourgon

import-module awspowershell


#You should have already saved your credentials locally using:
# set-awscredentials -accesskey myaccesskey -secretkey mysecretkey -StoreAs MyDeveloper
$credential = get-awscredentials -ProfileName MyDeveloper

# Get the current timestamp
$Current_Unix_Timestamp = [Math]::Floor([decimal](Get-Date(Get-Date).ToUniversalTime()-uformat "%s")) * 1000

mbourgon

//See full article at http://thebakingdba.blogspot.com/2017/07/clr-soup-to-nuts-making-generic-clr-to.html


using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Net;

mbourgon

--Written by mbourgon 2016 as per http://thebakingdba.blogspot.com/2016/08/statistics-get-last-4-stat-update-time.html
--CC, noncommerical with attribution.  Use it as you want noncommercially, but this notice must remain.

--run in the database you need to evaluate.
DECLARE @sql NVARCHAR (1000), @MIN INT, @MAX INT, @stats_name NVARCHAR(128), @tablename sysname, @the_schema_name sysname, @db_name sysname
if object_id('tempdb..#stats_info') is not null
    DROP TABLE #stats_info
CREATE TABLE #stats_info (updated DATETIME, [Table cardinality] BIGINT, [snapshot ctr] BIGINT, steps INT, density DECIMAL(19,16), [rows above] INT, [rows below] INT, [squared variance error] DECIMAL (19,12), [inserts since last update] MONEY, [deletes since last update] MONEY, [leading column type] VARCHAR(50))

if object_id('tempdb..#stats_info2') is not null

mbourgon

--check last 15 minutes to make sure nobody did a stupid.
DECLARE @tableHTML NVARCHAR(MAX);
WITH cte AS
(
SELECT PostTime, EventType, LoginName, ObjectName, ServerName FROM ENAudit_Events WHERE EventType LIKE '%route%'
AND PostTime > dateadd(minute, datediff(minute,0,GETDATE()) / 15 * 15, 0)
)

select @tableHTML = N'<H3>Event Notification ROUTE changes - make sure you meant to add/remove these from EN!' + '</H3>' 
+ N'<table border="1">' 

mbourgon

#get the list of servers to tackle
foreach ($Server in Get-Content "C:\UpdateUsers.txt")
#now do each server 
{
#making a \\server and server variable
$server2 = $Server -replace ("\\","")

#Which server is it updating
Write-host "Working on $Server2"

mbourgon

CREATE PROCEDURE ssis_job_failure__find_failures_to_report
AS 
/*
2016/04/13 MDB - 1.00 - Since SQLSentry doesn't always work with the stored procedure (performance issues because we have so many jobs running frequently)
							, we're trying this.  Scan sysjobhistory for errors.  Run each job failure through the SSISDB SP I wrote.
							Set the job to run every minute.
this is a companion to https://gist.github.com/mbourgon/5502095, which will send useful SSISDB emails when a job fails.

CREATE TABLE ssisdb_failure_email_last_instance_id_checked (id INT IDENTITY PRIMARY KEY, instance_id int, insert_datetime DATETIME)
--109911

mbourgon

DECLARE @last_insert DATETIME
SET @last_insert = (SELECT TOP 1 insert_datetime FROM EventNotificationRec.dbo.ENAudit_Events ORDER BY id DESC)
SELECT @last_insert

SELECT DATEDIFF(MINUTE,@last_insert, GETDATE())

IF (SELECT DATEDIFF(MINUTE,@last_insert, GETDATE())) >60
begin
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'me',

mbourgon

DECLARE @SQL as VARCHAR (MAX)
DECLARE @Columns AS VARCHAR (MAX)

SELECT @Columns=
       COALESCE(@Columns + ',','') + QUOTENAME(across)
FROM
(
       SELECT DISTINCT across
       From #TEMP
) AS B

mbourgon

--gets the last 4 stats updates for each non-system/non-MS table/statistic
--thebakingdba.blogspot.com
use yourdatabasenamehere
DECLARE	@sql NVARCHAR(1000),
		@MIN INT,
		@MAX INT,
		@statname NVARCHAR(128),
		@tablename NVARCHAR(128),
		@schemaname NVARCHAR(128);
DECLARE	@listofstats TABLE

mbourgon

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Eventlog_Tracking_Notifying_on_Busy_Servers] @threshold INT = 100
AS 

--mdb 2015/07/17 1.00 - Basically, making sure that we don't get blindsided by something going wrong.
--							Piggybacks on top of the eventlog_tracking job, so if that fails or stops working, so will this.
--							Looks for any server/log combinations that have more than 100 entries in a minute.
--							Once a couple issues are fixed, we could drop this number.  Obviously, filtering will have to occur, too.