Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
2
votes
1answer
28 views
How can you have a Buffer cache hit ratio of 100% with PLE of 103? [duplicate]
I found What does a "Buffer cache hit ratio" of 9990 mean? (I was getting result of 9000+ from a blog that was wrong) and used the solution by LowlyDBA/Denis Gobo combined with the PLE ...
0
votes
0answers
37 views
Maintain a large OLTP SQL Database
We are migrating a large OLTP database from Sybase to MSSQL Server. Currently this is been tested on Pre-Prod, after the bcp and inserts to SQL Server the database size is about 3.5TB (and it grows in ...
2
votes
0answers
17 views
Multiple Location AlwaysOn and Azure SQL Server
AS with the majority of projects, the original scope may not be a viable option. We were using Azure Data Sync and transaction replication to keep the 4 site databases in sync. However, the customer ...
2
votes
1answer
91 views
Memory Optimized table: Index size 10X bigger than data
On one of our production servers, I found out that Index size is 10X times bigger than data size.
These servers and schemas are identical with a similar workload.
CREATE TABLE [dbo].[...
0
votes
2answers
64 views
Issue migrating from SQL Server 2008 R2 to SQL Server 2017 Standard (on Linux)
I am currently trying to carry out a database migration from SQL Server 2008 to SQL Server 2017 (Linux) using the DMA tool.
I get the all green after running the assessment, however I get hit with ...
1
vote
1answer
41 views
How to use single domain name to connect to multiple database servers?
We have multiple Windows server 2016 machines hosting single SQL server instance on each server. Every server hosts ~100 databases. We have a master table in which has a record for each database & ...
1
vote
2answers
73 views
SQL Server Index and Statistics Maintenance performance question
I tried to use IndexOptimize stored procedure on a database with a very high number of indexes (> 250.000). The initial step where stored procedure collects data what needs to be processed takes hours,...
1
vote
1answer
36 views
Delete orphaned SQL Users
I have a Microsoft SQL Server that had a database called SQLDATABASE. It also had a user called SQLUSER. The SQLDATABASE was deleted but not SQLUSER. SQLUSER only belonged to SQLDATABASE. I want to ...
1
vote
1answer
31 views
How to create and fill multiple session temp tables with dynamic SQL?
I have a column that holds JSON data. The table can be filtered for sets of similar JSON data that will have mostly identical JSON keys in the JSON column. I can dynamically collect the set of JSON ...
0
votes
0answers
13 views
SQL Server 2017 two different Netbios names
Situation:
Migrating SQL Server database from 2003 to SQL Server 2017 moving to new hardware. SQL Server 2003 is on a machine with a netbios name of "Albert" on port X.X.1.22
Installed new SQL ...
0
votes
0answers
10 views
What are the service running for mssql server on linux, and how to check if they are running
On microsoft's site I can see the mssql-server on linux has following components
Mssql server (database engine)
SSIS (integration services)
full text service
High Availability
I can only see if ...
2
votes
1answer
69 views
Bind dll in SQL Server
I have a little side project at my work. My task is to deploy some SQLCLR code into our dev-database (SQL Server 2017) to test it. If the tests are successfull, we want to develop triggers and ...
3
votes
0answers
68 views
When using DAC, receiving the “[…] the maximum number of '1' dedicated administrator connections already exists” message in error log
I am about to escalate this to MS, but I thought I would try here first and see if anyone has any ideas as to how to prevent this faux error message...
When connecting to SQL Server (2016 and 2017 ...
0
votes
1answer
23 views
Resources used by ExtendedEvents
Does anyone know how to tell how many resources (CPU/MEM/Reads etc) have been used by SQL Server Extended Events?
I've went through the available DMVs here
But I dont see where this is tracked ...
1
vote
1answer
69 views
Updating values in table based on ranges defined in another table
I have two tables definitions and history with the following schema:
create table definitions (
id UNIQUEIDENTIFIER,
revision INT,
majorVersion INT
);
create table history (
id ...
0
votes
1answer
45 views
Execution plan for staging In-Memory tables estimate row count =1
We are using In-Memory tables (durability schema only) as temporary staging tables for part of the ETL process.
the nature of these staging tables is empty most of the time.
The ETL process executed ...
3
votes
3answers
187 views
All SQL Server aliases linked to default instance instead of instance I assigned
I have multiple SQL Server instances(different versions) on my computer.
I try to create aliases for each instance as
First I thought I had everything settled then I found no matter which alias name ...
2
votes
1answer
154 views
How to lock a MSSQL (2017 Linux) account after N unsuccessful login attempts
I have to configure account lock after N unsuccessful login attempts on MSSQL 2017 Linux. That is standalone server and is not in AD.
I couln'd find any valuable information so far unfortunately. ...
0
votes
1answer
34 views
Incorrect GrantedQueryMemory(KB) Value in SSMS Dashboard report
Just checking by to see if someone else has witnessed the same and tried a fix or has been fixed:
Issue:
I ran below query on stackoverflow2013 database
select
ParentId as [Post Link],
count(...
0
votes
1answer
51 views
Can a query whose execution plan uses parallelization use the cores of a processor on a another server that's part of the same Availability Group?
If I have two servers (server A and server B) that are part of the same Availability Group failover cluster, and a query running on server A has an execution plan that uses parallelization, is it ...
1
vote
2answers
62 views
Trigger to update only if value is in lookup table
I only want to update the comment if a description for the event is found in the TagDescLookup Table. If the Tag isn’t in the Lookup table then don’t change the existing Value for the comment column. ...
1
vote
1answer
47 views
SQL Server Instance Root Directory & Shared Feature Directory on Partition with 64k Allocation Unit Size?
I am in the process of setting up new test servers to migrate to SQL Server 2017. For certain reasons I dont have the option to have a separate drive partition for just SQL binaries so my options are ...
1
vote
1answer
21 views
SQL 2017 CU10 on Windows Server 2019 - Availability Group Listener fails to obtain IP from DHCP
We have a few instances of SQL 2017 CU10 Standard on several Windows Server 2016 machines, and normally we create Basic Availability Groups with Listeners obtaining IPs from our DHCP (192.168.112.0/22 ...
4
votes
1answer
113 views
SQL Server Primary Login Restrictions
I have read only routing setup and working fine. I have a SQL login that accesses the readable secondary via the listener using ApplicationIntent=ReadOnly. However I want to prevent the user from ...
2
votes
1answer
86 views
Why does my database using the simple recovery model have such a large transaction log?
Not a duplicate with:
Transaction Log is big! But simple recovery mode is chosen!
Why is my transaction file growing when db is in simple mode and there are no waiting transactions
Reason: My log is ...
-1
votes
1answer
76 views
Restore database - Operating system error 5(Access is denied.) on Linux
I try to restore SQL Server 2017 Express database using sqlcmd tool on Ubuntu 16 Server.
My SQL command is:
RESTORE DATABASE [xxxxx] FROM DISK = N'/home/xxxxxx/DBBackups/xxxx.bak' WITH NORECOVERY, ...
4
votes
2answers
106 views
What is 'Log Memory' in Query Store 2017
In SQL 2017 there is a new execution metric, 'Log memory' other than that it was added in 2017 I am not finding anything about it.
Execution metric: (SQL 2017)
CPU time, Duration, Execution Count, ...
5
votes
2answers
157 views
How can I export Query Store data?
I have Query Store (QS) running on a SQL 2017 instance. Currently at RTM, with RTM CU13 currently in test, to be applied on prod in next month's patch window.
While most queries and reports return ...
2
votes
0answers
84 views
Aggressive Tempdb spill process
We are having an issue with aggressive tempdb spills that significantly effect overall server performance and, in rare cases, cause AlwaysOn failures.
Tested on SQL2016 and SQL2017 Enterprise (recent ...
0
votes
1answer
38 views
Should TempDB SSD Drive be formatted with 64 Kb Allocation Unit Size?
We have installed dedicated SSD drives for TempDB
First question - should these drives be formatted with 64 Kb allocation size ? Currently it shows 4 Kb (Bytes per Cluster = 4096)
Does TempDB ...
1
vote
3answers
40 views
Documenting Databases with Extended Properties - value is being overwritten
I have a query that views some information about an entire instance of databases. Using Extended Properties, I have also added information on database level to what customer the database belongs to, ...
2
votes
1answer
77 views
How can I identify non deterministic updates occurring in my SQL Server?
SQL Server books online states
Use caution when specifying the FROM clause to provide the criteria
for the update operation. The results of an UPDATE statement are
undefined if the statement ...
0
votes
0answers
42 views
MS SQL 2017: Generate estimate execution plan take more than 3 min
Sometimes generate Execution Plan (estimated) for simple query take ~3min
Query:
SELECT MAX(field) FROM table_name
WHERE Org = x and PartKey = y
The table is columnstore cluster index
SQL Server ...
-3
votes
1answer
41 views
Kimball Data: Modeling Data as Both Fact and Dimension with View
In Data warehousing, Kimball discusses modeling an insurance policy premium as Both Dimension and Fact.
I have the following table,
create table dbo.DimAutoInsurance
(
DimAutoInsuranceId int ...
1
vote
2answers
27 views
Upgrade SQL Server 2014 AG to SQL Server 2017 AG
Is it possible to migrate SQL Server 2014 AG (Windows 2012, SQL Server 2014) to SQL Server 2017 AG (Windows 2016, SQL Server 2017) using rolling upgrade method?
What I mean is
Build 2 SQL Server ...
8
votes
1answer
199 views
Why do I have multiple (unassociated) temporal history tables?
I have been setting up a proof of concept system which has a SQL Server 2017 back end.
The system uses temporal tables to record asset configurations and track changes over time.
I have a Data table ...
4
votes
1answer
260 views
Why are these T-SQL jobs from different SQL Server instances executed on the same instance (AlwaysOn Availability Groups)
Recently, our blocked processes dashboard has been reporting blocked processes around the time when we do our statistic update.
The reason was quickly found: an update statistics job step (T-SQL) ...
0
votes
1answer
38 views
Get the total usage (in percent) between two specified dates
I have table at SQL server 2017:
Number 1 represent the test equipment, it is connecting and 0 is disconnected.
need your help to define query that can give me the amount of usage (in percentage) to ...
-1
votes
1answer
33 views
Convert SQL Date MMDDYY varchar to Date?
In reading this,
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
http://www.sqlusa.com/bestpractices/datetimeconversion/
I cannot locate the ...
4
votes
1answer
235 views
Updating Statistics: Estimated Number of Rows not equal to Actual for Index Scan. Why?
I am trying to understand the effect that forcing the update of statistics with a fullscan has on execution plan estimates.
I currently have the following result in the execution plan for a very ...
1
vote
1answer
71 views
How to format the output of SQL queries on SQLCMD for SQL Server 2017 on RHEL Linux 7.3?
I have installed SQL Server 2017 on RHEL Linux 7.3, and using SQLCMD utility for querying the SQL Server.
When I try to query for example: 'SELECT * FROM SYS.DATABASES'. I get output like the ...
13
votes
2answers
1k views
Why is a temp table a more efficient solution to the Halloween Problem than an eager spool?
Consider the following query that inserts rows from a source table only if they aren't already in the target table:
INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT ...
3
votes
2answers
130 views
Table partition existing table where partition key is not part of the primary key
Main objective: Add partitioning to table to make deletion of old orders non-blocking/quicker (and also understand partitioning)
I have an existing table Order, like this:
CREATE TABLE Order (
...
5
votes
1answer
450 views
Is .NET Framework 3.5 still needed with a SQL Server 2017 installation to utilize Database Mail?
I'm guessing .NET Framework 3.5 is still needed to use Database Mail with SQL Server 2017? As I had to include this on my SQL Server 2016 installs on Server 2016 not that long ago.
I was unable to ...
0
votes
0answers
36 views
sys.dm_db_index_operational_stats not giving proper statistics in sql server?
While working on Index Analysis in SQL Server 2017,
I observed that sys.dm_db_index_operational_stats not showing index is getting utilized(column name range_scan_count, however when i checked same ...
1
vote
1answer
55 views
SQL Creating Error Log Table Benefits
We are conducting a datawarehouse project, moving data from Staging database to Kimball Data Warehouse.
What is the purpose of creating a custom stored procedure Error Log Table?
I am seeing a lot ...
2
votes
1answer
40 views
Decode URL encoded characters in inline table valued function?
I'm looking for a inline table valued function that decodes URL encoded characters (like %20 for space). reference
I see other functions but haven't seen one that is an inline table valued function. ...
2
votes
1answer
120 views
Calculating total “on row” bytes for each row … the easy way
We want to calculate the total "on row" storage bytes for each row in the table. As we understand it, we must add up the DATALENGTH() of each column while also accounting for NULLs and things like ...
25
votes
1answer
1k views
SQL Server 2017 crashes when backing up because filepath is wrong
I was trying to restore my database and SQL Server kept crashing. I would get a message in SSMS that said there was a network transport error (the connection dropped bc the crash). I checked the logs ...
0
votes
1answer
19 views
SQL Server 2017 - Extracting source and target column names within a string containing column mappings
I have the following format string stored in text (could be any number of columns):
col1_source|col1_target;col2_source|col2_target;col3_source|col3_target;...
I'm trying to come up with an ...
