A community of more than 1,600,000 database professionals and growing

The Voice of the DBA

Using Trace Flags

At SQLServerCentral, we recently published a list of all the trace flags from Konstantin Taranov. It's a great list, and thanks to Konstantin for compiling it. While I haven't often used trace flags, there are some that have really helped me at various times when I needed to change SQL Server behavior. If you aren't sure what trace flags are, Erin Stellato of SQLskills wrote a great post recently. I see trace flags as feature flags. The development team can allow us to experiment, test, and use functionality at our discretion, or ignore it.

This week, I wanted to ask how many of you are using Trace Flags right now. Do you have any running in code or set for startup on your instances? If you don't know how to do this, we've got a short piece to help you.

In Erin's post, she notes that SQLskills only recommends three trace flags (depending on version) for their customers. In general, I think that's good advice. There is a risk with using flags, and certainly I would be wary of using without substantial testing. I do think Erin's list is good, and you might consider using those. I also become wary about trace flags that aren't embedded directly in code. I think these trace flags end up being hidden from anyone troubleshooting issues. After all, how many of you actually go to the Configuration Manager or the Services Applet and look for parameters?

I expect that most of you don't run trace flags on your instances. There may be some of you that have never heard or, or used, a trace flag in your career. That's fine, though I hope you use today to a) let us know, and b) educate yourself to ensure you know how to enable a flag you need one. If nothing else, add one to a test instance, and ensure you have the skills to actually make the change.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

NEW SQL Clone - version 1 now available!

Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. Try it free.

Featured Contents

Windowing Functions: Tell me when that changes

Michael Morin from SQLServerCentral.com

Windowing functions aren't just for analyzing numbers/dates. Using windows functions to analyze when a character field changes is a little know resource More »

SQL Server Bulk Insert Row Terminator Issues

Additional Articles from MSSQLTips.com

We often need to import data into SQL Server from a file. Sometimes unwanted end of line characters are part of the source file and these can create issues when importing the data. One of the ways to avoid this issue is to use SQL Server Integration Services (SSIS), but it doesn't always make sense to use SSIS for simple operations. Rahul Mehta explains that he uses BULK INSERT sometimes faces issues with Line Feeds and Carriage Returns. In this post, Rahul shows some examples and explains how to fix the issue. More »

Extending DevOps practices to SQL Server databases

In this free webinar, Steve Jones and Arneh Eskandari show how to use migration scripts as part of an automated database deployment. For the demo, they’ll be plugging Redgate’s Database DevOps solution into Git, TeamCity and Octopus Deploy. More »

From the SQLServerCentral Blogs - New Article – Application Database Security Design (Part 1 – Authentication)

Brian Kelley from SQLServerCentral Blogs

In conjunction with the webinar I gave last month for MSSQLTips, I’ve started an article series on application database security... More »

From the SQLServerCentral Blogs - VS Code PowerShell Snippets

Rob Sewell from SQLServerCentral Blogs

Just a quick post, as much as a reminder for me as anything, but also useful to those that attended... More »

Question of the Day

Today's Question (by Steve Jones):

Which of these enhancements makes AlwaysOn transport compression work faster? (choose 2)

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: AlwaysOn.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

Expert T-SQL Window Functions in SQL Server

Expert T-SQL Window Functions in SQL Server takes you from any level of knowledge of windowing functions and turns you into an expert who can use these powerful functions to solve many T-SQL queries. Replace slow cursors and self-joins with queries that are easy to write and fantastically better performing, all through the magic of window functions. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I run this command:

 Backup-SqlDatabase -ServerInstance .\SQL2014 -Database sandbox

I then run this:

 Backup-SqlDatabase -ServerInstance .\SQL2014 -Database sandbox -Incremental

If the default backup folder was previously empty, what files are now in the folder?

Answer: Just a sandbox.bak file with a full backup and a differential backup

Explanation:

The default action is not to include the INIT parameter. Both commands write to sandbox.bak, which will now contain two backups (a full and a diff).

Ref: Backup-SqlDatabase - click here

» Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2016 : SQL Server 2016 - Administration

Production SSRS Report Issue - Hi All, Two days back we migrated one database from one server location to another server location, But now when we...

AlwaysOn vs Replication vs Mirroring - I learned that replikation gives you workable copies and mirroring only a backup in case of the "main" database fails. Now...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Such a hard time grasping the basics and understanding the logic!! - Hello everyone, I am new to TSQL development and well to be honest programming itself. All my life I avoided writing scripts and...

Encapsulating complex logic without using a scalar UDF - I have business logic for date comparisons that go beyond the typical DATEDIFF functionality. For example, when determining the number...

SQL Server 2014 : Administration - SQL Server 2014

Remove all leading zeros from a column of data in SQL? - I have a data table called 'ItemNum' The column is a SKU, which is all numbers...the current format is: 028200136107 I need to...

Unable to fetch result from linked server - Hi Experts, In our production server, we are using one query to fetch the result from linked server, using one particular...

SQL Server 2014 : Development - SQL Server 2014

Update Query Performance - Hello Experts, Following update Query takes about 1 Millisecond to update a Product reserve under less Load. But when thousands of...

Covering Index - When and how do you use a covering Index?

Convert Cross Join to Inner Join - Hopefully this isn't too daunting given that I'm providing a rather lengthy piece of sample code. I understand that cross...

SQL Server 2012 : SQL 2012 - General

Incorrect sort order on using Top 1 with Unique Identfier Column views incorrect results in query - Hi Im using a view

Availability Groups Removed & Readded Database Problem :exclamation: - I had a bit of a server meltdown this morning that necessitated me removing one database from my availability group...

Unable to setup Subscriber - Hi, I'm unable to connect to remove server without mention of port number, but when I do that when specifying Subscriber...

SQL Server 2012 : SQL Server 2012 - T-SQL

Use dynamic database name in t-sql statement - Hi, I am sorry because I have duplicated this topic, Some one else tried to help me but I couldn't...

t-sql 2012 sort - In a t-sql 2012 view, I would like to sort the data by mailAddress. However a sort is not allowed...

Single quote for cross apply to use dynamic DBname - Hi, I have a sql query with cross apply function, it works fine when I set statically the DBname, but When...

SQL Server 2008 : SQL Server 2008 - General

OS error 665 - Not CHECKDB - Hi, SQL Server 2008 R2 Enterprise Edition - 10.50.6220 on a 2 node Windows 2008 R2 cluster. It's a dedicated host running...

SQL Server 2008 : T-SQL (SS2K8)

SQL code for the below - SERVERS DB's SIZES DATES RANKING ServerA DB1 14642 2017-03-05 06:00:13.193 1 ServerA DB1 14497 2017-02-26 06:00:11

Data Warehousing : Integration Services

Redirect bad rows from Execute SQL Task - Hi, I have an SSIS package with an Execute SQL task which executes a proc and inserts rows in SQL table....

Data Warehousing : Strategies and Ideas

Build an architecture for highly confidential project - Hi Guru, I've a project which dealing with confidential data, such as payroll, medical information and etc. All these confidential data...

SQL Server 2005 : Administering

Difference between db_datareader and db_denydatawriter - Hi Everyone, Please explain me the difference between db_datareader and db_denydatawriter. Thanks & Regards, Naveen

This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.

This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.