Stack Exchange Network

Stack Exchange network consists of 175 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

Questions tagged [sql-server]

All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.

11
votes
0answers
588 views

Splitting Snapshot files with MaxBCPThreads for Transactional Replication

I've just set up a publication, and I'm attempting to get the snapshot to apply faster. So far the Distribution Agent is respecting the MaxBCPThreads settings, but the Snapshot Agent is not. I'm ...
9
votes
0answers
124 views

Incorrect process utilization from System health extended events?

I have been recently working on understanding the data or metrics being stored in system health extended events event file. We are trying to implement the data collection of performance metrics using ...
8
votes
0answers
340 views

UPDATE STATISTICS options fail in Amazon RDS SQL Server

In Amazon RDS SQL Server (any version/edition), create a temp table and update statistics on it. This works fine: IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t; GO CREATE TABLE #t (id INT ...
8
votes
0answers
181 views

Do I need a “downtime” to update a stored procedure? (Due to “definition changed” error)

I always thought that updating a stored procedure was something I could do while users were actively using the system. But I am currently testing out an update to a stored procedure and while a long ...
8
votes
0answers
141 views

Statistics disappearing/empty randomly throughout the day

I've got a SQL Server 2017 (CU9) database that is exhibiting some performance related issues that I believe have to do with the index statistics. While troubleshooting I discovered that the ...
7
votes
0answers
94 views

SQL Server 2016 & 2017 Force Plan failing with reason 8695

I'm trying to force a plan using query store but it keeps failing with reason 8695 according to data in SYS.QUERY_STORE_PLAN the last_force_failure_reason_desc is 'General_Failure'. Does anyone know ...
7
votes
0answers
93 views

What cases benefit from the Reduce, Replicate, and Redistribute join hints?

The From Clause Documentation starting with SQL Server 2008 briefly mention 3 join hints and their basic mechanisms: Reduce Replicate Redistribute However there does not seem to be much information ...
7
votes
0answers
257 views

Cardinality estimation on tables with large cardinality variance between keys

Context I have stumbled on an issue in SQL Azure where the Sort-operator is spilling over into TempDB due to poor row estimates. We are querying a master table that is joined with a number of detail ...
7
votes
0answers
344 views

CHANGE_TRACKING_CURRENT_VERSION() incorrect on database in standby

We are using a change tracking solution to export increments for BI purposes for years now. But it was running directly on the production database. To lower the load on the system we wanted it to run ...
6
votes
0answers
154 views

SQL dumping all pages from buffer cache every few minutes

I have a single SQL2012 SP4 node running several databases. The server has 20GB of memory available, 14GB allocated to SQL (nothing else running on the box). Every few minutes SQL dumps the whole ...
6
votes
0answers
95 views

dm_os_memory_cache_clock_hands understanding

As I mentioned in this question I'm trying to understand the working of clock hands. Simply put clock hands move when there is memory pressure so that cache entries can be removed or the cost can be ...
6
votes
0answers
232 views

SQL Server Profiler: why the RPC Starting event does use an different decimal separator from SP Starting?

Trying to diagnose an problem, I've ran into that inconsistency. In SQL Server Profiler, the RPC Starting event uses the regional settings to write real numbers (example: in Portuguese(Brazil) 14.5 ...
6
votes
0answers
848 views

SQLPackage : Import or Export SQL Azure BACPAC without users

I know how to use SQLpackage.exe. I can export from SQL Azure and create a bacpac. I can also Import to SQL Server via SQLPackage.exe. I want to export or import without the users in the database. ...
5
votes
0answers
55 views

CMEMTHREAD waits during Index Optimisation in Availability Group

In a 3 node Availability group, a secondary replica will often become subject to redo lag due to the causes covered in Microsoft's documentation: Troubleshoot: Changes on the Primary Replica are not ...
5
votes
0answers
146 views

Using the Schema Generation Wizard to regenerate a previously-generated schema

Lately, I've been trying to clean up a group of projects that my company received from an outside contractor. Among them is an SSAS cube, with an associated data warehouse. The contractors generated ...
5
votes
0answers
185 views

Online index rebuild higher fragmentation on intermediate level

I need to rebuild some big indexes and I'm doing some tests with the various options (sort_in_tempdb, maxdop, online) of the ALTER INDEX statement on an test index with 4 levels and 800000 pages on ...
5
votes
0answers
88 views

