SQL Server trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. Here are the long list of available trace flags you can set. Following are some of the most wanted popular trace flags you might set for your SQL Server. I usually enable them when I setup a new SQL Server. But please test them out in test environment before applying to production servers!!!

1117: When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow. Scope: global only.

Note: Starting with SQL Server 2016 this behavior is controlled by the AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES option of ALTER DATABASE, and trace flag 1117 has no affect. For more information, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

1222: Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema. Scope: global only. This is very useful for troubleshooting deadlocks.

2371: Changes the fixed auto update statistics threshold to dynamic auto update statistics threshold. For more information, see this Microsoft Support article. Scope: global only.

Note: Starting with SQL Server 2016 this behavior is controlled by the engine and trace flag 2371 has no effect.

3226: By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic. With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries. Scope: global only.

4199: Enables query optimizer (QO) changes released in SQL Server Cumulative Updates and Service Packs. QO changes that are made to previous releases of SQL Server are enabled by default under the latest database compatibility level in a given product release, without trace flag 4199 enabled. Scope: global or session or query.

To accomplish this at the database level, see the QUERY_OPTIMIZER_HOTFIXES option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Starting with SQL Server 2016 SP1, to accomplish this at the query level, add the USE HINT ‘ENABLE_QUERY_OPTIMIZER_HOTFIXES’ query hint instead of using this trace flag.

Two new features in SQL Server Management Studio 17.3

SSMS 17.3 comes with two new exciting features:

• Import Flat File Wizard
• XEvent Profiler

I really like these two features. The Import Flat File Wizard streamlines the import experience of flat files (.csv, .txt) with an intelligent framework (Microsoft Program Synthesis using Examples - PROSE), requiring minimal user intervention or specialized domain knowledge. We don’t have to go through the long and time consuming SQL Server Import and Export Wizard anymore. Hooray!!

Here is the step by step tutorial for Import Flat File Wizard.

XEvent Profiler is replacing the plain old SQL Server Profiler. Finally we got something in SSMS GUI easier to use the extended events. Here is the step by step tutorial for it. Cheers!

WAIT_AT_LOW_PRIORITY

Starting in SQL Server 2014, there is an option - WAIT_AT_LOW_PRIORITY for online index rebuild. We can use it to kill the blocking queries or stop the index rebuild job itself.

An online index rebuild has to wait for blocking operations on this table. WAIT_AT_LOW_PRIORITY indicates that the online index rebuild operation will wait for low priority locks, allowing other operations to proceed while the online index build operation is waiting. Omitting the WAIT AT LOW PRIORITY option is equivalent to WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

ALTER INDEX IX_My_Index ON dbo.MyTable REBUILD 
WITH ( 
    ONLINE = ON (
        WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 1 MINUTES , 
        ABORT_AFTER_WAIT = BLOCKERS)));

This query gives any blocking SELECT queries currently running 1 minute to finish executing or else this query will kill them and then execute the index rebuild.

Alternatively we can do:

ALTER INDEX IX_My_Index ON dbo.MyTable REBUILD 
WITH ( 
    ONLINE = ON (
        WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 1 MINUTES , 
        ABORT_AFTER_WAIT = SELF)));

The rebuild query would wait for 1 minute to check if there is still any blocking queries. If so, it would kill itself, allowing the other blocking queries to continue running.

SQL Server 2017 is available today!!

SQL Server 2017 is the first version of SQL Server which can run on Linux (RedHat, Ubuntu, SUSE Linux, macOS, Docker). It can be downloaded from here. However, not everything is on other platforms except Windows. No Reporting Services or Analysis Services, nor Machine Learning Services, transactional replication, Stretch DB, or File Table. Check here to see Unsupported features and services. Management tools is still only for running on Windows only, though you can use command-line tools on other platforms.

Here is a detail list of What’s new in SQL Server 2017. The things I like the most are:

• Adaptive query processing
• Automatic database tuning provides insight into potential query performance problems, recommends solutions, and can automatically fix identified problems.
• Graph query support
• SSIS scale-out
• Python in Machine Learning Services

Bob Ward has a post explainning why SQL Server 2017 is fast, faster and fastest database.

And another important thing is SQL Server won’t have service packs anymore starting from SQL Server 2017. Now it has a Modern Servicing Model for SQL Server. We will only see Cumulative Updates (CUs) and General Distribution Releases (GDRs).

Hello! Hexo and GitHub Pages! It is much more simple than I thought to migrate my blog site to GitHub Pages. Hexo is also AWESOME!!!

