Stored procedure that continuously restarts SQL Server looking for undocumented trace flags

Finding undocumented trace flags.sql

/*
THIS IS A SPECTACULARLY BAD IDEA.

Never, ever, ever run this in production.
Don't even run it on a VM that you care about.
(I specifically build a throwaway VM just to run this.)

Get the latest version:
https://gist.github.com/BrentOzar/5d8e4e8cbc1d89d1021b6f889762d835
*/



USE master;
GO
/* Track our progress as we loop through trace flags */
CREATE TABLE dbo.TraceFlagErrorLogs (TraceFlag INT, LogDate DATETIME, ProcessInfo NVARCHAR(400), Text NVARCHAR(MAX));
CREATE TABLE dbo.TraceFlags (TraceFlag INT, ErrorNumber INT, ErrorMessage NVARCHAR(MAX), Success BIT, TestingNow BIT);
GO
INSERT INTO dbo.TraceFlags (TraceFlag, TestingNow) VALUES (11500, 1);
GO
/* Create a view in a user database with snapshot isolation on. We just need a view to test the stored proc. */
USE StackOverflow;
GO
CREATE OR ALTER VIEW dbo.Ocean
AS
SELECT DisplayName
  FROM dbo.Users;
GO

USE master;
GO
/* We're going to need xp_cmdshell to restart the server,
   and automatic stored proc execution on startup */
sp_configure 'xp_cmdshell', 1;
sp_configure 'scan for startup procs', 1;
GO
RECONFIGURE
GO


CREATE TABLE dbo.TraceFlagErrorLogs (TraceFlag INT, LogDate DATETIME, ProcessInfo NVARCHAR(400), Text NVARCHAR(MAX));

