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.

4,137 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
9 votes
1 answer
2k 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 ...
Obsidian Phoenix's user avatar
9 votes
2 answers
51k 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 (SQL Login): can do these commands (on ...
Dude Pascalou's user avatar
9 votes
1 answer
1k 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 ...
InMemoryHardships's user avatar
8 votes
1 answer
140 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, [...
sean's user avatar
  • 316
8 votes
0 answers
1k 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 ...
G.Smith's user avatar
  • 123
8 votes
1 answer
4k 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 ...
Peter PitLock's user avatar
8 votes
1 answer
2k views

Receiving error when running DBCC CheckDB - multiple IAM pages

Running DBCC CHECKDB (dbname) WITH NO_INFOMSGS, ALL_ERRORMSGS results in this error: Table error: Multiple IAM pages for object ID 813245952, index ID 1, partition ID 72057594073972736, ...
Eric Johnson's user avatar
8 votes
1 answer
2k views

Service Broker: Queue Monitor is dropped after poison message

I have searched everywhere and there isn't an answer online. There is one similar question on stackoverflow but it's not exactly the same and it has no accepted answer. When I setup event ...
Sergey's user avatar
  • 131
7 votes
0 answers
158 views

Find a previous statement or held locks in a blocking situation

I'm using XE blocked_process_report to detect and analyse the blocking. But since it's a point-in-time situation, I only see the blocking leader's statement currently being run and the incompatible ...
Zikato's user avatar
  • 3,881
7 votes
0 answers
76 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 ...
James Jenkins's user avatar
6 votes
0 answers
3k 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 ...
John K. N.'s user avatar
  • 17.1k
6 votes
0 answers
247 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 ...
Julian Goldsmith's user avatar
6 votes
0 answers
670 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 ...
LeonG's user avatar
  • 161
6 votes
1 answer
289 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 ...
Derek Czarny's user avatar
6 votes
0 answers
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 ...
AMtwo's user avatar
  • 16.1k
6 votes
1 answer
1k 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].[...
Yorik's user avatar
  • 193
6 votes
1 answer
2k 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 ...
BradC's user avatar
  • 9,784
5 votes
1 answer
362 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 ...
ck123's user avatar
  • 51
5 votes
0 answers
559 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 ...
JieLong's user avatar
  • 167
5 votes
1 answer
161 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 ...
Mark's user avatar
  • 879
5 votes
1 answer
606 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 (...
user avatar
5 votes
1 answer
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 ...
ReluctantDBA's user avatar
5 votes
0 answers
4k 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 ...
Drew's user avatar
  • 115
5 votes
1 answer
3k 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 ...
Daniel's user avatar
  • 61
5 votes
1 answer
118 views

How to calculate required ressources from a baseline and what should be measured at all?

I have to deal with the following situatiuon: Currently we have 1 cluster with 5 nodes running 10 instances (SQL Server 2008 Enterprise edition), and 1 cluster with 2 nodes running 5 instances (SQL ...
D.C.'s user avatar
  • 53
5 votes
2 answers
665 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 ...
Adam's user avatar
  • 51
5 votes
3 answers
166 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 ...
Tom's user avatar
  • 1,539
5 votes
1 answer
7k 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: '...
Jon of All Trades's user avatar
5 votes
1 answer
884 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 ...
Paresh's user avatar
  • 213
5 votes
0 answers
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" ...
Gennady Vanin Геннадий Ванин's user avatar
5 votes
1 answer
2k views

SQL Server Reporting Services Slow TotalTimeDataRetrieval Fast ExecuteReaderTime

I wrote a report that is executed by a few hundred users each day and the underlying stored procedure for the data set only takes milliseconds to execute. According to the ReportServer.dbo....
Registered User's user avatar
5 votes
1 answer
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 ...
Sam's user avatar
  • 409
5 votes
1 answer
677 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 ...
Sam Rueby's user avatar
  • 213
5 votes
1 answer
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. ...
Clement's user avatar
  • 466
5 votes
1 answer
963 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"...
ManOnAMission's user avatar
5 votes
1 answer
4k views

NESTING_TRANSACTION_FULL latch

Production SQL Server 2008 performance is far below expectations. Simple single-table queries sometimes are working for 5 seconds in the mid-day compared to 250ms after hours. We cannot find which ...
Abelevich's user avatar
5 votes
1 answer
235 views

MSSQL Server 2022 on Linux: sys.* tables very slow for non-db_owner users

Querying sys-tables, for instance sys.columns, sys.indexes or sys.tables, has become significantly slower on SQL Server 2022 on Linux for users which are not db_owner. To reproduce, take a SQL Server ...
final's user avatar
  • 315
4 votes
1 answer
2k views

Login failed for user '<token-identified principal>'

I have an Azure SQL Database as well as an Azure App Service. I use an App Registration to connect the user via Active Directory, I get the error Login failed for user '<token-identified principal&...
Aashishkebab's user avatar
4 votes
3 answers
282 views

Named instance throwing error 26 and won't connect

I've installed 3 new named SQL Server 2019 instances on a standalone server. The named install seems to have defaulted to computername\network name. Ex: BHMDB06\sseprod. During the install I had it ...
bwilliamson's user avatar
4 votes
0 answers
124 views

T-SQL: Iteration vs. Set-Based Operations When Independence of Ancillary Data is Needed

BACKGROUND I am using Microsoft SQL Server Management Studio v18.9.1 on Windows 10. I've created a database to store test results of our company's widget (not yet being produced). I am working on a ...
Dave's user avatar
  • 197
4 votes
0 answers
116 views

Log Backup occasionally blocked by automated update

We have a canary table in each of our databases that our automation updates once a minute. The automation is a sql server agent job that utilizes a cursor to iterate over the databases and then ...
DForck42's user avatar
  • 3,028
4 votes
1 answer
489 views

Different collation between master database and application database

I just installed SQL Server 2019. Unfortunately I didn't notice the collation while installing. Following are the collations: SELECT name, collation_name FROM sys.databases name collation_name ...
user avatar
4 votes
0 answers
88 views

Planning for application upgrade which connects to SQL server database

I am looking for some advise/guidance on how to achieve this with minimum downtime and possibly avoid any errors or failure during production change- Background- We have SQL database servers ...
Newbie-DBA's user avatar
4 votes
1 answer
2k views

Setting up cloud database for syncing to local SQLite database

Background I have a SQL Server database hosted on AWS RDS and there are web applications and WEB APIs that talk to the database. The database is a multi-tenant database and we are currently using SQL ...
keithm's user avatar
  • 141
4 votes
2 answers
4k views

Exclude certain schema along with unnamed constraints in SSDT

Task Automate database deployment (SSDT/dacpac deployment with CI/CD) The database is a 3rd party database It also includes our own customized tables/SP/Fn/Views in separate schemas Should exclude ...
Santhoshkumar KB's user avatar
4 votes
0 answers
443 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%,...
diffie's user avatar
  • 41
4 votes
0 answers
638 views

Remove table from DEFUNCT filegroup so we can recreate the table again in a different online filegroup

We have a BIG production database. We also have STG and UAT servers, where we restore the PROD database. One of the biggest tables (+250 GBs) in this PROD database is in the SECONDARY filegroup. We ...
Victor Barajas's user avatar
4 votes
1 answer
767 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 ...
DDiamond's user avatar
  • 143
4 votes
2 answers
2k views

Column length error querying SQLite via SQL Server Linked Server

I am attempting to query SQLite to copy data into corresponding tables in SQL Server. This is the first stage of an ETL process I'm putting together. Windows 10 Pro, SQL Server 2017 Developer Edition,...
alroc's user avatar
  • 1,664
4 votes
0 answers
1k views

SQL Server Replication Administrative Password not working

I have been struggling for the past one week to setup the replication in our environment and has been stuck on Administrative Password issue when adding Distributor on Publisher Server. Important ...
Ali's user avatar
  • 195

1
2 3 4 5
83