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.
- Firstly open the SQL Server Configuration Manager
- Right-click “SQL Server Service” and click on properties
- Next click on “Startup Parameters” and enter the value as shown below.
- Click Add.
- Click Apply. Click Ok twice.
- 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!