Useful traceflags

Trace flags in SQL Server modify how database engine works. Those changes can affect SQL Server globally, making impact on performance of whole workload, or be limited to only one active session where the flag was set.

!!!Please always be careful when using trace flags in production and test extensively before setting them on production server!!!

You may and should check if there are any trace flags set when troubleshooting server issues.

more info at source: DBCC Tracestatus

dbcc_tracestatus

we can see here trace flags set and what is their scope.

Few useful Trace flags in daily DBA life:

  • 3226 – suppress information about successful backup, quite handy if you are running transaction log backup frequently on many databases.
  • 3042 – SQL Server skips “pre-allocation” of of space for compressed backup and writes backup as it goes. May have negative impact on performance of backup operation. Useful when backup storage is used almost completely.

More trace flags will be added as I will have possibility to use them and time to write about it 🙂

SQL Server 2016 release date anounced

New version of SQL Server will be available in few weeks – it will be released on June 1st 2016!

As of now there is available Release Candidate 3 to download it please go here (registration required).

For those who will wait with upgrade of their systems there is also good news – some improvements to database engine from SQL Server 2016 will be also available in SQL Server 2014 SP2. More about it soon