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

Questions tagged [sql-server-2014]

SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.

3
votes
2answers
119 views

Performance Improvement went wrong in Production, worked fine in Test

We have an application supported by vendor and there was a piece of code which was doing very heavy logical reads and time consuming inside a procedure, hence I proposed them to tweak the query a bit ...
1
vote
1answer
50 views

Assign Permissions to a SQL User

I have active directory on my company, I have some login users created at the level of the SQL Server (2014) that I need to propagate to all my databases on that server. I want to create a read only ...
2
votes
1answer
205 views

Fragmentation Level for Heaps

I am currently using scripts provided by Mr. Ola Hallengren for executing maintenance job and of-late I have been noticing that there are many tables (heaps) fragmentation level is alarmingly high and ...
0
votes
1answer
55 views

Loading Data from CSV file or Staging Table to Multiple tables - SQL Server 2014

Hi i am trying to load data from CSV file to multiple sql server tables and some of the tables having Reference to parent tables so i can get the FK column value but trying to load via INSERT INTO ...
2
votes
1answer
28 views

Implicit Conversion of VARCHAR Column to NVARCHAR does not cause expected table scan

Given the following script, I can see implicit conversion and Data type precendence have a negative impact on a query plan -- create objects CREATE DATABASE ConvertTest GO USE ConvertTest GO CREATE ...
2
votes
1answer
264 views

How to determine the cost of index creation?

I have a table that I want to create an index on to improve the performance of a couple of SELECT queries that use the table. How can I test whether the index has had any significant detrimental ...
0
votes
0answers
22 views

Find when tables were deleted historically

We've had an issue where data tables have been deleted from a client applications SQL Server Database (Dynamics Navision). Unfortunately we had an outage and the SQL Servers were affected by this, ...
1
vote
1answer
27 views

JSON_VALUE in SQL-server 2014 [closed]

I am looking for an equivalent functionalities of JSON_VALUE in SQL Server 2014. Can any of you post an alternate of the below code which is supported in SQL Server 2014 SELECT JSON_VALUE(ERROR_MSG, '...
0
votes
0answers
24 views

Slow connection to SQL Server both locally and by client

Yesterday I was experiencing extremely slow connectivity to the SQL Server with connections via management studio taking 5+ seconds where normally connecting to the server occurs within seconds. This ...
0
votes
0answers
11 views

Encrypting File Sytem for FileStream Folders _ Using existing free space for EFS Processing

Can any one help me on below case related to EFS for File Stream folders. I am with SQL Server 2014 SP2 and Availability groups on Windows Server 2012 R2 Standard. I have 800 GB of FileStream ...
7
votes
2answers
387 views

SQL Server: Covering indexes including all columns?

Our team has inherited an application and associated database. The previous developers appear to have enforced a rule where every index, on every table, has an INCLUDE clause to always add every ...
0
votes
1answer
29 views

Problem with Script to Synchronize Login on AlwaysON

I'm trying to resolve an issue. I've a two nodes with Always On enabled, I want to synchronize user over the nodes. I see that fantastic script. https://maq.guru/synchronizing-sql-server-logins-in-an-...
0
votes
0answers
19 views

Cluster Access point [closed]

Cluster newbie here. This might a very stupid question. I am trying to configure a three node cluster. Two nodes in 1 subnet other is in a different subnet. In the Access Point For Administering the ...
0
votes
1answer
27 views

Remove Windows FCI completely

When I built the cluster I forgot to uncheck the storage. It's now giving me all sorts of errors. How can completely remove the cluster so I can rebuild it? This is a 3 node cluster and I evicted two ...
6
votes
1answer
186 views

When do _WA_Sys_ statistics Get Updated?

I have some of the auto generated _WA_Sys_ statistics in my database which have not been updated in a while (by comparison to other statistics in the same table) The rule of thumb appears to be in ...
-2
votes
2answers
42 views

Collecting query performance metrics without impacting production servers [closed]

How can I capture the query or performance metrics over a period of time, say a month, so that later they can be analysed for performance? I'd like to use DMVs or extended events. I am trying to ...
0
votes
0answers
35 views

Frequent Cluster node failure

I experience this node failures frequently for specific SQL servers which are on a VMs, anytime I investigate we get the errors below in the failover cluster manager logs but are unable to identify ...
0
votes
0answers
24 views

Allowing a user to grant SELECT and VIEW DEFINITION permissions only on objects they create

How do I give access to a user so that they can grant SELECT and VIEW DEFINITION on any objects they create in a database. For example, I have a user MyUser in the roles db_datareader db_datawriter ...
0
votes
1answer
26 views

Measuring Availability Group synchronization lag - Clarifications

Can I get some help on below As I have started and learning about the Measuring of Availability Group synchronization lag between replicas inclusive of BCP , I want to understand and learn that does ...
1
vote
1answer
20 views

Query to find the subtract of each column from the following column

Hi everyone hope you are doing well I have a table with this structure Table 1: [dbo].[ValueTest] (Date , PorojectCode , Value) It has this data ('1397/04/01' , 'A' , 100) , ('1397/04/03' , ...
5
votes
2answers
229 views

Process to change collation on a database

I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment. So my research shows that in order change ...
8
votes
0answers
95 views

Incorrect process utilization from System health extended events?

I have been recently working on understanding the data or metrics being stored in system health extended events event file. We are trying to implement the data collection of performance metrics using ...
5
votes
1answer
153 views

Strange blocking on readable secondary after reboot

We are running an SQL Server 2014 Availability Group with 3 replicas, one synchronous (SQL2 for this matter) and one asynchronous secondary replica. We also configured read-only routing to the ...
2
votes
1answer
37 views

Excessive memory grants from indexed foreign key cascade delete

Each day, stores can enter sales information into our OLTP application. The app calls a stored procedure in SQL to save this information. Based on the users's activity, the application sends a code ...
0
votes
1answer
25 views

File growth and location change for system health extended events

I am continuing the questions over system health extended event from my previous question How are the system health extended events files rolling over? I see system health file location by default ...
1
vote
2answers
31 views

Stored Procedure on a read-only AlwaysOn Availability Group replica

We have a read-only reporting server that is part of the AlwaysOn availability group. Our developer wants to create some stored procedures. My question is where should the stored procedure be ...
1
vote
1answer
34 views

SQL Server TDE - Database backups and post restore behaviour

Below is my SQL Server details: SQL Server 2014 Enterprise SP2 GDR with Availability Group configured. List item NOTE: The Databases are FileStream Enabled. Below are the series of actions I did ...
13
votes
1answer
306 views

SET NOCOUNT Error in handling SQL call after upgrade

We are upgrading our test environment with a new server and updated version of Microsoft SQL Server and have run into an issue. On the new server, our old code will get "operation is not allowed ...
2
votes
1answer
30 views

Unable to search between IPv6 range when converting string IP Address to VARBINARY(16)

I am attempting to search for IPGeolocation data, which is presented by the data vendor as a start and end range for both IPv4 and IPv6. However, when I convert the IP that is provided, I am getting ...
1
vote
1answer
38 views

Set up new publication on SQL Server 2014 database which is part of an availability group

I like to setup a publication (transactional replication) on a SQL Server 2014 database which is part of an availability group. We have a primary and one secondary (non-readable) replica. On the ...
5
votes
1answer
111 views

How are the system health extended events files rolling over?

I see the default system health session events within SQL Server are 5 MB each with 4 files in total. We are trying to fetch data from the system health in historical events but for some critical ...
0
votes
0answers
19 views

Multiple Ad-Hoc Statements taking longer than previous days [duplicate]

We have a Data Warehouse product which runs a number of ad-hoc (IE SQL Text, not stored procs or dynamic SQL) every night. The build took significantly longer last night than previous nights and ...
2
votes
1answer
41 views

SQL Server 2014 AOAG + Replication?

I have an existing SQL Server (2014) setup using Always On Availability Groups. All the documentation says I should not try to install replication (aka distributor role) on any of the servers ...
0
votes
0answers
30 views

CheckDB Failure on most databases in Dev instance

I have a maintenance plan on one of our infrequently used dev boxes which keeps failing on CheckDB on all user databases and most system databases. When trying to run CheckDB manually, I get the ...
3
votes
2answers
98 views

Why is this column order in my non-clustered index better for my query?

I am working on a query for a table that contains movie tickets. The database holds 380k rows. A rows represents a showing of the movie (which cinema, when, how many tickets and at what price among ...
3
votes
2answers
106 views

SQL Server CPU Issues

We've inherited some SQL server 2014 databases, they are running on Azure IaaS. We're getting complaints about performance and trying to get a grip on it. We're starting to look at CPU performance, ...
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 ...
2
votes
1answer
102 views

How to clear ad hoc queries from plan cache?

As the title implies, I'm going to remove just ad hoc queries (not prepared queries) from plan cache in sql server 2014/2016 because it occupies more than 50% percent of my main memory. Do you have ...
-2
votes
1answer
45 views

SQL Server Database flatten records

Sorry for the confusion. I have below attached table sql server database table holding continuous and non continuous records. When i say continuous that means EP of first record if Beginning Point(BP)....
0
votes
2answers
23 views

Three tables with some matching data and some not - want to data from all three

I have three tables Costs (47 rows) Budgets (60 rows) Committed Costs (12 rows) All three have the same 2 fields and than a number JobNumber, CostCode, (either costs, budget, commitedcosts) They ...
1
vote
1answer
44 views

DBCC check on Always On Secondary Replica

What is the best way to schedule DBCC check on all the nodes in Always On availability group. Can we just schedule the same job on all the nodes at the same time including all the secondaries. In my ...
5
votes
1answer
305 views

SQL Server Service does not start automatically after system restart

I have a SQL Server system that reboots weekly (after installing Windows Updates). The last three weeks I have seen that the SQL Server service does not start when the server comes back up (...
1
vote
1answer
68 views

Simple SELECT taking too long to run

I am running this simple query: SELECT * FROM ABC_TABLE WHERE DAY_DATE >='1-JAN-2018' AND DAY_DATE<='31-DEC-18' There are 64 columns and we need all of them. There are a total of 2,066,381 ...
0
votes
1answer
42 views

How to collapse into a single row from multiple rows

Please refer to the attached screenshot for the requirement. I have to collapse rows into a single row with latest test_date in one column and second_latest_date in an other column and latest ...
3
votes
1answer
38 views

Select/Join multiple row values from Column A if dates in Column B are within 'x' seconds of each other

I have the following Database structure and need a query to work in SQL Server 2008 and also SQL Server 2014. Database: ASSETS Table: COMPUTERS PK (GUID) (nvarchar) (nvarchar) | ...
0
votes
1answer
37 views

How To get value of column when it exist in content value of column by sql server 2016

I want To get value of column when it exist in content value of column by sql server 2016 For Example: Table1: Title1 ---- Jack Benz Volvoo Table2: Description Names ---- ...
1
vote
1answer
36 views

Listener IP not changing after failover

We have a multi-subnet AlwaysOn availability group. There are 2 subnets and those are listed on the cluster and also the listener. Whenever I fail over and ping by the listener name it still shows ...
1
vote
1answer
56 views

Clarifications on Transparent Data Encryption

This is the first time we are applying TDE on production databases that are critical and I have the questions below. While TDE is in progress: Can we still access the application that is working on ...
0
votes
3answers
101 views

Creating Windows login/user for Mixed Always on Environment+ Instance without Always ON

How can i use by making changes to below script to run in the environments with Always ON enabled/and not enbaled. Below seems to be working after some really helpful suggestions from Kin. However ...
22
votes
1answer
1k views

Why Do My Nonclustered Indexes Use More Space When I Delete Rows?

I have a large table with 7.5 billion rows and 5 indexes. When I delete roughly 10 million rows, I notice that the nonclustered indexes seem to increase the number of pages they're stored on. I ...