SQL Server Trace Flags

Question

Post reply

SQL Server Trace Flags

Viewing 15 posts - 46 through 60 (of 69 total)

You must be logged in to reply to this topic. Login to reply

Ron Klimaszewski SSC Eights! Points: 911 More actions · Answer 1

Trace Flag: 1140

Purpose: Continuous tempdb growth after upgrading SQL 2005 pre-SP2 to SP3/SP4 or to SQL 2008 or newer

KB Article: http://support.microsoft.com/kb/2000471

First Added: SQL 2005 SP2 build 3166

Versions: SQL 2005 SP3+, SQL 2008, SQL 2008 R2, SQL 2012

Ron Klimaszewski SSC Eights! Points: 911 More actions · Answer 2

Trace Flag: 2470

Purpose: Slow performance when an AFTER trigger runs on a partitioned table in SQL Server 2008 R2 or in SQL Server 2012

KB Article: http://support.microsoft.com/kb/2606883

First Added: SQL 2008 R2 SP1 CU3 or SQL 2012 CU1

Versions: SQL 2008 R2, SQL 2012

Trace Flag: 3023

Purpose: How to enable the CHECKSUM option if backup utilities do not expose the option

KB Article: http://support.microsoft.com/kb/2656988

First Added: SQL 2005

Versions: SQL 2005, SQL 2008, SQL 2008 R2, SQL 2012

Trace Flag: 3042

Purpose: Don't pre-expand compressed backup at start of backup process

KB Article: http://support.microsoft.com/kb/2001026

First Added: SQL 2008

Versions: SQL 2008, SQL 2008 R2

Trace Flag: 3101

Purpose: Slow performance when you restore a database in SQL Server 2008 R2, in SQL Server 2008 or in SQL Server 2012 if CDC is enabled

KB Article: http://support.microsoft.com/kb/2567366

First Added: SQL 2008 SP3 CU5, SQL 2008 R2 CU10, SQL 2012 CU2

Versions: SQL 2008+

Trace Flag: 4123

Purpose: A query that has many outer joins takes a long time to compile in SQL Server 2005

KB Article: http://support.microsoft.com/kb/943060

First Added: SQL 2005 SP2 CU4

Versions: SQL 2005

Trace Flag: 4134

Purpose 1: Incorrect results or constraint violation when you run a SELECT or DML statement that uses the row_number function and a parallel execution plan in SQL Server 2008

Purpose 2: Results may change every time that you run a parallel query in SQL Server 2005, in SQL Server 2008, or in SQL Server 2008 R2 if the query uses a ranking function and if the computer has eight or more CPUs

KB Article 1: http://support.microsoft.com/kb/2589980

KB Article 2: http://support.microsoft.com/kb/2546901

First Added: SQL 2008 CU6 or SQL 2008 SP1 CU2

Versions: SQL 2008

Trace Flag: 8010

Purpose: SQL Server services cannot be stopped on a computer that has SQL Server 2008 R2 Express Edition installed

KB Article: http://support.microsoft.com/kb/2633271

First Added: SQL 2008 R2 SP1 CU4

Versions: SQL 2008 R2

Trace Flag: 9485

Purpose: Revert original permissions for DBCC SHOW_STATISTICS in SQL 2012

KB Article: http://support.microsoft.com/kb/2683304

First Added: SQL 2012 CU2

Versions: SQL 2012

Ron Klimaszewski SSC Eights! Points: 911 More actions · Answer 3

New 4199 Fixes:

KB2276330: The "CHANGETABLE" function is slow in SQL Server 2008 and in SQL Server 2008 R2

KB2698639: Performance of a SELECT statement that contains a LIKE operator and an ESCAPE clause is low in SQL Server 2008 R2

KB2649913: Performance might decrease when you run a query that uses the sp_cursoropen stored procedure after you upgrade from SQL Server 2005 to SQL Server 2008 or to SQL Server 2008 R2 or to SQL Server 2012

KB2260502: Query performance decreases after you upgrade to SQL Server 2008 or SQL Server 2008 R2 from SQL Server 2000 if the query uses a user-defined scalar function

Ron Klimaszewski SSC Eights! Points: 911 More actions · Answer 4

Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option

Trace Flag: 2549

Purpose: Parallel CHECKDB operations when using mount points

