Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
631
questions
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....
