How to set SQL Server trace flag 610 on

SQL Server implements minimally logged operations to support high-volume data loading scenarios. Minimally logged operations will speed up your loading operations. It will also reduce the amount of log entries which implies less disk usage by the database logs or ldf files. SQL Server 2008 introduces trace flag 610, which controls minimally logged inserts into indexed tables. For almost all data warehouse implementations you want to enable trace flag 610 on your SQL Server instance.

It is best practice to enable trace flags at the instance level. We will set the flag as a start-up parameter to the SQL Server service. This will ensure it applies to the instance level and will remain activated with server restarts.

  1. Firstly open the SQL Server Configuration Manager

SQL Config

  1. Right-click “SQL Server Service” and click on properties
SQL Properties

SQL Server Properties

  1. Next click on “Startup Parameters” and enter the value as shown below.
SQL Properties Startup Parameters

SQL Properties Startup Parameters

  1. Click Add.
SQL Properties Startup Parameters Changed

SQL Properties Startup Parameters Changed

  1. Click Apply. Click Ok twice.
  2. Right click on the SQL Server Service and select Restart.

Note: This applies to SQL Server 2008 upwards.

Eric Humphrey has a nice Powershell script to do the task for you without all the clicking. Thanks Eric!

 

Advertisements
This entry was posted in SQL Server 2014 and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s