Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

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 ...