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.
27,119
questions
0
votes
0answers
20 views
SSIS ETL Deadlock (Single Job)
I have a single SSIS Package on the server running via SQL Agent Job. Every now an then we are getting deadlocks issue. I used Breant Ozar's sp_BlitzLock to find out the deadlocks history and got ...
0
votes
0answers
36 views
SQL Server on Virtual Machine or not?
According to sqlserver new upgrades do you know any useful information about compatibility of sqlserver with virtual machines?
I want deploy sqlserver instance for a large system (about 15 terabyte ...
0
votes
1answer
44 views
Sql Server 2016 uses low RAM
I have an SQL server 2016,
version:
(SP2-CU4-OD) (KB4482972) - 13.0.5239.0 (X64)
The server is a Win 2016, with 64 GB RAM.
I have configured max memory for SQL to 54 GB, but it uses less than ...
0
votes
0answers
22 views
Uploading large files to SharePoint 2016 hangs up the whole server
We started to have an issue while uploading large files (> 6 MB) to an on-premise SharePoint 2016. The whole server stops responding once the file is uploaded to SharePoint (whether thru client ...
0
votes
1answer
18 views
Why VolumeQueueLength goes high for SQL Server in AWS
We have one SQL Server hosted on AWS EC2 machine. In the monitoring tab of one of the volume, we are seeing that VolumeQueueLength is periodically reaching to around 32 and then coming back.
So ...
4
votes
2answers
698 views
Dodgy T-SQL Query Execution driving me crazy
SQL Server 2019. Here's a link to a gist with the xml plan.
Hello, I am having a heck of a time understanding why this query is taking 0.02 seconds to execute when it can't find a record with one of ...
0
votes
1answer
32 views
How to show multiple record if has top 1
Hi currently having difficulty to query the result sorry a newbie here. i am trying to find the result of these two tables. i want to get all the records on table 2 with an effective date < 2019-12-...
0
votes
0answers
37 views
MSSQL 2016 - Restore SSISDB from MSSQL 2012
On "MSSQL 2016 (SP2-CU11) (KB4527378)" I created empty SSISDB. After that I restored SSISDB from "MSSQL 2012 (SP4) (KB4018073)" to "MSSQL 2016 (SP2-CU11) (KB4527378)" On DB I set owner to sa and ...
0
votes
1answer
26 views
Why hardcoded date filter works much better than dynamic parameter?
We have a stored procedure where we are getting the list of customer IDs. Within that procedure, we have a filter condition for Date. When we use hard-coded date there the query performance becomes ...
0
votes
0answers
48 views
OUTER APPLY 100% cpu immediate
I have a query white an Outer Apply which is consuming 100% cpu immediately.
Running the 2 separate queries individually gives immediate results.
select s1.correlationid, s1.ucc_id, s1.starttime, s2....
0
votes
1answer
20 views
SQL stored procedure to produce custom XML text
I have around 30 tables from which I need to generate a specific XML format, which is consistent through the tables.
With my current solution I have a stored procedure for each 30 tables where I do: ...
0
votes
0answers
32 views
Improve query performance: add indexes
I have a query that performs unacceptable. It runs more than 60'000 times within a business critical operation and I have problems tuning it. Will greatly appreciate help. Please, if possible, ...
0
votes
0answers
15 views
Aggragate values over time in temporal table
Recently we started using temporal tables feature in MSSQL. Now our analytics department wants to write query aggregating data on per month basis.
Let's say we have table
+----+-------+----------...
0
votes
1answer
11 views
SQL 2019 - Polybase -> Cosmos DB (MongoDB Mode) Error with dates
Im new to polybase. I have linked my SQL 2019 server to a third parties Azure cosmos and i am able to query data out of my collection. I am getting an error out when i try to query date fields though. ...
0
votes
1answer
26 views
How to find port using Get-DbaTcpPort
How to find whether the port used by SQL Server is static or dynamic using Get-DbaTcpPort dbatools command?
Below command doesn't show whether it's static/dynamic:
Get-DbaTcpPort -sqlinstance TEST
...
4
votes
2answers
390 views
SQL Query returning same value while using where 1 condition
I have created a temp table and inserted the values as given below.
create table #temp( val int );
insert into #temp values(333);
insert into #temp values(222);
insert into #temp values(111);
On ...
0
votes
1answer
17 views
Transactional Data Replication, SQL Server version compatibility
I've been following the Microsoft Tutorial: Configure replication between two fully connected servers (transactional). Everything has gone "smooth", however the 'New Subscription Wizard' is stopping ...
2
votes
1answer
35 views
Rounding problem with my result
I have a rounding problem with the result of a query as shown below, where it feels that somewhere along the calculation the result was truncated. The result is
The SQL query is:
SELECT
...
8
votes
1answer
119 views
Can STRING_AGG be used multiple times with different delimiters?
I was doing some string aggregation and noticed what looked like a bug to me. STRING_AGG was used twice in the same query with different separator arguments. However, both produced the same results (...
5
votes
2answers
250 views
Finding Missing Inverse Relationships
I'm struggling with a query which I feel should be straightforward but I just can't get my head around it.
We have a single table that holds relationships between different parties. The table SHOULD ...
2
votes
0answers
43 views
db_datawriter vs grant delete, update, insert
I have an application set up in which the service account uses a user defined role which has each object in the database defined with specific permissions. It seems that every table in the database is ...
1
vote
1answer
34 views
SQL Server strategy for high availability with batch drop/create updates [closed]
Have a production SQL Server 2017 db that is populated nightly using sqoop from another source beyond our control. Would like to fully sync certain tables without bringing down the app (right now we ...
0
votes
1answer
21 views
Error on open the master key
I create a copy of a database with DB Master Key in the same database server, this server is part of an activity group I run in both servers this script:
OPEN MASTER KEY decryption by password = '...
0
votes
0answers
50 views
What is wrong with the stored procedure? [closed]
Trying to run such query and getting such errors.
USE [CompeatSampleDev];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROC [dbo].[exeAuditLogJson]
(
@AuditionLogRecords ...
1
vote
1answer
56 views
Why only few of expected tasks are processed although many cores are available?
I push some data from one database to another on the same instance using Service Broker. 20 cores are online, 6 Service Broker tasks are activated, every taks is working on indepentent piece of data. ...
-1
votes
1answer
29 views
Convert DATETIME [closed]
I have to convert datetime:
2011-10-16 11.12.06
to
2011-10-16 11:12:06
I tried out:
SELECT CONVERT(VARCHAR(29), '2011-10-16 11.12.06', 120)
but still its returning '2011-10-16 11.12.06'.
...
0
votes
1answer
65 views
How to restart MS SQL server right, what is best practice to do that?
I`m quite new to DB administration.
I have a problem when I restart SQL server, one of the databases goes into Recovery mode and automatically repairs the DB in 30 min. I don't know why this happens....
0
votes
0answers
23 views
Combine two Pivot Queries from same table [closed]
I have one table in that I have a tuple like this:
Id
Cost
Revenue
Order_type
OrderDate
SalesMan
I want to get data in a column in months like this:
Jan_margin, Jan_Revenue
The margin will come as ...
0
votes
1answer
15 views
How to create multiple azure sql database in an elastic pool using powershell script?
I want to create a sql server and deploy the elastic pool on that server. Then create multiple azure sql database in that elastic pool using powershell script.
0
votes
1answer
17 views
One to many relation getting recent records plus one column from previous record
Table 1
ProductID Status
PD_1 OPN
PD_2 CLS
Table2
Table2_id ProductID Amount
1 PD_1 900
2 PD_1 800
3 PD_2 1000
4 PD_2 ...
0
votes
0answers
22 views
How to update from one table to another [closed]
Whats the best way to update 150 rows into one table from a temp table?
Temp table includes
Col1 col2 col3 col4
Table1 includes
Col1 col2 col3 col4
Both have values in them. Table1 has 1700 rows ...
0
votes
0answers
35 views
Can not run Sys.dm_exec_requests during backup restore
We do database creation through an application,i am seeing below error when running below DMV
query ran :
SELECT session_id as SPID,wait_type,wait_resource,last_wait_type,
command, a.text AS Query,...
-2
votes
2answers
81 views
Query sometimes take 25 seconds to run
After a set of usual data access queries run, SQL Server 2019 gets into a state where one specific query takes almost exactly 25 seconds to execute. Under normal conditions it takes 200 ms.
When I ...
1
vote
2answers
56 views
How do I join getting one row from the left table, no matter how many matches i get from the right table?
I have two tables - one is a data table and the other is a mapping table. I want to join them together, but only preserve the data from the left table. However, it is possible that the mapping table ...
2
votes
1answer
49 views
Are Clustered Index Usage Stats skewed since they are referenced in Non-Clustered Keys?
When using tools like SP_BlitzIndex and looking at usage stats for indexes. Would clustered index usage be skewed in any way since they are secret keys in all non-clustered indexes and would be read ...
0
votes
0answers
37 views
query start, due, complete dates for first and last time phases from phase tracking table
linked excel file
I have a phase tracking table which consists of the fields record id, phase name, record order number etc:
RECORD_ID,phase,RECORD_ORDER,SUBMITTED_DATE,DUE_DATE,COMPLETED_DATE
1657,...
2
votes
2answers
73 views
How to make DDL changes to large tables and cause minimum server and object contention?
I work with a lot of large tables (billions to tens of billions of records in each) in a database I recently inherited. I see a few clear DDL changes that would benefit the use-cases of the database ...
0
votes
2answers
46 views
Microsoft SQL Server equivalent to Oracle Enterprise Manger Top Activity Interface?
My DB performance management experiences is primarily with Oracle and I am looking for an equivalent set of tools in MSSQL. In particular something to easily spot historical SQL peaks and identify the ...
0
votes
0answers
19 views
SSRS 2017 permissions required to deploy
I'm having issues with one of our developers deploying reports to SSRS, they seem to be able to deploy datasets, however not reports themselves. As a temporary measure, we've granted all privileges e....
0
votes
0answers
31 views
SQL Agent running Ola scripts: all fine and perfect but a differential backup on a specific database fails
I've two SQL servers with multiple DBs. On both, I've set up maintenance tasks using the Ola procedures. One DB server is Enterprise SQL with Agent and the other is Express using Win scheduler to run ...
0
votes
0answers
31 views
Get-Counter not accepting array created from SQL
When I try and generate an array from a SQL output, although it's the same data type and content, perfmon doesn't accept these values, whereas it does for a manually created a list. So the first call ...
-1
votes
0answers
46 views
Huge difference in logical reads for the same query and same data in different executions [closed]
When capturing long running queries in SQL Server (with XEvents), I have realized that some of them perform a huge number logical reads (millions). However, when I run the exact same query a few ...
1
vote
1answer
28 views
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' in Agent job
In brief, I added a computed column to a table and then Agent jobs began failing and reporting the error:
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'...
0
votes
1answer
35 views
SQL Server Log Shipping for Server Migration - What's the final step?
Working with a server running SQL Server that was the log shipping target for two other servers as part of server consolidation/migration. In other words, server B was log shipping to server A and ...
0
votes
2answers
54 views
Running a query on the results of another query?
I have 2 queries, they both run fine and pull the results I'm looking for. However, I need to be able to run one query from the results of another. This is t-sql/mssql/sybase
For example, the ...
1
vote
1answer
44 views
Restoring a MS SQL Server database on Linux
I am running Manjaro Linux and I am trying to restore a Microsoft SQL Server database from a .bak file.
I am trying to follow the official documentation, but it doesn't seem to work!
➜ ~ sudo ...
2
votes
1answer
68 views
Procedure shows results differently in SSMS 18.4 than in SSMS 17.9
I have a complex stored procedure that queries specific network folders, collects the names of SQL Server backup files it finds there, and then processes each file for details. If I run the procedure ...
0
votes
1answer
47 views
Should a filtered index theoretically perform faster in this example?
I have a SuperheroMovies table which has all superhero movies, and has a column called "UniverseName" which has either the values "Marvel", "DC", "Capcom", "Unknown".
It also has columns called "...
0
votes
1answer
46 views
Update multiple strings with another at once in SQL [closed]
I have a requirement where, if column has values
1. pun/pat it should display like pune,patna
2. If only pun it should display only Pune
Thank you for any leads.
1
vote
1answer
26 views
Looking up which record(s) correspond to a specific resource description in dm_tran_locks
I've spent the better part of two days trying to figure out what exactly is being locked in one of my tables and why I'm getting deadlocks that seem to make no sense at face value.
I've found ...
