Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

A SQL Server configuration option used to temporarily enable or disable specific server behaviours.

1
vote
2answers
40 views

Global only scoped trace flags in MS SQL Server

If the scope of the trace flag is global only does it mean I can't use it only for one connection while not affecting the performance of others?
2
votes
1answer
434 views

SQL Server Processor affinity with trace flag 8002 ON

I have been going through an article that explains the unwanted side effects of Setting a Processor Affinity in SQL Server. What I have come to know is that in most of the cases the default setting ...
2
votes
2answers
323 views

Monitoring deadlocks in SQL Server 2014

We are experiencing deadlocks on SQL Server 2014 and I am trying to capture as much information about them as I can. There is already Extended Events Session that captures xml_deadlock_report events ...
2
votes
0answers
40 views

how to tell which statistics SQL server references in query [duplicate]

Is there a way to check in SQL Server 2016 which statistics the optimizer referenced when compiling a particular query? My question is similar to How to check what stats were used in query, except ...
0
votes
1answer
53 views

How can I see the skip target value on demand?

I am reading Paul Randal's Investigating the proportional fill algorithm it says The skip targets are recalculated whenever a file is added to or removed from a filegroup, or at least 8192 extent ...
4
votes
2answers
347 views

ALTER DATABASE MODIFY FILEGROUP [filegroup] AUTOGROW_ALL_FILES

Does anyone know of a way to accomplish setting a filegroup to AUTOGROW_ALL_FILES without setting the database to single user mode? I frequently add new filegroups/files to a 24/7 production database ...
0
votes
0answers
77 views

Why SQL server ODBC driver 13 create invalid trace files?

I'm using 'Microsoft ODBC Driver 13 for SQL Server' installed on RHEL 7. As per configuration references I had update /etc/odbc.ini & /etc/odbcinst.ini working as expected. But when I set trace ...
4
votes
1answer
422 views

Suppress specific SQL Server log warnings and messages

I want to minimize the output of certain warnings and messages in the SQL Server 2012 logs, such as when an spid is killed. I am unable to find information on suppressing/expanding which messages are ...
0
votes
1answer
59 views

Where can I get a SQL Server Trace Dataset? [closed]

I need a huge SQL Server query trace file (like the profiler trace) with the SQL query text and the time-stamp. I need a trace of SQL traffic produced by a real application because I want to do some ...
3
votes
2answers
467 views

Enforcing option hint for a statement in a while loop is not possible

SQL Server 2014 Compatability level 110 uses old Cardinality Estimator. I am trying to enforce new CE for a statement. I am getting error "Incorrect syntax near the keyword 'option'." I need to do ...
3
votes
2answers
679 views

How to rotate trace logs in SQL Server

We are using 4032 & 3605 trace flags in SQL Server 2012 to get the expected log output. Now, we would like to rotate the log files on daily basis to archive it in Amazon Glacier storage facility. ...
0
votes
1answer
165 views

Why does DBCC TRACEON (1204, 1222) not work?

I have executed DBCC TRACEON (1204, 1222) on several servers. Some of them simply do not turn the trace flags on. The command brings the same expected informational message for each but some simply ...
2
votes
1answer
87 views

Trace flag 10260 on SQL Server 2016

Our SQL Server 2016 development servers have trace flag 10260 set on start up. The other servers that run SQL Server 2016 do not. I've searched high and low, but have not been able to find any info on ...
5
votes
1answer
310 views

Should we really turn on trace flag 1118 for every SQL instance?

It has been recommended that every SQL instance have TF 1118 turned on What is Paul’s recommendation for using trace flag 1118? Everyone should turn it on, on all instances of SQL Server, from SQL ...
39
votes
3answers
1k views

Methods for finding new Trace Flags in SQL Server

There are a lot of Trace Flags out there. Some are well-documented, some are not, and others found their way to default behavior status in the 2016 release. Aside from official support channels, ...
2
votes
1answer
759 views

Trace flag 2861 and what a 'zero-cost' plan actually means

I'm trying to get my head around trace flag 2861 and what it actually does with trivial queries? The blurb says: SQL Server typically won't cache plans for these trivial queries because the cost ...