mbourgon

DECLARE @page_command VARCHAR(4000), @ind_command VARCHAR(4000), @dbid VARCHAR(5)
		, @pagefid varchar(50), @pagepid varchar(50)
DECLARE @database_name sysname, @table_name sysname, @has_clustered_index VARCHAR(2)
SET @database_name = 'mydbname'
SET @table_name = 'mytablename'
SET @has_clustered_index  = '1'
--assumes it has one, to make the DBCC IND run faster.  If you don't care, set to -1. 
--You could set it to an actual index page, but you have to know the ID

SELECT @dbid = DB_ID(@database_name)

mbourgon

USE [msdb]
GO

/****** Object:  Job [EventLog Capture [Priority 2]]    Script Date: 5/12/2015 4:20:29 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 5/12/2015 4:20:30 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN

mbourgon

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE PROCEDURE EventLog_Blacklist_Stragglers
AS
--mdb 2015/04/30 for some reason we're seeing cases where the stored procedure is called, but isn't doing anything.
--				while running single-threaded fixes it, it also takes several (4x) times longer. 
--				We could also simply load everything in first, then go through and filter.  However, that causes other
--				issues, like space bloat.  Hence, this.

mbourgon

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Eventlog_Blacklist_Removal
@ComputerName sysname, @EventLog VARCHAR (255)
AS 
SET NOCOUNT on
/*
--Uses log parser to scan the event logs 
1.00 Michael Bourgon 2010/02/05 still need to be fixed: TRDB issues, "Error reading event log: The remote procedure call failed."

mbourgon

USE [EventLog_Tracking]
GO
/****** Object:  FullTextCatalog [Eventlog_Blacklist]    Script Date: 5/12/2015 4:02:38 PM ******/
CREATE FULLTEXT CATALOG [Eventlog_Blacklist]WITH ACCENT_SENSITIVITY = ON
AS DEFAULT

GO
/****** Object:  Table [dbo].[EventLog_Blacklist_Calls]    Script Date: 5/12/2015 4:02:38 PM ******/
SET ANSI_NULLS ON
GO

mbourgon

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[EventLog_Monthly_Table_View_Maintenance]
AS 
--mdb 2015/05/06 adding insert_datetime so that we can tell when the heck rows are added, for troubleshooting
DECLARE @sql VARCHAR(8000)
SELECT @sql =
'CREATE TABLE [dbo].[EventLog_' + CONVERT(CHAR(6),DATEADD(mm,1,GETDATE()),112) + '] (

mbourgon

#Note this is JUST the capture portion; there's a database component as well
# the 4 scripts below are obviously prereqs, from Hey Scripting Guy and PoshCode
clear 
. C:\powershell_scripts\invoke-sqlcmd2.ps1;
. C:\powershell_scripts\split-job.ps1;
. C:\powershell_scripts\write-datatable.ps1;
. C:\powershell_scripts\out-datatable.ps1;

$server_repository = 'yourreposerverhere'  
$database_repository = 'yourrepodbhere'

mbourgon

USE tempdb
/*
2015/04/22 - mdb - 2.00 - pull raw CSV, shred via function, pivot into a virtual table, then insert into
							actual table based on existing fields.  Practically: forwards/backwards compatability.
							Add new fields to your target table, and as they show up in the new file they'll be added.
							Practically, they'll have to rename the file when they do this, so that we know the 
							names of the new fields. But that's just a parameter change in the job. 
2015/05/05 - mdb - 2.1 - very basic implementation, complete code. Just fill out the parameters!
Downside: SLOW?!  A 5mb file, which has 106k rows, takes 7 seconds.

mbourgon

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO


/*
2015/04/22 - mdb - 2.00 - changed to pull raw CSV, shred via function, pivot into a virtual table, then insert into
							actual table based on existing fields.  Practically: forwards/backwards compatability.
							Add new fields to your target table, and as they show up in the new file they'll be added.
							Practically, they'll have to rename the file when they do this, so that we know the 

mbourgon

. C:\powershell_scripts\out-datatable.ps1
. C:\powershell_scripts\write-datatable.ps1

$server_repository = 'myrepo'  
$database_repository = 'repodb'

#here we create $starttime, then it will get the values when passed through the for-each. Could just do inline, but it's a clever concept so leaving it here.
$StartTime= @{n='StartTime';e={$_.ConvertToDateTime($_.CreationDate)}}

#get list of servers we want to look at