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,740 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
23
votes
0answers
1k 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 ...
14
votes
0answers
365 views

Constant Scan applying

This question is a continuation of optimizer behavior exploration in regards to VALUES construct started here and here. I want to ask about VALUES and APPLY this time. Using CROSS APPLY to alias an ...
12
votes
1answer
1k 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 ...
10
votes
0answers
271 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 ...
8
votes
1answer
762 views

Database Stuck 'In Recovery' with command XTP_DB_RECOVERY

We recently converted a table to memory optimized data. Our backups have all ballooned (3x300GB files to 3x600GB files for Full, 3x50GB to 3x250GB for Diffs), and startup has been getting ...
8
votes
0answers
527 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 ...
8
votes
5answers
2k views

SELECT query slow even with nonclustered Index and a full-text index

I am attempting to query between 0 and 65,000 rows from a table. The server is using Microsoft SQL Server 2014, and I have no way to change the hardware on the server. Schema [Id] (PK) INT ...
7
votes
1answer
82 views

Why does only one version of these procedures get blocked under moderate load?

The table: CREATE TABLE [dbo].[Session] ( [SessionId] UNIQUEIDENTIFIER NOT NULL, [CID] INT NULL, [DEST] VARCHAR (50) NOT NULL, [...
7
votes
2answers
34k 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). ...
7
votes
1answer
1k 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 ...
6
votes
0answers
414 views

SQL Server 2019 service terminated unexpectedly at startup but manually starts

I've had a SQL Server 2019 server working great for the past few months, but it started stopping by itself since last Friday. This morning, I installed latest cumulative update on it. Here is my ...
6
votes
0answers
238 views

Using a Table as a Queue without sp_getapplock/sp_releaseapplock

I have a list of commands I need to execute, all of which are contained within a table I've named myQueue. This table is a little unique in that some commands should be grouped together such that ...
6
votes
0answers
601 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 ...
6
votes
0answers
214 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 ...
6
votes
0answers
2k 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. The ...
6
votes
0answers
579 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
194 views

SQL Server Replication

We had an issue with replication, where a large (7.5 million row) update was done on a 33 million row table. Replication converted that to 7.5 million individual update statements. When my alerting ...
6
votes
0answers
1k views

sp_send_dbmail causes session to hang

We had an issue in production (2008R2 SP3) yesterday, where a developer was running a piece of code in SSMS that was executing sp_send_dbmail inside a transaction, and including an attachment from a ...
5
votes
0answers
168 views

Hash join hint returns an error when using local variable

The query below is using the StackOverflow2010 sample database: DECLARE @Id INT = 18471 SELECT c.UserId FROM dbo.Comments AS c INNER HASH JOIN dbo.Users AS u ON c.UserId = u.Id AND c.UserId = @Id ...
5
votes
1answer
487 views

How can Cumulative Updates to SQL Server 2019 be Slipstreamed into a Single Installer?

I'd like to take the latest version of SQL Server (SQL Standard, 15.0.2000.5), and apply the latest CU to it so that I have a single installer, thus reducing the size of the resulting package. The ...
5
votes
2answers
190 views

how to remove all permissions in a database?

every time I need to either restore a database or reinitialize a replication publication I first (on the restored to be or subscription database) I script all the permissions, triggers, indexes, ...
5
votes
0answers
90 views

Can Azure Backup Server backup databases participating in Log Shipping?

We have a number of log-shipped SQL 2017 databases which we wish to start backing up using Microsoft Azure Backup Server (locally and to the cloud) on a 15 minute schedule. Does anyone know if it is ...
5
votes
1answer
507 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
2k views

Very Sporadic Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server

OLE DB provider "SQLNCLI11" for linked server "001" returned message "Unable to complete login process due to delay in opening server connection". Msg 7303, Level 16, State 1, Line 19 Cannot ...
5
votes
1answer
350 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
1answer
2k 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
2answers
630 views

SQL AlwaysOn & System Center Configuration Manager TP 4 1601

Recently Microsoft released Technical Preview 4 for Configuration Manager (1601). Per TechNet, "Configuration Manager now supports using a SQL Server AlwaysOn availability groups to host the ...
5
votes
3answers
150 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
6k 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
798 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
0answers
3k views

Once more about SQL Server virtual accounts

Let me reboot my question on SQL Server virtual accounts permissions The (same) MSDN article on the same version 2012 of SQL Server "Configure Windows Service Accounts and Permissions" ...
5
votes
1answer
619 views

Memory-optimized table index size 10X bigger than data

On one of our production servers, I found that index size is 10X times bigger than data size. These servers and schemas are identical with a similar workload. CREATE TABLE [dbo].[...
5
votes
1answer
1k views

How do I identify the source of the 17886 errors ("server will drop the connection") in my SQL errorlog?

I have a SQL 2012 server that is throwing the following error every 5 minutes or so: Error: 17886, Severity: 20, State: 1. The server will drop the connection, because the client driver has sent ...
5
votes
1answer
4k views

SQL Server performance issue (CXPACKET wait type)

I have a performance issue with production SQL Server 2012 which has 24 cores and 32 GB of RAM. From wait type I can see CXPACKET is on the top based on the following SQL Server metrics, should I ...
5
votes
1answer
503 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
2k 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. ...
5
votes
1answer
619 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
138 views

Is there a more optimal way to create this view or stored procedure? Currently using a cross apply, but it's slow

I've updated this post with some test data. I'm creating a report for my Movies database where I'd like for the end-user to be able to select movies of a certain genre. However, some movies have ...
4
votes
0answers
104 views

SQL Server Network Packet Size

During the analysis of network traffic from Citrix clients to our database server, we noticed that we had a lot of RETRANSMITs occurring during the transport of TCP packets. The packets were being ...
4
votes
0answers
205 views

Can backups done through external clients cause memory pressure on SQL Server?

Developer in DBA role here, we recently migrated from 2008 to 2019 and are having memory related problems on the new server. Every 3 to 6 days the OS will start paging and CPU usage would spike to 99%,...
4
votes
0answers
523 views

MSSQL Ubuntu Unexpected call to legacy ABI

We recently had a Ubuntu 16.04 server loose power. After restarting the server I noticed that MSSQL Server was no longer running due to a an error Unexpected call to legacy ABI. After researching for ...
4
votes
0answers
100 views

Encrypted stored procedure is encrypting the plan cache query plan

I'm having a really bizarre issue when querying the plan cache that relates to stored procedure encryption. However, it's the stored procedure that is querying the plan cache that is encrypted, not ...
4
votes
0answers
1k views

SSIS Package failing to access UNC path it has permissions to

Trying to run an SSIS package that tries to access \\fileserver\share\file.txt, but the SSIS job returns: "File System Task:ERROR: File or directory "\\fileserver\share\file.txt" represented by ...
4
votes
0answers
98 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
103 views

Designing golf pro scheduling into POS

I have an existing Point of Sale app that is used for golf courses. I have a new feature request from one of my courses that would allow users of my POS to sell golf lessons to customers. Here are ...
4
votes
0answers
67 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
1answer
1k views

How to completely get rid of replication subscriptions?

I have restored a database in another server. I don't want any of the transactional subscriptions of the former database. I already called exec sp_removedbreplication 'MyRestoredDB' go And also ...
4
votes
0answers
4k 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
3k 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 ...
4
votes
0answers
2k 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 ...

1
2 3 4 5
75