Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
480
questions
6
votes
2answers
229 views
What is the best option for High availability on a data warehouse?
I have a customer who have an existing 1.5Tb data warehouse and are currently planning for a complete refresh of the DW on a new environment. Their infrastructure manager has organised 2 servers with ...
1
vote
0answers
23 views
Availability Group Read Only Routing Not Working for Azure App Service that connects over Hybrid Connection
We have following config of Availability Group On-Premise:
server1.domain.com - primary
server2.domain.com - sync secondary
server3.domain.com - async secondary
sql-listener.domain.com - ...
2
votes
1answer
41 views
Availability Group Read Only Routing Not Working for SQL Logins
We have following config of Availability Group:
server1.domain.com - primary
server2.domain.com - sync secondary
server3.domain.com - async secondary
sql-listener.domain.com - availability group ...
3
votes
1answer
221 views
Why can't SQL Server optimizer use foreign key [duplicate]
I have the following tables(See below for complete reproducible SQL):
ToBeTransferredTemp (ID varchar(100),type varchar(100) NOT NULL, other...)
CODES(Key1,Key2,Key3, IDSTRING varchar(100), other...)
...
0
votes
0answers
21 views
How to Track Table Usage
I need to track usage for 1 table, and log how this table is accessed (select, insert, etc.) and by which logins
What is the proper way to do it using SQL Server Audit and Extended Events ?
If there ...
2
votes
1answer
55 views
Optimize query that only returns when the number of rows to be returned is less than an amount
I have a query that first counts the number of rows to be returned and, if it's below a limit, it returns those rows, else it returns nothing.
For context: I have a map that displays some points ...
1
vote
3answers
113 views
Slow query in production, bad execution plan or bad index?
I've just fixed a production performance issue by dropping an index and recreate it.
I suspect dropping the index also dropped executions plans that used it and one of them happen to be bad.
...
1
vote
1answer
16 views
Reporting Services Database on different version
Just wondering if anyone has come across this scenario and can provide some advice.
We have a vendor application that only runs on SQL Server Reporting Services 2014. However, all of our other ...
2
votes
1answer
28 views
SQL Server Configuration Manager is not showing SQL Services
I have newly installed following things on my system
- Microsoft SQL Server Management Studio 2014
- SQL Server 2017
- Visual Studio 2015
When I tried to open SSMS for the first ...
0
votes
1answer
27 views
How to find out which table/object the KEY or PAGE or EXTENT locks in the sys.dm_tran_locks belong to?
When I query sys.dm_tran_locks, a couple of questions arise:
I can see there are some locks that are placed at KEY, PAGE, EXTENT, RID level (resource_type column). How to find out to which table or ...
1
vote
2answers
136 views
Are views treated like a subquery in SQL SERVER?
I'm trying to optimize a view which grabs the most recent history record for an item based on some specific criteria. I wrote a query which ran fairly quickly, and then turned it into a view.
The ...
0
votes
0answers
13 views
Using Policy-Based Management to check backup history for an Availability Group database
I like using Policy-Based Management to do some simple "everything's okay" sanity checks and email me if something goes out of spec. Typically, I validate the time since the last full backup, both to ...
0
votes
2answers
48 views
Why Table Lock Escalation is happening in my scenario
I have a table Table1 that is updated in small chunks using below query:
update top (1000) Table1
set VarcharColumn1 = 'SomeValue'
from Table1
where ID in (select ID from Table2)
and ...
10
votes
1answer
238 views
Cardinality estimate outside the histogram
Setup
I'm having some trouble understanding a cardinality estimate. Here's my test setup:
the 2010 version of the Stack Overflow database
SQL Server 2017 CU15+GDR (KB4505225) - 14.0.3192.2
the new ...
0
votes
1answer
40 views
Disable dbo schema fallback when table doesn't exist in default schema
I've properly performed these SQL commands in SQL server 2017:
CREATE DATABASE DataTeamDatabase
USE DataTeamDatabase
CREATE LOGIN DataTeamUser
WITH PASSWORD = 'YourStrong!Passw0rd',
...
0
votes
0answers
15 views
SQL Server Windows backup restore on centos
I have installed sql server 2017 on my centos 7 machine. I need to restore my database backups from windows machine to this machine. I could successfully run filelistonly. However, for restore, I'm ...
2
votes
1answer
26 views
SQL Server seems to be ignoring specified backup file extension
I have created a maintenance plan using the Wizard and this is the SQL produced, per SSMS:
BACKUP DATABASE [FOO] TO DISK = N'C:\SQLBAKS\FOO\FOO_backup_2019_08_09_161656_6414445.diff'
WITH ...
3
votes
2answers
93 views
How to query data in backups?
I have an ASP.NET app using SQL Server 2017. There's a bug somewhere that's causing some records to be deleted, and I'm trying to recover the data and find the cause of the bug. We take daily backups, ...
-2
votes
2answers
51 views
get data from express edition db to developer edition [closed]
Hi I've installed freshly db of ssms developer edition and Uninstalled express edition but I've a issue that I forget to take back up of that database.I've same data in c drive program file MS SQL ...
2
votes
1answer
40 views
Big Amount of <free_space_in_bytes> in Buffer Pool's Database Pages
We were viewing sys.dm_os_buffer_descriptors using below query
select
d.[name] [Database_Name],
(count(file_id) * 8) / 1024 [...
0
votes
1answer
32 views
db_mail Mail Server Failure
Description/Background:
I have spent the last couple of days searching everywhere on the internet and have not managed to find a solution or a proper explanation of my issue. since a couple of weeks ...
1
vote
1answer
25 views
Hourly differential backups with append option
With simple recovery mode, a nightly full backup, and hourly differential backups, if the hourly differential backup is set to "append" will the differential file continue to grow day after day after ...
2
votes
1answer
26 views
Msg 15404, Level 16, State 10, Procedure xp_logininfo - missing permissions [duplicate]
I have a server based in the USA, where I am not a domain admin, and my permissions are limited to sql server only - where I am sysadmin.
after a migration of a few databases to a new server, I ...
0
votes
1answer
19 views
Amount of used cache / free cache
I need to find out how much cache is used, and if there is free cache available.
Background:
Queries on a production server take a lot of time when the server is under heavy load. I suspect that ...
1
vote
1answer
80 views
SQL Server 2017 login language display is wrong
I have just done a clean install of SQL Server 2017 (CU15) on two of my servers, and have noticed that the displayed language for all my logins shows ARABIC instead of '' or 'English'.
Here is the ...
2
votes
1answer
40 views
How can I create a filegroup on SQL Server attaching a bare filename?
I'm trying to script a database creation from a Java program. I need to create 2 file groups, and link them to the database. In the example I can see in the official docs, I only see examples with ...
0
votes
0answers
54 views
SQL Performance post virtualization - sp_blitz and latency info attached
Full disclosure, I'm a VMware admin. We're trying to virtualize a SQL server that handles pricing information for multiple customers. Up until now, it has been a physical system with local SSDs. ...
9
votes
1answer
263 views
Why do MS SQL Server SEQUENCEs not have an ORDER parameter like Oracle?
In the documentation of CREATE SEQUENCE for T-SQL, you can see that the CREATE SEQUENCE command has no ORDER parameter.
For comparison, the Oracle docs for CREATE SEQUENCE show the ORDER/NOORDER ...
8
votes
1answer
163 views
Database Cache Memory in Performance Monitor drops down significantly after DBCC CheckDB
We have been monitoring some SQLServer: Memory Manager's metrics, and noticed that after DBCC CheckDB job, metric
Database Cache Memory (KB)
drops down significantly. If to be exact, it dropped ...
1
vote
1answer
46 views
Error after SQL-Server Update: The prepared statement with handle xyz was not found
In the last weeks we updated two of our customers. Both got a new SQL-Server and both have now this error: "The prepared statement with handle xyz was not found."
The error occures several times ...
0
votes
1answer
34 views
Is it possible to restore a database from corrupted .mdf file?
I know that the answer of the question may vary depending on what exactly is broken in each .mdf file - only looking for advice if there is another way to approach this.
So, I have received some bad ...
3
votes
2answers
57 views
Backup a database from Azure SQL Managed Instance and restore to on premise SQL Server
I have three databases that I am migrating to Azure SQL Managed Instance. One is a Utility database, just a small collection of generic functions and procedures. One is the OLTP database for a web ...
13
votes
1answer
255 views
Why does this RX-X lock not appear in Extended Events?
The Problem
I have a pair of queries that, under serializable isolation, cause an RX-X lock. However, when I use Extended Events to watch lock acquisition, the RX-X lock acquisition never appears, it ...
0
votes
1answer
50 views
Rename foreign keys en masse on SQL Server 2017
I have a database that was recently migrated from Oracle to SQL Server 2017. Whoever initially setup the database set all the foreign keys to be system generated names. In other words if I look at ...
0
votes
1answer
51 views
TempDB file overflow
This is my experience with TempDB. The Algorithm works better when
the TempDB File sizes are the same size.
We were seeing heavy TempDB contention on a TempDB file that was larger than the others. ...
2
votes
0answers
38 views
Distributed Availability Group Endpoint_URL Setup Question
I have two sites: SiteA and SiteB. Both sites are running SQL Server 2017 Enterprise v14.0.3048.4 on Windows 2012. Both sites are linked with a dedicated VPN connection. We have a series of ...
2
votes
1answer
37 views
Graphical Actual Execution Plan - Estimated vs Actual number of rows
I am using:
SQL Server Management Studio v18.1
SQL Server 2017 CU9
AdventureWorks restored from here
Test query:
USE [AdventureWorks];
GO
SELECT TOP (13) *
FROM Sales.SalesOrderHeader AS s
...
15
votes
5answers
423 views
Attempts to reclaim unused space causes the used space to increase significantly in SQL Server
I have a table in a production database that has a size of 525 GB, of which 383 GB is unused:
I'd like to reclaim some of this space, but, before messing with the production DB, I'm testing some ...
1
vote
1answer
45 views
SQL Server sys.dm_tran_active_transactions long running transaction worktable
today I used the system object sys.dm_tran_active_transactions (to be honest for the first time). I had an issue with distributed transactions (Microsoft DTC) and went digging there after I had no ...
1
vote
1answer
28 views
Prevent updating table using procedure in different database
This is my scenario:
Windows User Gabe has db_owner access of database DB1.
Windows User Gabe has db_reader on database DB2.
When the user executes UPDATE DB2.dbo.Tbl1 SET X = 1, they get "The ...
0
votes
1answer
61 views
SQL Server Agent Job - Unable to open Step output file. The step succeeded
I have seen many posts with "Unable to open Step output file.", but can't find any references where the step succeeded. I have my output file location set to:
$(ESCAPE_SQUOTE(SQLLOGDIR))\$(...
0
votes
0answers
28 views
How to reuse index space that was not created
My database is 34Gb in size and I created a clustered index which I believe was not successfully created due to some error and I can't find the index anywhere. During running of creation of Index, i ...
2
votes
4answers
71 views
Copy table from one server to another including keys and indexes
I have been working on a small project for about two weeks.
We want to consolidate a few tables from several remote server databases in order to run queries and reports on separate from the live ...
2
votes
2answers
57 views
Adding a database with Service Broker to an existing Availability Group on a new replica
Server 1 - SQL Server 2016 (Primary in existing AG)
Server 2 - SQL Server 2016 (Secondary in existing AG)
Server 3 - SQL Server 2016 (Offsite Secondary in existing AG)
Server 4 - SQL Server 2017 (New ...
0
votes
1answer
63 views
Does read-only activity on synchronous secondary replica slow down primary replica in Availability Group?
Suppose that we have an Availability Group configuration (SQL Server 2017) where primary and synchronous secondary do not share any resources
(each replica have dedicated CPU, memory, disk, etc.)
...
7
votes
2answers
172 views
Why cant i get partition elimination when partition column values are in a lookup table?
I created a partitioned table as you can see below. And seeded 480 million rows, about 181 rows per account.
I'm running baseline queries before adding indexes. And was surprised to see that doing ...
0
votes
1answer
24 views
Outputting result as 2 separate columns
So I created a procedure that calculates the average and median of a string array of numbers:
CREATE PROCEDURE dbo.spAvg_Median
@input nvarchar(20)
AS
BEGIN
declare @split table(id int ...
0
votes
0answers
31 views
How can I obtain a list of our applications' SQL statements that do not make use of indexes?
We have a system that processes thousands of statements a second, all running quickly. With so many statements we are looking to fine tune with small improvements to have a knock on large improvement ...
0
votes
1answer
68 views
Hard Disk is full of Sql server logs [closed]
SQL server created a lot of SQLDump.txt files in log folder I'm using SQL server 2017, but I have two databases in My SQL server. I find out my problem isn't about to shrink Ldf file I checked it out. ...
2
votes
1answer
408 views
How can I make this execution plan more efficient?
I have worked out all the implicit conversions, but I still see mentions of it in the plan. I have attached the plan, and any recommendation will help.
select cardholder_index, sum(value) as [RxCost] ...
