Press "Enter" to skip to content

Category: Trace Flags

Trace Flag 3625 and Masking Failed Logins

Published 2024-12-11 by Kevin Feasel

Patrick Keisler notes an interesting data capture result:

Several months ago, I discussed my customer’s intention to enable trace flag 3625. Since that time, we have observed an intriguing phenomenon when encountering a login failure.

To demonstrate this, our initial step is to ensure we are capturing failed login attempts. In Server Properties, select the Security tab, click on Failed Logins Only, and then click OK.

Patrick has a link to a description of trace flag 3625, but the short version is that it masks sensitive information in error messages from non-sysadmins.

Leave a Comment

Options for Forcing Parallelism

Published 2023-12-28 by Kevin Feasel

Chad Callihan looks at a pair of options:

Just because something works doesn’t mean it’s the right thing to do. I had that type of challenge to my database morals recently when facing a query that refused to go parallel.

Read on to learn more. Note that neither of these relates to MAXDOP because that doesn’t determine whether a plan will go parallel (though you can use it to prevent a plan from going parallel).

Comments closed

Trace Flag 460 and String Truncation

Published 2023-07-05 by Kevin Feasel

Chad Callihan enables a trace flag:

In SQL Server 2016 and 2017, trace flag 460 can be used to gather additional details about string truncation errors. You may be familiar with the “String or binary data would be truncated” error message. Have you been left wondering what would be truncated? This is where trace flag 460 comes in. When enabled, the error message will include details on where exactly the potential truncation is taking place.

Read on to see if this is something you might benefit from enabling.

Comments closed

The Importance of Trace Flag 460

Published 2023-03-10 by Kevin Feasel

Jonathan Kehayias helps me understand that Trace Flag 460 is one of the best trace flags ever:

For most people that are reading this post, I’d venture to guess that you have no idea what Trace Flag 460 is or when you would use it. Well first off, let me tell you it’s a fully documented and supported trace flag, and it’s totally safe. In fact, on SQL Server 2019 and higher it is the default behavior when you create a new database. What does it do? It makes troubleshooting string or binary truncation issues easier by changing the error message that is returned from message ID 8152 and replaces it instead with message ID 2628. The trace flag is also available in SQL Server 2016 SP2 CU6+ and SQL Server 2017 CU12.

Read on to learn more about it and to remove a potential bit of confusion in the documentation.

Comments closed

A Trace Flag (Generally) to Avoid

Published 2021-09-15 by Kevin Feasel

Erik Darling takes us through trace flag 3608:

According to the docs:

Prevents SQL Server from automatically starting and recovering any database except the master database. If activities that require TempDB are initiated, then model is recovered and TempDB is created. Other databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work. Use for Move System Databases and Move User Databases.

Note: Do not use during normal operation.

Scope: global only

But it turns out it can do quite a bit of harm. It seems that many things stop working when it’s in use, though, including statistics getting automatically created.

Click through to see what kinds of things fail to work as a result of this trace flag.

Comments closed

Enabling Trace Flags in SQL Server

Published 2021-07-08 by Kevin Feasel

Robert Sheldon performs some level-setting:

SQL Server includes a set of configurable options known as trace flags. You can use trace flags to set server characteristics and control different types of operations. SQL Server offers a wide range of trace flags that let you modify the platform’s default behavior to meet specific requirements. Trace flags can help you when performing such tasks as testing stored procedures, diagnosing performance issues, or debugging complex computer systems. Microsoft Support might also recommend using certain trace flags to address behavior that’s impacting specific workloads. This article explains how to enable SQL Server trace flags.

Click through for the article.

Comments closed

Ignoring Backups in the SQL Server Error Log

Published 2021-05-21 by Kevin Feasel

Garry Bargsley has a solution to an annoyance:

Whether you are new to SQL Server or a seasoned veteran, you will notice odd behavior in the SQL Server Error Log. When a database backup is performed, an entry is put into the SQL Error Log. The SQL Server team decided to log successful backup messages to the Error Log. If you ask most technology professionals, you will find that logging successful events are not really a common occurrence.  This behavior causes a bloated Error Log that can make it hard to find what you need quickly.

Luckily, that same SQL Server team built in a solution to this situation.

Read on to see what the solution is, as well as how to use it.

Comments closed

Using Hints Instead of Trace Flags

Published 2021-01-28 by Kevin Feasel

Taiob Ali has a set of hints to use instead of setting trace flags:

Introduced in SQL Server 2016 (13.x) SP1 and Azure SQL Database is the USE HINT option. At the time of writing this article, there are 23 of those. You can see an up to date list by running Dynamic Management View sys.dm_exec_valid_use_hints.

With the introduction of this feature, we can replace some of the trace flags with hints. I personally like hints over trace flags (if I have to choose between two devils). That way someone reading the code have some idea what the hint is about instead of remembering the trace flag numbers.

I agree with Taiob on that sentiment.

Comments closed

Resolving Call Stack Symbols on SQL Server 2019

Published 2020-10-20 by Kevin Feasel

Paul Randal takes us through a change to SQL Server 2019:

After beating my head against the proverbial wall for an hour, I wondered if I had the wrong symbols somehow. I checked with the excellent SQLCallStackResolver tool from GitHub (authored by Arvind Shyamsundar from the Product Group) and that worked fine with the symbols I had, so it had to be something within SQL Server.

Read on to see the answer.

Comments closed