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.

3,275 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
15
votes
0answers
237 views

Implementation and contraindications for OPTIMIZE_FOR_SEQUENTIAL_KEY

SQL Server 2019 CTP3.1 introduced an optimization to address last-page insert contention. This takes the form of an index option called OPTIMIZE_FOR_SEQUENTIAL_KEY. One imagines this could be an ...
11
votes
0answers
658 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
1answer
745 views

HADR high worker thread usage

Why would an availability group's number of worker threads in an HADR pool increase well beyond minimum usage of "typically, there are 3–10 shared threads" per replica? In one case we've observed ...
8
votes
0answers
454 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
1answer
268 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 ...
7
votes
0answers
135 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
124 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
280 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
3answers
839 views

Foreign Key with a Constant

Let's say I have a table A, that has two columns: one is an ID for ThingA, and one is an ID for ThingB. The primary key is (ThingA, ThingB). Next, I have a second table, but this time it's restricted ...
6
votes
0answers
91 views

Can oscillating number of fragment decrease FTS query performance?

I've got a FTS Catalog on a table containing ~106 row. It used to work like a charm, but lately, for an unknown reason it started to get very bad (query >30 sec) performances randomly. Reading ...
6
votes
0answers
89 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 ...
6
votes
0answers
117 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
262 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
381 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
1answer
1k 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 ...
6
votes
1answer
894 views

Change Tracking across several Tables

I'm looking into providing DB level change tracking within my system. I need to be able to track changes at an entity level, rather than just individual tables. Right now, we have triggers on each of ...
5
votes
1answer
208 views

Reinitialize Table Values in SQL SSDT Unit Testing

I am creating SQL Server Unit Tests. We are testing various stored procedures. In Unit testing principles, it is good practice to setup a small database table, populate values, and tear down (...
5
votes
0answers
159 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
219 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
2answers
442 views

Deleting data from a table containing LOBs did not reduce the amount of data reported INTERNALLY by the table or database

Running SQL Server 2012 standard, using both replication data and log shipping. Transaction log backups are created every few minutes. This question is NOT about the amount of space reported by the ...
5
votes
0answers
99 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
1answer
259 views

Handling 'Variable not found' in a tokenised job step

I have an Agent job which is usually triggered by a WMI event. The job has a token within it, which automatically takes a value from the WMI event and substitutes it into the step's Powershell code, ...
5
votes
1answer
146 views

Extracted data WAY bigger than deficit left from deleted rows

I recently undertook an exercise to free some space up in a production DB (SQL Server 2008 R2) due to disk constraints. Just one table in particular takes up 98% of the DB. Records that were not ...
5
votes
1answer
1k views

Informix DB export to Microsoft SQL Server 2016

I have an Informix Database and a Microsoft SQL Server 2016. My task is to dump the whole Informix Database and export it to the Microsoft SQL Server Database. I installed the Informix SDK 4.5. I ...
5
votes
1answer
90 views

Any Tricks to Better Identify Disabled SSIS Elements?

Does anyone have any tricks they care to share that will help better identify disabled elements within SSIS packages? Ideally there's some manner to adjust the text color to red or or something along ...
5
votes
0answers
885 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
3answers
105 views

Optimizing table/indices for getting the latest row (filtering by one additional column)

I have a SQL Server table (SQL Server 2012 SP3 Standard edition) that stores a bunch of configuration information (basically text blobs) for different organizations. The schema is something like this: ...
5
votes
2answers
425 views

Multiple files per partition/filegroup?

I am designing a partitioning based solution for a high performance large SQL Server 2016 database. Some of the data will be coming in with hundreds of millions of records per day. During the day we ...
5
votes
1answer
231 views

Columnstore index and low selective columns

I have a table with almost 1T rows. create table bigtable ( K1 int, K2 date, -- PK columns C1 ..., C8 ...., -- columns with various data types like float, date, varchar, ... B1 bit, B2 bit, .......
5
votes
1answer
143 views

ObjID in Page Header

I am reading this article: Inside the Storage Engine: Anatomy of a page. I have a database MyDB, and there is a table MyTable in the database. I have the following questions: If I do as follows: (...
5
votes
1answer
109 views

Different Disk Drive Same Backend Storage

I have a database running on a VM which is getting hammered during a large load, in particular I can see waits of WRITELOG happening. My initial thought is to split the files out on to their own ...
5
votes
1answer
5k views

What causes “attribute key cannot be found when processing” errors?

I'm consistently getting variations on this error when I process the cube in my first SSAS project: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: '...
5
votes
1answer
645 views

Replication throw an error after failover mirror partner is down in SQL Server

Received an error mentioned below after failover when mirror partner is down. Error : to fail over to a database which is not configured for database mirroring. code: 22037, text: 'Invalid ...
5
votes
1answer
350 views

What is a non-atomic yield in the context of SQL Server?

There's a SQL Server performance counter for 'Non-atomic yield rate'. The documentation simply states that this is Number of non-atomic yields per second. I'm having trouble finding an ...
5
votes
1answer
16k views

The INSERT/UPDATE/DELETE permission was denied on the object 'TheTable', database 'TheDb', schema 'dbo'

I have two SQL Server user accounts on a database that behave differently on INSERT / UPDATE / DELETE. SQL Server version is 2008 R2 (10.50.4042, 64 bits). UserA can do these commands (on any table). ...
5
votes
1answer
318 views

Server Logon Trigger fails after deleting many records in “Logon-Table” -SQL Error 2801

We have a server logon trigger defined that logs every login with information like windows/sql user name, client and time in a table. The table doesn't have clustered index, but it does have a "PK_ID"...
4
votes
1answer
28 views

Truncate Table locking sys views

We have a proc that runs Truncate Table inside an Snapshot transaction. This seems to be causing a LOCK_M_S lock that blocks the sys view sys.partitions. Is there a convenient work around for this?...
4
votes
1answer
52 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
116 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 ...
4
votes
0answers
76 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
52 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
223 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
1answer
84 views

Attribute values to “pervious” and “next” rows for aggregation (and allow exceptions) in query + view?

I have a table in a database built on SQL Server 2014 which contains rows for "events", some events are attributed to User IDs while others events are not attributed to any user ID, values are '-'. ...
4
votes
1answer
83 views

CONVERT_IMPLICIT when inserting a Geography datatype into a Geography datatype column on Temp Table

@@Version Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) If I try to insert data into a geography datatype in a temp table I get a CONVERT_IMPLICIT, converting a Geography datatype to a ...
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
936 views

A row read and locked with UPDLOCK, ROWLOCK, READPAST is not subsequently available

For many years we had a simple queue system, where a regularly executed loop would select and lock a next available row and call a stored procedure to actually process it, which, in turn would also ...
4
votes
0answers
496 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
2answers
280 views

SQL Replication - Distribution database backlog of commands for one table

Our infrastructure is as follows: Publisher on Live db server, Distibutor and subscriber on our mirror server. Basically yesterday we were doing archiving on a huge table on our live server, (...
4
votes
0answers
712 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
1answer
286 views

Stuck with self-referencing linked server - will replacing the primary server name be better?

A vendor app uses 4-part names to reference objects. I am pushing back on that, but the current version is what it is. We installed the app using a SQL Alias for all the good reasons you do that, ...