Deadlock in REDO thread , 1 replica went OK other didn't?

I have a 3 node cluster and the 2 secondaries initially went into SUSPECT mode due to a deadlock. I tried to resume both and only one caught up while the other is continuously deadlocking. I have to ...
5
votes
0answers
271 views

Should Full Text Filter Daemon Service be a cluster resource?

Our newly installed and configures Sql Server Always On Availability Failover Cluster with Sql 2016 Enterprise Edition with four instances and six nodes. It includes the DB Engine Service and the ...
4
votes
0answers
41 views

How to know which DMVs relate to each other?

Is there any way to know which DMVs/DMFs are related to each other? Some of them are well known or obvious, like sys.dm_exec_connections JOIN sys.dm_exec_sessions ON session_id = session_id, or the ...
4
votes
0answers
70 views

Should I use Transactional Replication here?

I have a database on SQL Server A & one on SQL Server B. (SQL 2017 w/ enterprise) They share similar objects and need to be kept fairly up to date. Both will require changes to be passed to each ...
4
votes
0answers
50 views

Authoritative Extend Event object list?

I am looking for an authoritative list of objects for extended events, I want to know what version an object becomes available and some clues about what the value really represents. I have googled ...
4
votes
0answers
173 views

Moving to version 2016 from 2008 and Breaking Change Alert from Upgrade Advisor

DBMS: SQL Server Upgrading from version 2008 to 2016. I have this part of the code that is being flagged by the Upgrade adviser as Breaking Change. Error: uses a constant expression which are ...
4
votes
0answers
2k views

Safely drop master key

I've restored a database in my Dev environment (SQL version 14.0.1000.169) and I'm using the wizard to create a "Availability Groups", when I try to select the database, I get a pop up "This database ...
4
votes
0answers
432 views

SQL 2016 Clustered Install - SlipStream - Updates not Shown

Attempting to install SQL 2016 with the latest SP and CU. Running the following (from the directory with the Install exe): setup.exe /Action= InstallFailoverCluster /UpdateEnabled=TRUE /UpdateSource=...
4
votes
0answers
649 views

Always On DTC Support on new database?

Is it possible in SQL Server 2016 Enterprise to add a new database to an Always On availability group and have it enabled for DTC support without recreating the AG? Our Availability Group is already ...
4
votes
0answers
259 views

Adding more tempdb data files gives more write latency

I am facing problems with tempdb please help and provide your suggestions. Initially, I have found tempdb contention problem in my server (Sql server) by using Simple talk Script. By using that ...
4
votes
0answers
597 views

BCP neither gives results nor outputs anything when using valid statements but it does throw errors when passing invalid parameters

I have to use bcp command-line tool to export data from an SQL Server database to a file in a Red Hat server. I am (apparently) using valid statements but bcp is not producing any kind of output/...
4
votes
0answers
706 views

Personal Finance database review

G'Day, I found that someone had started on this topic on personal finance, I like to go further using a good personal finance plan I found on the internet which I would model. In advance please ...
4
votes
0answers
979 views

Export sql azure DB as bacpac file without including data for certain tables

When I export a sql azure database as data tier application from sql server management studio I get the option to tick off certain tables but then these tables are not included in the resulting file. ...
4
votes
0answers
923 views

Upgrading SQL Server from 2008 to 2012 doesn't respect existing install / instance paths