When I tried to restore a database backup to a new server with SQL Server 2008 R2, I got this weird message - _The file “mydb_log” failed to initialize correctly. Examine the error logs for more details.” Error: 3283, Severity: 16, State: 1. _I suspect the backup file was corrected. So I took another backup from the source. Tried the restore again. But it failed again with the same exact message. So I did some research. Found out the source database turned on TDE before, but it got turned off. It is a bug Microsoft still didn’t fix. I had to import the Certificate which was used for TDE to the new SQL Server. Then did restore again. It was successful. Now learned another trick.

<span class="kwrd">USE</span> master; 
<span class="kwrd">GO</span> 

<span class="kwrd">CREATE</span> MASTER <span class="kwrd">KEY</span> ENCRYPTION <span class="kwrd">BY</span> PASSWORD = N<span class="str">'Passw0rd!'</span>; 
<span class="kwrd">GO</span>


<span class="kwrd">CREATE</span> CERTIFICATE TDECert
<span class="kwrd">FROM</span> <span class="kwrd">FILE</span> = N<span class="str">'D:\TDECert.certbak'</span>
<span class="kwrd">WITH</span> PRIVATE <span class="kwrd">KEY</span>
( <span class="kwrd">FILE</span> = N<span class="str">'D:\TDECert.pvkbak'</span>
, DECRYPTION <span class="kwrd">BY</span> PASSWORD = N<span class="str">'Passw0rd!'</span>
);
GO

When I was trying to install ASP.NET Web Tools Preview 2 for DotNetCore on Visual Studio 2015, I got this error message. I have installed VS2015 Update 3 as required.

Then I followed the instruction to repair VS2015 Update 3. Still had the same error. I even uninstalled VS2015 completely. Then installed VS2015 again. But it was still the same. After searching online a little bit, looks like this is not a rare issue. And found out there are actually some options you can pass to the installation program. And solved this error.

The installation file is: DotNetCore.1.0.0-VS2015Tools.Preview2.exe. If you run:

You can see some options like this:

###

It still doesn’t seems to have an option for solving this error. But you can run this also with parameter SKIP_VSU_CHECK=1:

Now the magic just happened. No more errors.

SQL Server 2014 SP2 is just released recently. You can download it from here. It includes all the fixes from CU1 to CU7. There are a couple interesting things added in SP2:

• DBCC CLONEDATABASE (Transact-SQL) is introduced in SQL Server 2014 Service Pack 2: this is very useful for creating a quick clone of the database with statistics copied from the original database, but without the data. Then we can detach and move it to development server for doing the query tuning and performance trouble shooting.
• Adds a stored procedure for the manual cleanup of the change tracking side table in SQL Server 2014 SP2: Cheer for releasing the storage headaches.
• New query memory grant options are available (min_grant_percent and max_grant_percent) in SQL Server 2012: finally we got this in SQL Server 2014.

Microsoft has changed the way how service packs and (CU) cumulative updates for SQL Server are released. Now installing CU is recommended as important as service packs. And service packs won’t be released regularly as before.

• Each new cumulative update (CU) contains all the fixes that were included with the previous CU for the installed version/Service Pack of SQL Server.
• Microsoft recommends ongoing, proactive installation of CUs as they become available:
  • SQL Server CUs are certified to the same levels as Service Packs, and should be installed at the same level of confidence.
  • Historical data shows that a significant number of support cases involve an issue that has already been addressed in a released CU.
  • CUs may contain added value over and above hotfixes. This includes supportability, manageability, and reliability updates.
• Just as for SQL Server service packs, we recommend that you test CUs before you deploy them to production environments.

BTW, I found another SQL Server Updates information site which is maintained by Brent Ozar Unlimited.

Microsoft just announced SQL Server 2016 public preview coming this summer. A couple long waiting features will be really interesting:

• Always Encrypted: Banks, Health Care and insurance companies will be happy.
• Stretch Database: DBA will be happy.
• Real-time Operational Analytics & In-Memory OLTP
• Built-in Advanced Analytics, PolyBase and Mobile BI
• Additional security enhancements for Row-level Security and Dynamic Data Masking to round out our security investments with Always Encrypted.
• Improvements to AlwaysOn for more robust availability and disaster recovery with multiple synchronous replicas and secondary load balancing.
• Native JSON support to offer better performance and support for your many types of your data.
• SQL Server Enterprise Information Management (EIM) tools and Analysis Services get an upgrade in performance, usability and scalability.
• Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Azure and place your SQL Server AlwaysOn secondaries in Azure.

Hooray! Can’t wait to download it.