Stored procedure that continuously restarts SQL Server looking for undocumented trace flags
/* | |
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 | |
*/ | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment