Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
0
votes
2answers
34 views
SQL Alert for long running queries
I want to receive a mail when a query is running for more than x seconds. I know I can set up a job running on the database but, is there any way to do the same using SQL Alerts? What kind of alert ...
1
vote
1answer
19 views
How to prevent concurrency issues in a ssis package?
Have a SQL Server 2017 Server.
Have several SQL Server jobs that call a single SSIS package using the SYNCHRONIZED flag.
In the package there is a data flow that runs a stored proc (please see below)...
6
votes
1answer
66 views
Cannot insert duplicate key row on a non-unique index?
We've been encountering this strange error 3x the past few days after being error free for 8 weeks and I'm stumped.
This is the error message:
Executing the query "EXEC dbo.MergeTransactions" ...
2
votes
1answer
55 views
Unexplainable DBCC CHECKDB Command Output
Microsoft SQL Server 2017 (RTM-CU14-GDR) (KB4494352) - 14.0.3103.1
(X64) Mar 22 2019 22:33:11 Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows Server 2016 Datacenter ...
0
votes
2answers
80 views
Background Index Creation on SQL Server
I have no maintenance plans yet I see indexes being created in the background (low spid). I have no idea what indexes are being created or why. Using Standard SQL Server 2017 - a VM on Azure with SQL ...
13
votes
3answers
1k views
SQL Server has encountered occurences of I/O requests taking longer than 15 seconds
On Production SQL Server, we have following config:
3 Dell PowerEdge R630 servers, combined into Availability Group
All 3 are connected to single Dell SAN storage unit which is a RAID array
From ...
0
votes
1answer
38 views
Error at installing Microsoft SQL Server 2017 Database engine services and uninstalling multiple instances
I am trying to install Microsoft SQL Server 2017 with SQL Server 2017 media installation tool, but next error occurs at installation:
PerfLib 2.0 counter removal failed with exit code 2. Command ...
2
votes
1answer
45 views
The transaction log for database 'SolarWindsOrion' is full due to 'XTP_CHECKPOINT'
To start, I am a database novice. I thank you for any help in advance.
I have a solarwinds SQL windows 2017 database in full recovery mode in an availability group. The database is roughly 2GB.
...
0
votes
0answers
14 views
Can I upgrade SSIS 2008 packages directly to SSIS 2017 SSISDB?
I'm planning a mifgration of a SQL Server 2008 instance running the DB engine and SSIS.
The SSIS is deployed to the file system.
Am I able to copy those SSIS packages to a directory on my new ...
0
votes
0answers
24 views
SQL Server Resource Governor for Buffer Pool [duplicate]
We plan to have multiple databases on a Server Instance. Does Resource Governor allow buffer pool to be allocated for each database in SQL Server 2017?
3
votes
1answer
126 views
What is the proper event in Extended Events to track stored procedure executions?
My goal is to track executions of a single stored procedure using Extended Events
In the event selection list, I can find following:
sql_statement_completed
sql_batch_completed
...
1
vote
0answers
15 views
SQL Server 2017 Merge Not Running Triggers
Everywhere I am reading, when a merge statement happens, it will fire the triggers (INSERT, UPDATE, DELETE) in no particular order. I have a basic merge:
MERGE dbo.Users WITH(HOLDLOCK) as tgt
USING #...
9
votes
3answers
888 views
Why does this query, missing a FROM clause, not error out?
So we have a query with a subquery that has a typo in it. It's missing the FROM clause. But when you run it, it doesnt error out! Why!?
SELECT
1
,r.id
,'0D4133BE-C1B5-4141-AFAD-...
12
votes
3answers
464 views
Increase speed altering column on large table to NON NULL
I recently added a NULL-able bit column to a table which has close to 500 million rows. There's not a default on the column, however all inserts are specifying a value of 0 or 1, and I ran a one-time ...
1
vote
2answers
39 views
SQL Server Database Role not working
I have tried to set up a database role that the users are able to execute or view stored procedures or functions as well as SELECT, INSERT, UPDATE, DELETE data on tables via stored procs. This is what ...
1
vote
1answer
64 views
Reviewing the design of a database about animals of two distinct species
I am creating a small database using SQL Server Express 2017.
There are two animals (Foxes and Elephants).
The Foxes table will have the columns: Identifier,
Species, BodyLength, and FurColour.
The ...
2
votes
2answers
78 views
Moving all Heap Tables to another Filegroup in SQL Server
In SQL Server 2017, I want to move ALL Heaps in a DB to a secondary filegroup. What's the easiest way to do this?
This question is only for Nonclustered Indexes.
https://stackoverflow.com/questions/...
2
votes
1answer
23 views
SSAS Tabular model Date Dimension with Time possible?
I am in the process of building my first SSAS Tabular model and thought everything was going well, until trying to create a measure by DateTime.
In my warehouse, I have a DIm_Time dimension, which ...
2
votes
1answer
34 views
Call Powershell command string from Sql Query Publish profile script
Is there a way to call powershell in SSMS Sql query window? I know Command prompt can be invoked with xp_cmdshell. Does similar method for powershell exist ? (Note, I know about powershell window , ...
0
votes
1answer
34 views
Why can a select statement acquire more than 1 Sch-S lock on one table?
I have a repeating deadlock in my production environment. I cannot reproduce it in staging.
Script 1 is a scheduled data import procedure.
-- Create and populate new_TableImported
if object_id('...
1
vote
1answer
35 views
How to show the Availability group listener instead of the nodes?
I have just installed a sql server availability group, always on, with 2 servers,
SQLPROD1_TS and SQLPROD2_TS as you can see on the picture below:
select distinct rcs.database_name,
ar....
22
votes
1answer
430 views
Self referencing scalar function nesting level exceeded when adding a select
Purpose
When trying to create a test example of a self referencing function, one version fails while another one succeeds.
The only difference being an added SELECT to the function body resulting in ...
3
votes
1answer
189 views
Messages about parallel redo
Parallel redo is shutdown for database '' with worker pool size [2].
Parallel redo is started for database '' with worker pool size [2].
Starting up database ''
I see this on a client PC a lot in ...
5
votes
1answer
89 views
Window Functions: What is the purpose of Rows Unbounded Preceeding?
What is the purpose of using the Rows Unbounded Preceeding clause in a window function?
I think I understand that it's basically saying not to limit how far back to look when rolling up for the ...
0
votes
1answer
29 views
Install SQL Server 2017 over 2016 Local DB and 2012 native client
I have downloaded Microsoft SQL Sever 2017 and would like to install it. But just to be sure I checked installed applications and I found that there is:
Microsoft SQL Server 2012 native client
...
0
votes
1answer
43 views
SQL-Server schema audit trigger - possible to get old view/sp/function definition?
I'm using the below database-level trigger for schema-change-auditing.
It works fine, but if I do (drop view - create view), it only stores the sql that created the view, not the one that is dropped. ...
1
vote
0answers
27 views
Why does an UPDATE to a full-text-indexed column lock the full text index?
I'm troubleshooting a deadlock on a SQL Server full-text index. From what I can see in SentryOne's monitoring, two queries deadlocked when trying to acquire locks on the full-text index; one query was ...
2
votes
1answer
31 views
How to get the SQL-server login name of an integrated security account?
Let's say I'm a user in windows domain DC (DataCenter).
My user-name is DC\Somebody
I'm in group DC\Developers
Now SQL-Server has the login group called DC\Developers, and with that, I can login on ...
4
votes
1answer
32 views
Add replica from node 1 can't find node 2 while installing an Always ON AG lab on virtual machines
I am trying to set up an Always ON availability group lab on virtual machines, with Windows Server 2016 and MS SQL Server 2017.
While creating a new Availability Group on node 1 by the wizard, I ...
13
votes
1answer
466 views
Physical only checkdb is failing, but full one is completed successfully
I am executing checkdb with physical_only option and it fails with multiple errors like below:
Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1557580587,
index ID 1, partition ID ...
0
votes
0answers
161 views
Microsoft SSMS 2018 cannot open (only the splash screen)
yes, i've read this SQL Server Management Studio 18 won't open (only splash screen pops up) , it work the first time. after i restart my computer, and try to open smss again, the problem happen ...
6
votes
1answer
66 views
Is it safe to use Strings instead of SqlStrings for CLR function input parameters?
I have a CLR scalar UDF implemented through C# code. I've noticed that using the String data type for input parameters significantly improves performance compared to the SqlString data type. In ...
0
votes
0answers
16 views
How to fix ERROR_SCRIPTDOM_NEEDED_FOR_SQL_PROVIDER error
I'm getting below error while executing PS script
Error Code: ERROR_SCRIPTDOM_NEEDED_FOR_SQL_PROVIDER
More Information: The SQL provider cannot run because of a missing dependency. Please make sure ...
4
votes
1answer
81 views
SQL query output on one line
Sorry, I'm not a expert in scripting,
I need to create a query in MS SQL Server to get db and db log size on one row. I am using PRTG to monitor 4 SQL servers and i need to know if there's any ...
0
votes
0answers
14 views
SSIS/SSDT packages no longer showing in Visual Studio after deploying
So earlier today I had deployed my project including SSIS packages to my SQL Server. They successfully showed up ( but were giving me a duplicate key error). So I decided to save the project and end ...
4
votes
1answer
50 views
sp_BlitzIndex “Missing Index” section has columns but no values/results
when I run sp_BlitzIndex @Version = '7.5', @VersionDate = '20190427', on my Microsoft SQL Server 2017 v14.0.3048.4, I have some tables where the missing indexes reports one column Finding with the ...
0
votes
1answer
27 views
when is it necessary to create aliases in the SQL Configuration Manager?
I am having a problem with connectivity in one of my transactional replications and when checking the situation with the SQL Server configuration manager
I see there are aliases.
I was wonder why ...
8
votes
3answers
498 views
Does SQL Server allow (make visible) DDL inside a transaction to the transaction prior to commit?
In PostgreSQL I can create a table with some test data, and then in a transaction migrate it to a new column of a different type resulting in one table-rewrite upon COMMIT,
CREATE TABLE foo ( a int );...
3
votes
0answers
102 views
Import Oracle unicode data with OLE DB Source and destination in SSIS 2015
My issue is identical to this post on mssqltips.com, however my issue is importing from Oracle, not Excel.
I get all the way to the bottom where I need to map the output columns from my data ...
0
votes
1answer
40 views
The ALTER TABLE statement conflicted with the FOREIGN KEY constrain
I had a specific relational database with different tables. what I wanted to do is actually to copy two tables from different DB located on different server to my database using export feature in ...
14
votes
5answers
300 views
What is the fastest way to convert many nullable ints 1:1 to a binary string?
Part of my workload uses a CLR function that implements the spooky hash algorithm to compare rows to see if any column values have changed. The CLR function takes a binary string as an input, so I ...
7
votes
3answers
243 views
Long Running Query on Read-Only Replica that takes moments on the Primary
I've got a 4 node AG setup as follows:
VM Hardware Configuration of all nodes:
Microsoft SQL Server 2017 Enterprise Edition (RTM-CU14) (KB4484710)
16 vCPUs
356 GB RAM (long story to this one...)
max ...
1
vote
0answers
53 views
String or binary data would be truncated
I'm struggling with SSMS 17 and SQL Server 17
I have just added some data into a row (multiple columns). When I move away from the row in SSMS, it commits fine. If I try to change any column, I get ...
1
vote
0answers
160 views
DATABASE lacks a quorum of nodes for high availability
Just after setting up successfully alwayson - availability group,
testing the failover,
I get this error message:
TITLE: Microsoft SQL Server Management Studio
Failed to resume data movement in ...
0
votes
0answers
33 views
SQL Server query extremely slow. It fixes itself after rebooting [duplicate]
I have an SQL Server instance running on AWS RDS that is performing badly. A query that usually takes milliseconds to run, is taking minutes, causing my application to time out. This particular query ...
0
votes
0answers
67 views
Attunity Connectors for SSDT are not showing in VS 2017 (SSIS Toolbox)
I'm trying to add a connection manager using the new Attunity drivers I downloaded but I'm not seeing it as an option.
This Microsoft page does not list the latest SSDT version (15.x) as one of the ...
3
votes
2answers
68 views
SQL Server 2017 Enterprise, DB has suspect_pages, GAM and SGAM NOT ALLOCATED - am I OK?
I realize I may get some "horror!" reactions, but bear with me...
There is but one suspect_pages row, and I plugged the info into DBCC PAGE (output shown below). When I saw that both GAM and SGAM ...
3
votes
1answer
82 views
How to migrate Unicode UTF-8 CLOB data from Oracle to SQL Server 2017 UTF-?
I am currently assisting in a migration effort of this application from Oracle 12c to SQL Server 2017. Initially I just performed table inserts using openquery to Oracle. I discovered that the tables ...
0
votes
1answer
107 views
Why does SQL Server table partitioning stop working? [closed]
For a number of reasons, but mostly the sheer volume of data, we have 5 MS-SQL (2017) sharded servers to break up the overall load on any individual server. Secondly we have a real-time table that is ...
0
votes
2answers
40 views
Backup MS SQL AG on a non-production environment
We've created a non-production environment for SQL Server.
This environment uses the SQL Server Always-On Availability Groups as a HA-solution.
To work with SQL Server AG's you have to set the ...