CREATE OR ALTER PROC dbo.usp_TraceFlagTest AS
BEGIN
	/* Stop testing for a couple hours in the morning so I can poke around */
	IF DATEPART(hh, GETDATE()) BETWEEN 6 AND 8
		RETURN;

	/* Get the trace flag we're currently testing: */
	DECLARE @CurrentTraceFlag INT;
	SELECT TOP 1 @CurrentTraceFlag = TraceFlag
		FROM dbo.TraceFlags
		WHERE TestingNow = 1;
	IF @CurrentTraceFlag IS NULL
		RETURN;

	/* Collect the error log from the last round */
	CREATE TABLE #Errs (LogDate DATETIME, ProcessInfo NVARCHAR(400), Text NVARCHAR(MAX));
	INSERT INTO #Errs
		EXEC xp_readerrorlog;
	INSERT INTO dbo.TraceFlagErrorLogs(TraceFlag, LogDate, ProcessInfo, Text)
		SELECT @CurrentTraceFlag, LogDate, ProcessInfo, Text
		FROM #Errs;

	/* Try the problematic T-SQL */
	BEGIN TRY
		EXECUTE StackOverflow.sys.sp_refresh_single_snapshot_view
			@view_name = N'dbo.Ocean',
			@rgCode = 0;
		UPDATE dbo.TraceFlags
			SET TestingNow = 0, Success = 1,
			ErrorNumber = ERROR_NUMBER(),
			ErrorMessage = ERROR_MESSAGE()
		WHERE TraceFlag = @CurrentTraceFlag;
		RETURN;
	END TRY
	BEGIN CATCH
		UPDATE dbo.TraceFlags
			SET TestingNow = 0, Success = 0,
			ErrorNumber = ERROR_NUMBER(),
			ErrorMessage = ERROR_MESSAGE()
		WHERE TraceFlag = @CurrentTraceFlag;
	END CATCH

	/* Bail out if it worked */
	IF EXISTS (SELECT * FROM dbo.TraceFlags WHERE Success = 1)
		RETURN;

	/* Take off the last trace flag */
	declare @Parameters				varchar(max)='-T' + CAST(@CurrentTraceFlag AS VARCHAR(5)),
				@Argument_Number	int,
				@Argument					varchar(max),
				@Reg_Hive					varchar(max),
				@CMD							varchar(max)
	select * from sys.dm_server_registry where value_name like 'SQLArg%' and convert(varchar(max),value_data) LIKE '%' + @Parameters + '%';

	---------------------------------------------------------------------------------------------------------{Parameter Cleanup}
	if exists (select * from sys.dm_server_registry where value_name like 'SQLArg%' and convert(varchar(max),value_data) LIKE '%' + @Parameters + '%')
	begin 
				select 
				@Argument=value_name,@Reg_Hive=substring(registry_key,len('HKLM\')+1,len(registry_key))
				from sys.dm_server_registry where value_name like 'SQLArg%' and convert(varchar(max),value_data) LIKE '%' + @Parameters + '%'
		
				set  @CMD='master..xp_regdeletevalue
										''HKEY_LOCAL_MACHINE'',
										'''+@Reg_Hive+''',
										'''+@Argument+''''
				exec (@CMD)
	end 

	IF @CurrentTraceFlag <= 1
		RETURN;

	SET @CurrentTraceFlag = @CurrentTraceFlag - 1;
	/* Skip trace flags that are known, or known dangerous to run this on */
	WHILE @CurrentTraceFlag IN (101,102,105,106,107,110,120,139,144,146,168,174,176,204,205,206,208,210,212,220,221,222,237,242,243,244,246,253,257,260,262,272,274,302,310,320,323,325,326,330,342,345,445,506,610,611,617,634,646,647,652,653,661,662,669,683,692,698,699,670,671,715,806,809,815,818,822,825,828,830,831,834,835,836,839,840,842,845,851,888,902,916,1106,1117,1118,1119,1124,1140,1165,1180,1197,1200,1202,1204,1205,1206,1208,1211,1216,1217,1222,1224,1228,1229,1236,1237,1260,1261,1262,1264,1400,1439,1448,1449,1462,1482,1504,1603,1604,1609,1610,1611,1613,1615,1704,1717,1800,1802,1806,1807,1808,1810,1816,1851,1903,2301,2309,2312,2315,2318,2324,2328,2329,2330,2332,2335,2336,2340,2341,2363,2371,2372,2373,2382,2388,2389,2390,2398,2430,2440,2453,2456,2466,2467,2468,2470,2479,2486,2505,2508,2509,2514,2520,2521,2528,2529,2536,2537,2540,2541,2542,2543,2544,2545,2546,2547,2548,2549,2550,2551,2552,2553,2554,2555,2556,2557,2558,2559,2562,2566,2588,2701,2861,2880,2881,3001,3004,3014,3023,3028,3031,3034,3035,3039,3042,3051,3057,3101,3104,3106,3111,3117,3205,3207,3210,3212,3213,3216,3222,3226,3422,3231,3282,3400,3408,3412,3422,3427,3448,3499,3502,3503,3504,3505,3601,3602,3603,3604,3605,3607,3608,3609,3610,3614,3625,3626,3628,3629,3635,3640,3654,3656,3659,3660,3663,3688,3689,3801,3861,3913,3917,3923,3924,3940,4001,4010,4013,4020,4022,4029,4030,4031,4032,4044,4052,4055,4101,4102,4103,4104,4105,4106,4107,4108,4109,4110,4111,4112,4115,4116,4117,4119,4120,4121,4123,4124,4125,4126,4127,4128,4129,4130,4131,4133,4134,4135,4136,4137,4138,4139,4199,4606,4610,4612,4613,4614,4616,4618,4620,4621,5004,5101,5102,5302,6498,6527,6530,6531,6532,6533,6534,7103,7300,7301,7311,7314,7352,7357,7359,7470,7412,7470,7471,7497,7498,7501,7502,7505,7525, 7601, 7603, 7604, 7605,7608,7613,7614,7646,7745,7752,7806,7826,7827,7833,8001,8002,8004,8008,8009,8010,8011,8012,8015,8016,8017,8018,8019,8020,8021,8022,8024,8025,8026,8030,8032,8033,8038,8040,8048,8049,8050,8075,8079,8202,8203,8206,8207,8209,8218,8295,8446,8501,8599,8602,8605,8606,8607,8608,8609,8612,8615,8619,8620,8621,8628,8633,8649,8666,8671,8675,8677,8679,8687,8690,8692,8719,8720,8721,8722,8738,8739,8744,8746,8755,8757,8758,8765,8780,8783,8790,8795,8809,8816,8901,8903,9024,9050,9052,9054,9055,9056,9059,9061,9062,9063,9065,9068,9079,9082,9109,9115,9130,9134,9136,9165,9185,9204,9205,9207,9209,9210,9259,9268,9275,9292,9347,9348,9349,9354,9358,9389,9394,9453,9471,9472,9476,9481,9482,9483,9485,9488,9489,9495,9532,9559,9567,9591,9592,9806,9807,9808,9830,9837,9851,9929,9939,9989,10202,10204,10207,10213,10316)
		BEGIN
		SET @CurrentTraceFlag = @CurrentTraceFlag - 1;
		END
	INSERT dbo.TraceFlags(TraceFlag, TestingNow)
		VALUES(@CurrentTraceFlag, 1);

	SET @Parameters	='-T' + LTRIM(CAST(@CurrentTraceFlag AS VARCHAR(5)));


	---------------------------------------------------------------------------------------------------------{Add Parameter}
	--select * from sys.dm_server_registry where value_name like 'SQLArg%'
	select @Reg_Hive=substring(registry_key,len('HKLM\')+1,len(registry_key)) ,@Argument_Number=max(convert(int,right(value_name,1)))+1
	from sys.dm_server_registry
	 where value_name like 'SQLArg%' 
	group by substring(registry_key,len('HKLM\')+1,len(registry_key)) 

	set @Argument= 'SQLArg'+convert(varchar(1),@Argument_Number)
	select @Argument,@Reg_Hive

	set  @CMD='master..xp_regwrite
	''HKEY_LOCAL_MACHINE'',
	'''+@Reg_Hive+''',
	'''+@Argument+''',
	''REG_SZ'',
	'''+@Parameters+''''

	exec  (@CMD)

	/* Restart the server in 1 second. */
	EXEC xp_cmdshell 'shutdown -t 1 -r -f';

END
GO

/* Set it to run at startup: */
EXEC sp_procoption 'usp_TraceFlagTest', 'startup', 'on'


/* Miscellaneous commands I ran while testing: 
EXEC usp_TraceFlagTest;

SELECT * FROM dbo.TraceFlags ORDER BY Success DESC, TestingNow DESC, TraceFlag DESC;
SELECT * FROM dbo.TraceFlagErrorLogs;

-- Clean out the trace flag table between tests
DELETE dbo.TraceFlags;
GO
-- Insert a first value starting point:
INSERT INTO dbo.TraceFlags (TraceFlag, TestingNow) VALUES (11500, 1);

Finding trace flags that didn't run:
SELECT *
  FROM dbo.Numbers n
  LEFT OUTER JOIN dbo.TraceFlags t ON n.Number = t.TraceFlag
  WHERE t.TraceFlag IS NULL
  AND n.Number > 9165 AND n.Number < 11500
  ORDER BY n.Number;

DBCC TRACESTATUS();


-- Dump one trace flag's error logs into a table for filtering:
SELECT *
INTO dbo.TraceFlagErrorLogsToIgnore
FROM dbo.TraceFlagErrorLogs
WHERE TraceFlag = 11500
-- Then go hand-edit those messages to make them LIKE-friendly for the next step

-- Find unusual strings in the logs
WITH Unusual AS (SELECT TOP 10000 t.Text, SUM(1) AS Recs
	FROM dbo.TraceFlagErrorLogs t
	LEFT OUTER JOIN dbo.TraceFlagErrorLogsToIgnore i ON t.Text LIKE (i.Text + N'%')
	WHERE t.Text NOT LIKE '%transactions rolled forward in database%'
	  AND t.Text NOT LIKE '%transactions rolled back in database%'
	  AND i.Text IS NULL
	GROUP BY t.Text
	HAVING SUM(1) <= 10)
SELECT t.* FROM dbo.TraceFlagErrorLogs t
	INNER JOIN Unusual ON t.Text = Unusual.Text
	ORDER BY t.TraceFlag;

GO
*/