Basic Information : Running Microsoft Windows Server 2008 with MS SQL Server 2008 (version 10.0.5869), trying to upgrade to SQL Server 2012. Currently working in a cloned VM of our production server (...
4
votes
0answers
117 views

Can SQL Server use a spatial index and another index at the same time and be efficient?

Let's say we have a table that contains two terrain datasets defined by TerrainModelID like below: CREATE TABLE [dbo].[DigitalTerrain]( [DigitalTerrainID] [uniqueidentifier] NOT NULL, [...
4
votes
0answers
3k views

MS SQL Server 2012 Failover Cluster connection issue

I was about to give up with this but I'll give it a go here if anyone can help. I've set up a fail-over cluster SQL Server with 2 SQL nodes, Domain controller and file witness. All seems to work fine,...
4
votes
0answers
4k views

SQL Server intermittent error 40 Could not open a connection to SQL Server or The network path was not found

I am getting intermittent errors as per the below stack trace. I am using C# Entity Framework with SQL Server 2012 enterprise running on a separate server computer. I can connect and query the DB ...
4
votes
0answers
1k views

Auditing access to a linked server

I have a linked server with SQL Server configured with the security option "For a login not defined in the above list, connections will: Be made using the login's current security context". What I ...
4
votes
0answers
262 views

merge replication too big

I have multiple handhelds which sync their data via merge replication. From time to time, new core data is imported on the server-database. For various reasons I keep the old data in the databse and ...
4
votes
0answers
2k views

Preferred Methods of Indexing MS SQL Server Audit Data to Splunk

What is your preferred method for indexing MS SQL Server for Splunk? I am collecting audit data in various environments by various means (profiler, app logs, and extended events) but I hope to ...
4
votes
0answers
302 views

Conflict handling, SQL Server 2008 R2 Peer to Peer

I have a Peer to Peer replication topology set up between two SQL Server 2008 R2 servers. I was doing some testing on fault tolerance and conflict handling. I have conflict detection enabled, and ...
4
votes
0answers
628 views

SQL Server 2005 Replication

I am in the process of creating Replication between 2 Remote Servers, server 1 is the Distributor and Publisher and server 2 is the Subscription. server 1 windows 2003 server 192.168.10.1 connected ...
4
votes
0answers
191 views

How to Audit for Access with Cross Database Ownership Chaining

I've just joined the data team of a new company and we have a large SQL Server environment (100+ instances) and cross database ownership chaining is on by what I gather is a necessity. I'm looking for ...
4
votes
0answers
118 views

Merge Replication Optimization

I am running Merge Replication on Microsoft SQL Server 2012 through web sync. Is there a way to recognize when regenerating a new snapshot and applying it would be more efficient than running a merge?...
4
votes
0answers
540 views

SQL Server 2008 replication scenario

First of all sorry about my english, and most importantly, my database ignorance, I'll do my best ;) Until now, the scenario that I have is as follows: In a server under our LAN, we have a database ...
3
votes
0answers
67 views

SQL Server 2012+ & Service Broker: SSBDT Spam ending conversations after sending message to NULL conversation_handle

I'm mostly posting this for community awareness, but feel free to give feedback if you think there is a solution other than coding around the problem as we are doing. Crossposting this from my bug ...
3
votes
0answers
64 views

Is there a function like min_active_rowversion for Identity?

I'm storing a queue of events in Sql Server with concurrent writers and I'm assigning a sequence number using an IDENTITY column. I'm willing to accept the gaps that might occur, but I would like ...
3
votes
0answers
101 views

When using DAC, receiving the “[…] the maximum number of '1' dedicated administrator connections already exists” message in error log

I am about to escalate this to MS, but I thought I would try here first and see if anyone has any ideas as to how to prevent this faux error message... When connecting to SQL Server (2016 and 2017 ...
3
votes
0answers
66 views

Slow Connection to Azure SQL Database using SQL Server Authentication

In SSMS, when connecting to any of our Azure SQL Databases using service accounts and SQL server authentication, the connecting itself can take between 10 to 15 seconds to establish. Once connected, ...
3
votes
0answers
67 views

how does CXPACKETS relates to CXCONSUMER wait types?

when running a delete query to a table that has many foreign keys I get the following waits. I noticed that I have both CXPACKETS and CXCONSUMER wait types at the same time during of the execution ...
3
votes
0answers
129 views

Combine multiple stored procedures into one report?

This is my first time doing this... For my assignment, I need to combine multiple stored procedures into one big report using a cursor. There is only one table Debt from the database college. Here is ...
3
votes
0answers
103 views

Difference between sqlserver.nt_username & sqlserver.username

I am just learning about SQL Extended Events. I have a small event and for actions I have selected both sqlserver.nt_username & sqlserver.username When I test connect with a NT account the ...
3
votes
0answers
72 views

What Triggers sp_recompile in database without triggers, stored procedures, etc?

In a database with only tables and indexes (ie no stored procs, functions, triggers, etc). We get a call to sp_recompile that blocks table selects / updates randomly (ie once a month but in a highly ...
3
votes
0answers
196 views

PAGEIOLATCH_EX Wait on Target table of insert..select query

We are running SQL Server 2012 on Windows 2008 R2 with 256GB RAM and 90% memory allocated to SQL Server. No other Apps run on the server. I have a scenario where I’ve to copy a Source (staging) table ...