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

Questions tagged [sql-server]

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

10
votes
0answers
510 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 ...
7
votes
0answers
56 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
103 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
226 views

Blocking caused by Query Store. Unable to clear or disable

I recently updated our 2016 SQL Server to SP2 and the most recent CU (KB4458621) released in August 2018. Just in the last day or so, I noticed that I have some blocking going on. I can't kill the ...
6
votes
0answers
218 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 ...
6
votes
0answers
795 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. ...
6
votes
0answers
292 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 ...
5
votes
0answers
52 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 ...
5
votes
0answers
76 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 ...
5
votes
0answers
71 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 ...
5
votes
0answers
134 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
178 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 ...
4
votes
0answers
46 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
145 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 ...
4
votes
0answers
1k 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
76 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 ...
4
votes
0answers
256 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
519 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
643 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
944 views

DB snapshot goes into SUSPECT mode (SQL 2014 on WIN2K8R2)

I was hoping someone here might have encountered this sort of issue and if so what did you do to fix it? Here's some details: DB server: SQL 2014 Enterprise (12.0.4100.1) OS: Windows 2008R2 ...
4
votes
0answers
859 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
113 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
258 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
299 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
626 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
538 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
44 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 ...
3
votes
0answers
79 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
52 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
121 views

What's a pathological case where a bitmap filter would not allow the PROBE(Field, IN-ROW) semijoin reduction optimization?

I recently learned a performance optimization in SQL Server I did not know about ("Bitmap Filter On (Clustered) Non-Nullable Column Hash Semi-join Reduction via Scan + Probe IN-ROW" - a real mouthful),...
3
votes
0answers
77 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 ...
3
votes
0answers
184 views

Stored Procedure triggered by Service Broker can't access another database in SQLServer 2017

I make an extensive use of SQL Server Service Broker to trigger/parallelize data science tasks (data imports with SSIS or direct queries to another database on the same server, then data management ...
3
votes
0answers
195 views

Snapshot isolation transaction failed in database 'tempdb'

Upgraded from SQL Server 2008 R2 to SQL Server 2017. In SS 2008 R2 the application would occasionally get this error: “Snapshot isolation transaction failed in database 'tempdb' because the ...
3
votes
0answers
46 views

FILESTREAM database options cannot be set on system databases such as 'model'

Summary of Question My question is for SQL Server 2017 is there a way you can re-attach the model database (without rebuilding master or restoring master) if it has been dropped given that both of ...
3
votes
0answers
159 views

Why the disparity between Filestream size and Container folder size?

I have an issue with one of my SQL Server 2014 databases. It appears to me that the Filestream size is significantly larger than the actual Filestream container folder size? ~360Mb larger. The ...
3
votes
0answers
48 views

ASCII only connection to SQL Server

Let's imagine I have a legacy application that I would like to connect to a modern SQL Server. This legacy application only understands ASCII, but the SQL Server contains Unicode data in nvarchar ...
3
votes
0answers
133 views

Restore from Azure blob storage failing after backup overwrite

I'm attempting to backup a database (full backup, in simple recovery) to Azure storage, replacing a bak file that already exists, then restoring the new backup. Doing a regular backup and restore ...
3
votes
0answers
130 views

Optimize query without rewriting it - vendor app

Here is a query that uses a lot of CPU on my SQL Server (8 cores): DELETE TOP(1) rd FROM (SELECT TOP 99.9999 PERCENT * FROM APM_IisBb_Request_Details ORDER BY RequestDate ASC) rd JOIN ...
3
votes
0answers
81 views

In-Memory OLTP Non-Durable

I created In-Memory OLTP Non-Durable Tables in 4 different SQL servers.Lately one of the server performance has degraded and I see lot of processes (Almost 200-300) running with command XTP ...
3
votes
0answers
152 views

Msg 22830 level 16 on sp_cdc_enable_db_internal - could not update metadata that indicates database is enabled for Change Data Capture

I'm attempting to create an MCVE for this recent question. SQL Server is returning an error when attempting to update the system tables in master to indicate CDC is enabled for my test database. ...
3
votes
0answers
59 views

database restore with filestream on Windows Server 2016 renames data file

I have a database in SQL Server 2008 R2 sp2 that has filestream enabled. Restore of the database on a Win10 machine works as it should. However, on Windows Server 2016, the data directory (file) for ...
3
votes
0answers
530 views

Developer Edition install error code -2068119551

As stated above I am attempting to install SQL Server 2017 Developer Edition and coming up with the following error: Exit Code (Decimal): -2068119551 Error Description: Attempted to perform an ...
3
votes
0answers
71 views

Connecting SQL Server 2012 to MySQL

I have 2 devices, a server computer installed with SQL Server 2012, and a raspberry pi with MySQL installed. I have data in MySQL tables. How can I connect SQL Server 2012 to MySQL so I can save that ...
3
votes
0answers
39 views

Query plan shows high negative value and the server slows down

My SQL Server 2016 had been presenting problems certain days, all process since the morning begins to run slowly than normally. Sometimes updating statistics works, sometimes not and had to reboot ...
3
votes
0answers
951 views

Getting: Possible index corruption detected. Run DBCC CHECKDB

When I connect to the database and run a command on some catalogs, I get Msg 9100, Level 23 1> SELECT * FROM sys.databases; 2> GO Msg 9100, Level 23, State 2, Server x230, Line 1 Possible index ...