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,305
questions with no upvoted or accepted answers
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 ...
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 ...
8
votes
1
answer
162
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,
[...
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 ...
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 ...
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, ...
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 ...
7
votes
0
answers
136
views
SQL Server: how Row Goal affects the same index operation?
I am tuning some SQL performance in the SQL Server 2019, and I ran into issues with one query that involves the issue with the row goals. The simplified query is:
select TOP 1 ISNULL(RUNID,-1)
from ...
7
votes
0
answers
197
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 ...
7
votes
0
answers
84
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 ...
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 ...
6
votes
0
answers
254
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
0
answers
696
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
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 ...
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].[...
6
votes
1
answer
3k
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
0
answers
71
views
Why is the lock on the key not held?
When analyzing a locking error in SQL Server I found a design decision which causes deadlocks.
I want to understand why this decision was made. As for context: If somebody updates a table I have a ...
5
votes
0
answers
669
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
1
answer
169
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
1
answer
617
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
1
answer
2k
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
...
5
votes
1
answer
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 ...
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 ...
5
votes
1
answer
122
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 ...
5
votes
0
answers
739
views
An Indexed View slows updates to a crawl. Is there a hint to work around it?
I experimented with creating an Indexed View. I finally had a case which (almost) met the criteria for one. All inner joins, very few non-deterministic functions (which were moved to an outer view) ...
5
votes
2
answers
691
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
3
answers
167
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
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: '...
5
votes
1
answer
916
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
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" ...
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....
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 ...
5
votes
1
answer
708
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
1
answer
3k
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
1
answer
974
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"...
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 ...
4
votes
1
answer
87
views
How can I delete extended event files from within Managed Instance?
Simplifying a few details, I have a recurring process on SQL Server 2019 which runs every 10 minutes and does the following:
Reads data from extended event file targets using sys....
4
votes
0
answers
552
views
Non parallel plan with row level security predicate to check server role
While exploring Row Level Security I picked up that whenever I had it enabled it was not creating any parallel query plans.
In SQL Server 2019 this gave me
NonParallelPlanReason="...
4
votes
3
answers
705
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 ...
4
votes
1
answer
141
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 ...
4
votes
0
answers
148
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 ...
4
votes
1
answer
655
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
...
4
votes
0
answers
425
views
Why adding select into table is changing execution plan?
I am having following query:
DECLARE @VersionsIds TABLE(VersionId INT)
INSERT @VersionsIds(VersionId) VALUES (520)
SELECT
vEps.MasterPolicyId
-- INTO #Temp
FROM [Events].[vwEventMasterPolicy_Search] ...
4
votes
0
answers
107
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 ...
4
votes
1
answer
3k
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 ...
4
votes
0
answers
532
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
0
answers
730
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 ...
4
votes
2
answers
879
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
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,...
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 ...