Questions tagged [sql-server-2017]

SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
0
votes
0answers
19 views

Setup certification for SQL Server running on Debian

I have successfully setup an instance of a SQL Server 2017 instance on the latest Debian release, and a domain that points to that server, and I have gotten the respective certificates by using ...
1
vote
1answer
32 views

Composite index performance with varchar columns

I have large fact tables with composite non-clustered indexes, something like this: NONCLSUTERED INDEX (OrderDate,OrderType,ClientKey,ItemKey,CustomerKey) The columns ending with "Key" are varchar(...
0
votes
0answers
44 views

Unable to attach SQL Server database with shared folder in docker on Linux

The problem I'm having is that my SQL Server 2017 Linux container is not able to read my database files. I guess it has something to do with permissions and I'm sure I've overlooked something but I ...
0
votes
1answer
22 views

Invalid object name 'ApexSQL.ApexSQL_SourceControl.Objects'

I was trialing Apex source control - trial ended. Uninstalled Apex (well I thought I did). Today I deleted the Apex database. Now I get Msg 208, Level 16, State 1, Procedure ...
4
votes
1answer
107 views

SSIS Package Not Being Deployed

I have an issue with an SSIS package, that when deployed via VS2017 to the integration services catalog is not actually being deployed. This is an individual package deployment, and not a project ...
0
votes
1answer
30 views

Query to get data from 2 different tables is taking long time

I have this query, select Column1,Column2, etc.. from Db1.dbo.Tb1 b Where b.Id1 in ( select distinct Column3 as Id1 from Db2.dbo.Tb2 s where s.Id2 <> '' union select distinct ...
2
votes
2answers
120 views

How to install SQL Server 2017 Express in “Quiet Simple” mode without an extraction folder?

I need to install SQL Server 2017 Express with as little user interaction as possible. I am using the SQLEXPR_x64_ENU.exe setup file that I found a Microsoft download for. The installer I am currently ...
0
votes
2answers
46 views

MS SQL Server cycle calculation

Dears, I have an problems with a calculation of this formula: Column "A" has always the same value for a group of SubIDs. In the "Result" column I need formula (function) like in the example columns ...
2
votes
1answer
28 views

Why are my Log Ship Jobs Blocking Each Other

I have 5 databases at a DR site all being log shipped to once a minute using the built in SQL Server log ship jobs. The server is running SQL Server 2017. For the last week the log shipping keeps ...
9
votes
4answers
2k views

Increased memory to server, but do I need to change page-file size?

I'm running SQL Server 2017 on Windows Server 2012 R2 and have increased the memory from 256 GB to 512 GB. I've noticed the following configuration for Virtual Memory: C: [OS] = 2 GB G: [DATA] = ...
1
vote
1answer
28 views

Can a rollback/commit occur, independent, for each iteration inside a cursor?

We have this ecosystem of accounting solutions and, basically, it got two tables: dbo.AccountFluxRecord ( Id, TotalCredits, TotalDebits, CreditMinusDebit, Application (the application ...
0
votes
0answers
19 views

SQL Server - How to know to which database user certificate is mapped to?

Inside a user database, I have two certificates, both are used for signing stored procedures First cert was created long time ago by someone Second I created myself for testing purposes For ...
0
votes
1answer
31 views

Can a trigger with an “insert into” statement cause Intext Exclusive locks for small insert operations?

We have a trigger that runs AFTER an insert in a table (Nodes) that creates entries in another table (NodeClosures) that selects entries (6 on average) from the NodeClosures table and inserts them as ...
0
votes
1answer
16 views

Migrate to sql 2017 and add ag's

A client wants to migrate from sql 2008 to sql 2017 and add availability groups. All enterprise. All vm's. Question: (without using any words starting with Az~ smile) should I set up the ag's first ...
0
votes
1answer
41 views

Will subquery (using IN) run multiple times for update statement (SQL Server 2017)

Where an update statement is updating the value returned by a subquery - will the subquery be run for each row? UPDATE a SET DM = 1 FROM TableA a WHERE a.ID NOT IN (SELECT DISTINCT ID FROM TableA ...
1
vote
1answer
23 views

Why MSDB transaction log file grew while MSDB was in Simple recovery mode?

I have Availability Group of 3 servers, and transaction log backups for databases in Availability Group are taken on a secondary replica, each 1 minute It was working like this for several months ...
0
votes
1answer
18 views

Table Partition Function Empty Partitions

I plan to partition my table monthly along a date column, and have read at few places, that it is recommended (in fact a best practice) to keep empty partitions at both "end" of the range. https://...
0
votes
0answers
42 views

Query on SQL HA/DR

DC1: we have two DB servers (DB1 and DB2) on top of WSFC DC2: 1 DB server (standalone DB machine). I have configured SQL Always-On on DC1 from DB1 to DB2. Now I have to sync/replicate data from DC1-...
0
votes
1answer
50 views

SQL Server - How to know which tables are used and which are not?

I have a database that has 225 tables in it What is the best way to find out / log which tables are used, and which are not ? I would like to see something similar to below: Table 1 - select 2222 ...
1
vote
2answers
69 views

Setting up Availability group for report server databases

I am reading msdn article on using SSRS with AG and have few doubts on setting up the same: Server A - Will host Report Server services with scale out deployment Server N1 - Node 1 with ReportServer ...
0
votes
0answers
24 views

Is there a way to access the LRU information in SQL Server Buffer Pool? [duplicate]

In SQL Server (2017) you can find all pages that are loaded into memory, and the page life expectancy etc. But is there a way to see the last time a page in the pool was actually read? The process ...
1
vote
1answer
34 views

Adding a date and the time portion of a datetime2 together [duplicate]

I have a date field [load_date] and a datetime2 field [load_time] that I want to combine into a datetime2. I want to combine load_date with the time portion of load_time and create a datetime2. It's ...
1
vote
0answers
15 views

Remote Connection to SSIS

I have just created a new SQL Server with SQL Server 2017 (14.0.1000) and SSIS (14.0). I am able to remote connect with the admin user on that server whom I did all of the setup/configuration with, ...
1
vote
2answers
31 views

SQL Server Logon Auditing and Reporting the data from a DB Table

Is it possible to configure a SQL Server database to write audit information for logons (success and failure) to a database table that can be queried? The requirement is to be able to generate a ...
1
vote
1answer
92 views

Trouble Connecting to SSIS in SQL Server 2017 (14.0.1)

Setting up a new server with SQL Server 2017 (14.0.1000) and I just installed SSIS (14.0). As of right now I am unable to connect to SSIS from SSMS and receiving the following error message: ...
0
votes
1answer
46 views

Piecemeal restore only one filegroup without primary filegroup restore

I have a db in SIMPLE recovery model, and regularly take FULL,DIFF backups. This db also have filegroups for each month. Each filegroup has exactly one NDF file. Like this: FileGroup: PRIMARY File: ...
0
votes
1answer
138 views

Error installing SQL Server Database Engine Services Instance Features - The User Log directory in the registry is not valid

We have a SQL Server: Microsoft SQL Server 2017 (RTM-CU14-GDR) (KB4494352) - 14.0.3103.1 (X64) Mar 22 2019 22:33:11 Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build ...
9
votes
1answer
169 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 (...
1
vote
1answer
36 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 ...
1
vote
2answers
40 views

Copy database removed source - Cannot acces mdf file

I attempted to copy a database, using Microsoft SQL Server Management Studio (SSMS), but right-clicking and following the wizard. I wanted to target server to be the same as the source, so I added "...
0
votes
0answers
26 views

Login failed for user Domain\MachineName$ local Machine

We have some problems with 2017's sql servers. There are lots of errors with text like: Login failed for user Domain\MachineName$ Reason: Could not find a login matching the name provided. [...
0
votes
0answers
18 views

Reorganizing columnstore index on uncomitted rows

I want to add a non-clustered columnstore index to the table table_with_csi. The following code runs every day and inserts big amounts of data into the table. Where should I add the reorganize ...
1
vote
0answers
37 views

Does SQL Server 2017 Express support Linked Server connections to Oracle DB

Is there any restrictions with "Linked Servers" with SQL Server 2017 Express? Vs using SQL Server 2017 Standard. Only in regards to "Linked Servers".
0
votes
1answer
50 views

How to upgrade SQL Server 2016 and 2017 Standard to 2017 Enterprise edition

We have one SQL Server 2016 Standard instance, and two SQL Server 2017 Standard instances installed on the same server. Now my management wants to install SQL Server 2017 Enterprise edition and ...
2
votes
1answer
58 views

Log Autogrowths bigger than log size, but no shrinks to explain how

I have a database with a logfile size of 1GB. I implemented some logging of AutoGrowths to eventually setup some notifications for when a database autogrows. What I'm experiencing though, is that ...
0
votes
0answers
34 views

SSIS OData Package Fails on SQL Server 2017

Awhile back, I created several SSIS packages, in SSDT 2015, that were deployed to a SQL Server 2016 SSIS instance. They were scheduled with agent jobs and run using basic auth. Currently, in both ...
2
votes
0answers
40 views

Generate scripts wizard hangs when loading database information

I am trying to script out a database from an instance of SQL Server 2017, so I can load it into an instance of SQL Server 2014. I right-clicked on the database, chose Tasks -> Generate Scripts, and ...
0
votes
2answers
36 views

Query on SQL Server Always On

DC1: (servers are in WSFC): DB_Server1 and DB_Server2 DC2: DB_Server3 DC1 and DC2 servers are located in different geo locations. Is it possible to configure SQL 2017 Always-On/SQL 2017 Distributed ...
0
votes
1answer
26 views

Can’t Get My JOINs Right for this Query

With the SQL Fiddle at http://sqlfiddle.com/#!18/fce3e/1, I’m trying to get the following results, but can’t wrap my head around it. I don’t want to specify a specific category or personId values. ...
2
votes
2answers
78 views

How to check AUTO_CREATE_STATISTICS/AUTO_UPDATE_STATISTICS is enabled for database

I have a project database that maintain different databases from different environnement (dev, test, prod, predprod...), I need to check if AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICSis set to ...
1
vote
2answers
2k views

SQL Server works slow after enable XP_CMDShell even disable until restart service

2 days ago I created a function that needs INSERT and UPDATE, and I enabled XP_CMDShell and do it by running a script. Afterwards, SQL Server works very slow for SELECT commands. Even very simple ...
20
votes
3answers
556 views

Why would a table with a Clustered Columnstore Index have many open rowgroups?

I was experiencing some performance issues with a query yesterday and upon further investigation, I noticed what I believe is odd behavior with a clustered columnstore index that I'm trying to get to ...
5
votes
5answers
504 views

Is there a way to reduce the amount of time spent on a point in time recovery?

I'm pretty sure I know the answer to this, but the limitations of a point in time recovery blows my mind. Say I have a 2TB database set to FULL recovery mode. I diligently take weekly full backups, ...
0
votes
1answer
35 views

How to write a query to identify common combinations of parameters used to call a stored proc?

recently I deployed a change into our production environment that ALTER'd an existing stored proc and essentially all the addition did was INSERT into a table I created the parameters passed in by the ...
1
vote
2answers
26 views

Failing MERGE Statement: How to monitor with XEvents and specifically get parameter values for a prepared statement?

My problem I am running SQL Server 2017 and would like to investigate an error with a MERGE statement like the following MERGE INTO table USING (SELECT ? AS Search_Col) AS SRC ON table....
3
votes
0answers
30 views

A curious discrepancy between DBCC PAGE and %%physloc%% for System Base Tables

During an attempt to dig into some odd system behavior I've come up against a scenario where I'm using the undocumented routine DBCC PAGE to find some information within a page, but I'm not getting ...
2
votes
3answers
250 views

SQL Server 2017 Stored Proc & TVF Performance Problem

I have a SQL Server 2017 Enterprise Edition instance where a stored procedure was taking approx. five minutes to execute. After reviewing the stored procedure code, I could see that there was an ...
2
votes
1answer
36 views

Using PHP form to insert geometry from WKT and SRID into SQL Server 2017

I have a PHP 7.3 form that asks the user for a Name, a WKT and an SRID. I would like to upload the WKT and SRID to SQL Server 2017 so that it creates a geometric object in Upload_WKT_Test using ...
0
votes
0answers
47 views

Auto Seeding SQL Server AG Combined with Backup/Restore

Are there special conditions under which the below paragraph from MS Docs is true? I tested this process on SQL Server 2017 Enterprise Edition and ended up with databases saying not synchronized/...
11
votes
2answers
597 views

Why/when does SQL Server evaluate the probe side of an inner hash join when the build side was empty?

Setup DROP TABLE IF EXISTS #EmptyTable, #BigTable CREATE TABLE #EmptyTable(A int); CREATE TABLE #BigTable(A int); INSERT INTO #BigTable SELECT TOP 10000000 CRYPT_GEN_RANDOM(3) FROM sys....

1 2 3 4 5 13