mbourgon

USE eventnotificationrec
GO

CREATE PROCEDURE [dbo].[EN_TFS_Checkin]
AS
/*
Purpose - using Event Notifications, determines which objects have changed (sub-database-level, for now).
Once list has been generated, repeatedly call a custom powershell script that will
	"get" and "checkout" the object from TFS if it exists, overwrite it with a current copy (scripted via SMO),
	then "checkin" the new object with a comment based on the loginname

mbourgon

CREATE PROCEDURE sp_server_diag_event_parser
as
--SP_SERVER_DIAGNOSTICS Dynamic Parser for "events", v1.23
--You may use this at will, you may share it provided this header remains.  
-- Copyright 2012 Michael Bourgon
-- Commercial use or sale prohibited without permission. Personal, Internal Company, or Private use is fine.
-- If you're just running this as your job as a DBA, enjoy.
-- Please feel free to share, and feel free to send corrections or enhancements - thebakingdba.blogspot.com
-- Thanks to Marc_S on Stackoverflow for the help on parsing XML.
-- Thanks to Stack Overflow for forcing me to come up with a good question - so I found the flawed derived table slowdown.

mbourgon

--For the repository server I only have one cert and one key.  
--For the new servers I have to create a new certificate and key for each one.  

-------------------------------------------------------------
--Part 1, Run on new server adding to Event Notifications--
-------------------------------------------------------------
USE master
GO
--Creating master key and an user which will be used by the certificate
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'';

mbourgon

<#
.SYNOPSIS
   Script downloads an rss feed of data about SQL Server service packs and cummulative updates.
.DESCRIPTION
   Script downloads data into a staging table and is called by a job.
.PARAMETER <paramName>
   There are no parameters.
.EXAMPLE
   Call from a job:  powershell "& 'C:\Powershell_Scripts\rss_feed_SqlServer_Releases.ps1'"
#>

mbourgon

--reminder, this runs on the central monitorING box, and uses xp_cmdshell and the like to connect to the "new" box.


/*
 Event Notification - add server to monitoring.  Probably simpler to do by hand, but I like this.

 See http://thebakingdba.blogspot.com/ for more information, look for Event Notifications

 Given a parameter of servername, WHEN RUN ON THE EN "repository", it will 
	a) try to determine the servername for the "sender"

mbourgon

--mdb 2016/08/18 v1.10.  Added a few things to make it faster and not delete the entire table. And some docs!
--No idea where the idea to walk the table comes from (Bertrand? Strate?); the premise was that if you knew 
-- what you were currently deleting, and it was ordered, you could tell the optimizer to skip the part you've already deleted.
-- The overall delete for the top 5k from a CTE is from Simon Sabin & the SQLCat team.
--https://web.archive.org/web/20090630064721/http://blogs.msdn.com/sqlcat/archive/2009/05/21/fast-ordered-delete.aspx?
--http://sqlblogcasts.com/blogs/simons/archive/2009/05/22/DELETE-TOP-x-rows-avoiding-a-table-scan.aspx

IF OBJECT_ID('tempdb..#mydeleted') IS NOT NULL
   DROP TABLE #mydeleted
CREATE TABLE #mydeleted

mbourgon

--written by MDB and ALM for TheBakingDBA.Blogspot.Com
-- basic XE session creation written by Pinal Dave
-- http://blog.sqlauthority.com/2010/03/29/sql-server-introduction-to-extended-events-finding-long-running-queries/
-- mdb 2015/03/13 1.1 - added a query to the ring buffer's header to get # of events run, more comments
-- mdb 2015/03/13 1.2 - added model_end events, filtering on hostname, using TRACK_CAUSALITY, and multiple events
-- mdb 2015/03/18 1.3 - changed header parse to dynamic, courtesy of Mikael Eriksson on StackOverflow
-- This runs on at 2008++ (tested on 2008, 2008R2, 2012, and 2014). Because of that, no NOT LIKE exclusion
------------------------------
-- Create the Event Session --
------------------------------

mbourgon

if object_id('tempdb..#systemhealthsessiondata') is not null
    DROP TABLE #systemhealthsessiondata

SELECT CAST(xet.target_data AS XML) AS XMLDATA
INTO #SystemHealthSessionData
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'

mbourgon

if object_id('tempdb..#systemhealthsessiondata') is not null
    DROP TABLE #systemhealthsessiondata

SELECT CAST(xet.target_data AS XML) AS XMLDATA
INTO #SystemHealthSessionData
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'

mbourgon

--table must have at least 10000 rows, so that it doesn't get lost in NULLs.  I think.
DECLARE @table_name sysname, @date_column sysname, @sql NVARCHAR(4000), @lowest_date VARCHAR(20)
SET @table_name = 'mytablename'
SET @date_column = 'mydatefield'
SET @lowest_date = '20150415'

SELECT @sql = '
DECLARE @tempdate DATETIME, @target_id bigint, @max_id bigint
SELECT @target_id = MAX(id) FROM ' + @table_name + ' with (NOLOCK) 
set @max_id = @target_id