Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
887
questions
-3
votes
2answers
59 views
i need answer for this problem [closed]
I need to get How many days each customer place order?
Table name - T_Order
OrderId OrderDate MenuItemId Quantity CustomerId
1 2021-01-02 1 2 1
2 ...
6
votes
5answers
1k views
Is it possible to do a partial transaction log backup with SQL Server 2017? I don't have enough disk space to do a full backup
Inherited a database server to maintain and found that the transaction log is 92 GB. My plan is to do the backup of that and get it brought back down to a regular size, then fix it. The problem is I ...
-1
votes
1answer
19 views
Why is the estimate (out of Stream Aggregate) so low?
I have a table like this:
create TABLE dbo.MyTable
(
TMyTableID bigint NOT NULL IDENTITY(1, 1),
LogArgumentID bigint NOT null,
LogID bigint NOT NULL,
LogTextID int NOT NULL,
FloatValue float NULL,
...
-2
votes
1answer
31 views
How to create partition function on two columns like integer and datetime
Can a table be partitioned in two ways?
For example, both with date and with the unit
if unit=1 and date > 2021-09-01 save rows in file1.ndf
if unit=1 and date > 2021-10-01 save rows in file2....
-2
votes
1answer
36 views
For how long will a query wait, and what will it do post the wait when offline index rebuild is running?
When an offline index rebuild is running, and a CRUD query is fired, assuming the rebuild takes couple of hours.
How long will the query wait for rebuild to complete?
What will it do once it ...
0
votes
1answer
49 views
Why I can't take differential backup of primary replica?
I am trying to take a differential backup of my primary database by connecting to the primary replica, but I can't do that. We use SQL Server 2017 EE with latest CU. Database is in full recovery model ...
0
votes
1answer
27 views
SQL Server 2017 freezing during bulk insert of large file into in-memory staging table
Running SQL Server 2017 CU20 Standard on a dedicated Dell PowerEdge with 28 cores and 192GB of RAM.
I have a stored procedure that processes large files (~7GB each) in a loop by bulk inserting them ...
0
votes
1answer
49 views
how to get result value JSON and send result Format JSON
I have Query for result JSON but I need result like this:
result:[ "B":{1628424359816,35},
"A":{1628424359816,1},
"B":{1628424380816,63},
"...
0
votes
1answer
34 views
Is there a way to customize SQL Server Performance Dashboard?
The report in SQL Server Management Studio presents System CPU Utilization broken out by a minute. Is there a way to specify the interval - perhaps by 10 seconds?
10
votes
1answer
181 views
Could I have an undetected deadlock?
Upon running sp_whoisactive on a server for the first time, I was greeted by something unexpected:
Two sessions had been running for 13 days but both of them seemed to be blocking each other. Taking ...
0
votes
0answers
31 views
How to troubleshoot SQLServerAgent startup
I wonder what can cause SQLServerAgent to stay in "starting" stage after restart. The service restarted successfully, but I can't get details of any job, or start/stop/delete with msdb ...
7
votes
3answers
481 views
SQL Server 2017 - CU25 - sp_pkeys - Wrong order
After our customers have installed CU25 there is a problem with the stored procedure sp_pkeys.
It may now return the wrong order if a primary key has multiple columns.
It can be tracked down in the ...
1
vote
1answer
64 views
Can I downgrade from SQL Server 2017 CU25 to CU20?
New instance of SQL Server 2017. I chose to install up to CU25 during the initial installation. Been using the database for a few months, but it seems to have a memory leak when using memory-optimized ...
0
votes
1answer
49 views
Select rows where result appears more than once
I have been looking all over StackExchange, testing various methods listed here, without any success.
I need to SELECT only users that have multiple sessions.
This is my beginning SELECT statement:
...
0
votes
2answers
58 views
Restore fails in single mode because exclusive access cannot be obtained
We have a script that runs every night, this is the relevant part of the script:
---- Restore DB from file system
print ''
print '----------------------------------------------'
print 'Restoring ...
0
votes
0answers
22 views
SSMS: Dragging vertical tab group divider and nothing happens
I've searched Google for "ssms vertical drag divider nothing happens" but can't find anything. Am I the only one experiencing this?
I'm using both SSMS 17 and 18, as some servers I work with ...
0
votes
1answer
52 views
SQL Server high SQLCONNECTIONPOOL consumption
SQL Server Version : SQL Server 2017(RTM-CU21) on Linux
Runing environment : kernel 4.19,Docker 18.09
Docker Image : docker pull mcr.microsoft.com/mssql/server:2017-CU21-ubuntu-16.04
Problem ...
0
votes
0answers
19 views
Give SQL Server DB Login permission
I have SQL Server 2017 instance with many databases, as an example, I have a database on this instant called DB_A, and I want to create a SQL Server user account to log in to this database (I will ...
0
votes
0answers
21 views
Database owner - why owned by a dbo? [duplicate]
I have a question regarding the database owner. When i see the database properties, it show that the database owner is [machine name]\administrator , but under same database, under the security tab- ...
0
votes
0answers
10 views
Roles owner : why not own by database itself?
I create a database role under specific database.
Would like to ask Why role owner owned by user, why not own by the database itself?
Second, dbo, for my understanding is the schema, why my database ...
2
votes
1answer
105 views
How can I improve query performance with filter on datetime column using an indexed view?
I have this table:
CREATE TABLE transactions
(
id NUMERIC(20, 0) NOT NULL PRIMARY KEY,
amount NUMERIC(18, 2) ...
0
votes
0answers
54 views
(Error : RESTORE HEADERONLY is terminating abnormally) when restoring SQL Backup from Standard edition to Developer Edition (Both 2017 Versions!)
The instances have the following versions
Source -->
Microsoft SQL Server 2017 (RTM-CU25) (KB5003830) - 14.0.3401.7 (X64)
Jun 25 2021 14:02:48 Copyright (C) 2017 Microsoft Corporation
Standard ...
1
vote
1answer
38 views
Identify the line of a procedure of a spid/session
In SQL Server 2017, how can I identify or pinpoint at which line of a stored procedure a spid (session) is currently at and what the execution plan of the current statement looks like?
0
votes
1answer
27 views
When should I Switch In from Stage Table to a SQL OLTP Partitioned Table
I have an OLTP Database with a big table (30M records a day / 400TPS). I want to implement Partitioning & Sliding Window on that table with Monthly Partitions plus a temp Stage-Table.
The time to ...
4
votes
1answer
209 views
ALTER INDEX REORGANIZE questions
I have few tables with indexes that are highly fragmented (over 90% fragmentation) and are big enough (tens of gigabytes) in size. Goal of defragmenting those is to reduce space consumption by above ...
2
votes
1answer
46 views
Speed the response time of the Query
So I have this query that I am using on an API endpoint, the query takes between 3-4 seconds to run, returning a small OFFSET of 10 rows. Is there a way I can put indexes somewhere or improve on ...
0
votes
1answer
25 views
SQL drop connection is similar with set single user command?
Would like to ask when i detach a database , i select the drop connection properties in the box. Is this drop connection properties is similar function as "set single user with rollback immediate&...
0
votes
0answers
16 views
How does sql server allow job to run ssis package when proxy doesnt have msdbagent permissions?
I have a ssis sql job step with run as proxy account. This is scheduled to run every 6 hours.
The proxy account has required datareader and writer permission on the accounts_db database to be able to ...
0
votes
2answers
43 views
Do the windows local admin group or domain admin group members automatically get the sysadmin permissions?
https://docs.microsoft.com/en-us/sql/ssms/agent/select-an-account-for-the-sql-server-agent-service?view=sql-server-ver15#windows-domain-account-permissions
This link says that:
member of the Windows ...
-2
votes
1answer
48 views
Delete millions of rows on a badly designed table [duplicate]
I have identified Millions of duplicated rows on a table I have inherited by using this query:
SELECT COUNT(*) AS NumRecords, AccessID, LEFT(SQLTEXT, 5000)
FROM Table
WHERE AccessID=5012
GROUP BY ...
1
vote
1answer
57 views
Create a Sequence with the calling user as the owner
Background
I have a SQL User that has the following permission:
GRANT CREATE SEQUENCE ON SCHEMA::seq TO SequenceCreator
GO
I would like to be able to have this user create a sequence that is owned ...
1
vote
1answer
190 views
Why is Index causing bad estimation and execution plan
SELECT ISNULL(SUM(CASE WHEN T4.f_in =12 THEN T4.f_money END),0) -
ISNULL(SUM(CASE WHEN T4.f_out=12 THEN T4.f_money END),0)
FROM [Credit] M WITH(NOLOCK)
INNER JOIN [Transaction] T4 WITH(...
1
vote
1answer
60 views
SQL Server Always ON - LOGPOOL_CONSUMER wait and replication issue
I'm facing a strange issue in one of my customers AG. There're 4 instances under this AG.
Staring a few days ago, my customer complains about some query (SELECT) timeouts issues. When I faced this ...
0
votes
0answers
36 views
Database Restore on two different servers but index fragmentation differs
We have 2 SQL servers in our test ENV, both with the same database on. There are some tables that are replicated from the DB on the one server to the DB on the other server. While we're testing we ...
0
votes
1answer
31 views
Calling function that takes user defined table type as input, in a select query, with data in the table type from the table selected from
Hopefully the title makes some amount of sense. Basically I have a scalar function that takes a user defined table as input, f_doCalc(@MyUserDefinedTableType). The table type has 2 columns, theValue ...
1
vote
1answer
162 views
Update Rows From Table If Table Exists Without Using sp_executesql
I have a situation where I need to run a query on a large number of similar databases. Most of them will have a table that I need to update some rows in but some of them will not have the table.
I ...
-1
votes
1answer
32 views
Efficient dimension and fact joining
I have large fact table, and a much smaller dimension table in a simple star schema:
--1.
CREATE TABLE dbo.Dim
(
Id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
CustomerName VARCHAR(2000)
)
--index
...
10
votes
3answers
1k views
DB backups have suddenly gotten smaller
About 8 GB has been trimmed off of the size of our main database. This is reflected in backups and when we look at free vs available space.
Everything seems to be working and we can't find any sign ...
1
vote
1answer
56 views
LISTENER_IP = ALL not working
I try to create an endpoint specifying to listen on any IP:
CREATE ENDPOINT [Hadr_endpoint]
AS TCP (
LISTENER_PORT = 5022,
LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
...
0
votes
0answers
44 views
Database Page Compression and ColumnStore Archive - is data compressed when in buffer pool and when sent to client? [duplicate]
One of benefits of Page Compression or ColumnStore Archive is reduced storage space requirements
Here I have two questions:
When compressed data is read from disk to buffer pool, in the buffer pool ...
1
vote
0answers
129 views
Speed up reading data from a table
Problem
We have a table that has just 44 rows, and one of the columns is varbinary(max).
This column stores XML files converted to varbinary(max).
Average length of data in this particular column is ...
2
votes
1answer
112 views
SQL Agent stopped recording history (SQL Server 2017)
I have a Dev server that has been running without issue for at least 2 years. This dev machine was a VM Clone of the Production server.
On 2021-06-05 something happened on this server that altered ...
0
votes
0answers
30 views
Searching text using LIKE, and Included Column
SELECT * FROM Table WHERE (Column1 = 'ED69K')
SELECT * FROM Table WHERE (Column1 LIKE '% ED69K%')
I have a wide table with 25 columns on the table. The above queries do need to do key lookup ...
0
votes
0answers
25 views
Can we track historically or see queries on server is landing on which schedulers
The ask may sound weird but we are seeing sort of awkward behavior. Or may be we do not have historical data to confirm if it is true.
For our 4 sockets 16 core processor SQL server with total of 64 ...
0
votes
2answers
42 views
Remove Query Tuning Assistant (msqta) schema and tables
When running the Query Tuning Assistant (using the New Database Upgrade Session) on SQL Server 2017 I have a new schema msqta created in the corresponding database as supposed, along with six tables ...
0
votes
1answer
63 views
How to reduce the maximum degree of parallelism at session level?
I have inherited a legacy application that includes a job that creates performance issue at instance level. The DBA told us about this and our team needs to fix the stored procedure that is the most ...
1
vote
1answer
112 views
Estimated recovery time and REDO queue size related to DB up time
Can someone help me figure out if my understanding is correct on the below:
On the AG Dashboard for my readable secondary replica I see:
Estimated recovery time (secs) - 4598
REDO queue - almost 24 ...
0
votes
0answers
49 views
Clustered Columnstore Index alignment on multiple columns
I have a fact table which is a clustered columnstore index.
It is also:
-partitioned by a single data date column.
-has a nonclustered, aligned, composite unique index (PK) on 6 INT columns, plus the ...
0
votes
0answers
29 views
Failover an Availability Group during a backup
This is a scenario I hope won't happen, but if I was to perform a manual failover on an availability group halfway through a database backup will this cause the backup to fail or would it continue?
I'...
0
votes
1answer
78 views
SQL Server 2017 and TLS - client requirements
I am testing something around SSL\TLS and SQL Server 2017. I have enabled TLS on a test SQL Server instance by creating a self-signed cert and doing the required steps to add it to the Windows ...
