Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.