KB Article: http://support.microsoft.com/kb/2634571

First Added: SQL 2008 SP2 CU 9, SQL 2008 SP3 CU4, SQL 2008 R2 CU11, SQL 2008 R2 SP1 CU4, SQL 2012 RTM

Versions: SQL 2008, SQL 2008 R2, SQL 2012

Trace Flag: 2562

Purpose: Forces CHECKDB to use a single batch (may use more tempdb space); reduce contention on DBCC_MULTIOBJECT_SCANNER latch

KB Article: http://support.microsoft.com/kb/2634571

First Added: SQL 2008 SP2 CU 9, SQL 2008 SP3 CU4, SQL 2008 R2 CU11, SQL 2008 R2 SP1 CU4, SQL 2012 RTM

Versions: SQL 2008, SQL 2008 R2, SQL 2012

yup SSCarpal Tunnel Points: 4490 More actions · Answer 5

Hey, thanks a lot.

I'll verify & add these to the main list.

SQLCharger SSCommitted Points: 1958 More actions · Answer 6

Very nice, keep it coming!:-)

Cheers,

JohnA

MCM: SQL2008

yup SSCarpal Tunnel Points: 4490 More actions · Answer 7

yup

SSCarpal Tunnel

Points: 4490

July 12, 2012 at 4:56 pm

#1512350

All in place & published now.

rajasekhar.bollareddy SSC Enthusiast Points: 153 More actions · Answer 8

Hi,

How can I see default enabled trace flags in Sqlserver..??

yup SSCarpal Tunnel Points: 4490 More actions · Answer 9

The command is DBCC TraceStatus.

You can details here

http://msdn.microsoft.com/en-us/library/ms187809.aspx

Tim-153783 Default port Points: 1407 More actions · Answer 10

While trace flag -T8038 has been noted, I think it deserves more information.

Hp has a support Document ID: c02110402 that explains it a bit more than the KB article

DESCRIPTION

ProLiant servers that have multiple processor cores and that are running Microsoft SQL Server 2005 SP3 or SQL Server 2008 may stop responding when under a heavy I/O processing load. If Automatic Server Recovery (ASR) is enabled on these servers, the server may reboot when the server stops responding.

SQL Server 2005 SP3 and SQL Server 2008 use mmtimer (Multimedia) timer rather than the RDTSC timer, which changes the clock granularity (to 1ms). This change may result in clock-drift or an unresponsive server if the server uses enhanced power management technologies that change CPU frequencies.

Note: This issue may occur with any ProLiant server that supports power management technologies. Refer to the Affected Platforms included at the bottom of this advisory; however, other platforms may also be affected.

SCOPE

Any ProLiant server that supports power management technologies is running SQL Server 2005 SP3 or SQL Server 2008 on Windows Server 2003.

rajasekhar.bollareddy SSC Enthusiast Points: 153 More actions · Answer 11

I have implemented fulltext search and Rebuild indexes for this scheduled every 5 mins and its working fine but for every 5 mins of running this job its going to write the below message in SQLServer Error Log.

Message

Changing the status to MERGE for full-text catalog "CatalogName" (7) in database "DbName" (5). This is an informational message only. No user action is required.

How can I avoid this,I mean I do not want to write this into SqlServer Error Log.I have searched for trace flags but I did not find related to this.

Ron Klimaszewski SSC Eights! Points: 911 More actions · Answer 12

This is not the thread to ask these questions.

Find the message number in [font="Courier New"]master..sysmessages[/font] and then use [font="Courier New"]sp_altermessage[/font] as appropriate.

yup SSCarpal Tunnel Points: 4490 More actions · Answer 13

yup

SSCarpal Tunnel

Points: 4490

October 11, 2012 at 10:58 am

#1548281

Thanks Tim, I'll add those in the next update.

Rudy Panigas SSChampion Points: 10702 More actions · Answer 14

Great article!!! I would change only one thing to make it easier to ready for all DBA levels. Think if you put the actual version of SQL Server like SQL 2000, SQL 2005, SQL 2008, etc. it would make it a better read. Other than that, it excellent!!

Thanks,

Rudy

yup SSCarpal Tunnel Points: 4490 More actions · Answer 15

yup

SSCarpal Tunnel

Points: 4490

April 23, 2013 at 7:35 am

#1609050

Trying to keep it as concise as possible.