Stack Exchange Network

Stack Exchange network consists of 174 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

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

5
votes
0answers
61 views

TokenAndPermUserStore Clear decreases CPU usage for a short period of time

Intro In short, there are a lot of ad hoc queries occuring on my server, from an application which I do not control and cannot change (Even pushing indexes is hard, and they use many heaps... ). ...
0
votes
0answers
38 views

Access Database Size

I have an Access database, which is 1.9 GB. I linked that Access database to SQL Server. So far my understanding data flow to SQL server tables if any changes occur in Access database. How about the ...
1
vote
0answers
38 views

Memory distribution across numa nodes within Sql server

I need to understand following behavior of memory distribution across numa nodes within sql server Current set up: 2 node cluster SQL server 2014 SP2 with virtualization disabled We have 4 NUMA ...
1
vote
1answer
47 views

Strangely high writes per second

I'm experiencing some extremely strange behavior on one of our OLTP Servers. Just to provide a bit of background, we have an 'OLTP' which has many large and wide tables. We have 560 tables within it ...
2
votes
1answer
62 views

CPU usage among Numa nodes in sql server

I need to understand the below scenario On Sql server 2014 with compatibility mode of 2008R2, having 80 logical processors with 20 distributed amongst 4 numa nodes. I. E numa node 1 has 0-19 , numa ...
1
vote
0answers
26 views

UPSIZING ACCESS 2010 to SQL Server 2014

When I run the upsizing module it goes through the motions but the report at the end says "table was skipped or export failed" for each of the tables. I can't find a solution for this. I ...
1
vote
1answer
23 views

Sql Server : How to query list of subscriptions and Articles (for a given publication) from Publisher

In Sql Server 2014, how to query a list of Subscriptions & its articles for a given Publication from Publisher? (transactional replication) Yes, there are easier ways to query from Distributor ...
1
vote
1answer
199 views

“The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.” after adding indexes to temp tables

Recently, I added some indexes on temp tables to procedures which prepare data for data warehouse. On a testing db, tests of these procedures worked fine but when running on client's server it failed ...
0
votes
1answer
17 views

Monitor volume of data returned to the client application

I've identified ASYNC_NETWORK_IO as our top wait by some distance averaging between 70-77% of wait time for our client application SQL Server. This has been put forward towards the development team ...
0
votes
0answers
22 views

Error: 823, Severity: 24 --> error 21 device is not ready after System Reboot. How do I prevent it?

During a scheduled reboot of the machine running my SQL Server 2014 instance tonight, several live servers became corrupted and had to be restored from backup. Checking the error log/event viewer I ...
0
votes
0answers
40 views

Restore time on dev servers

We have 4 test environments running SQL Server 2014 Developer edition. Every night, we refresh one of those environments by restoring a SQL Backup file stored on a separate server, so that it's in a ...
0
votes
1answer
32 views

SQL Server hangs on index rebuild, works well after SQL service restart

We have a SQL Server 2014 enterprise cluster. Server 1 and Server 2. SQL Server is limited to 64GB of memory. We are using Ola Hallengren scripts for index rebuilding and optimizations once per week ...
3
votes
0answers
44 views

Need help with SQL Server 2014 and disaster recovery

I am tasked with building a highly available SQL Server 2014 Enterprise environment for our new policy admin system. This database will eventually grow to 1TB +. Background Our normal method of ...
1
vote
0answers
23 views

How do I see what update statistics is doing while running?

I have a very large table with 500m rows and 150 columns (I did not design the table, im simply trying to figure out what is going on here). I try to update a single statistic for this table. I run ...
2
votes
2answers
42 views

Duplicate index - Same key, one with INCLUDE columns, which one to drop?

I have 2 indexes that have the same key, but one also has INCLUDE columns. Which one can I drop? They are the same, except IndexInclude has 6 INCLUDE columns. Both have some reads (IndexNoInclude ...
1
vote
1answer
31 views

Does number of rows affect Update Statistics performance if you are sampling with n rows?

For example, I have table A with 80 million rows and table B with 200 million rows. Assume both tables have 30+ stat tables (all manually generated by SQL Server). I randomly take the first 10 stat ...
2
votes
1answer
31 views

History table implementation: “Tuple-versioning” vs Effective Date

I'm designing a database that will have history tables (SCD Type 4 I guess) for auditing purpose. The question is what are the advantages of having tuple-versioning (start and end dates) vs just ...
-1
votes
0answers
20 views

Index Fill Factor [duplicate]

On SQL Server 2014, does fill factor 0 means the same when I set 80 ? Is it some automatic setting ? I put fill factor 80, when I did rebuild of indexes and performance went down. Now, I figured ...
0
votes
1answer
53 views

Validation of data with checksum vs Binary_checksum

Based on this post, I was able to solve the performance issue by adding indexes and thought I completed my project until I ran into an issue with the checksum... I then did research (should have done ...
2
votes
2answers
57 views

How does patching work in SQL Server?

I need a clarification on how below SQL Server service pack patching works. Few of my servers are at below patch for SQL Server 2014 which has latest meltdown patch 12.00.5214 2014.120.5214.6 ...
2
votes
1answer
33 views

Concurrent requests not running in parallel

I am trying to run update statistics in parallel. Say I start with 2 sessions: S1, S2 In S1 I run Update Statistics A with fullscan and time it to take 2 minutes. In S2 I run Update Statistics B ...
5
votes
2answers
77 views

Configure SQL Server Analysis Services (SSAS) to run on same IP as SQL Server instance

TL;DR I would like to add SQL Server 2014 Analysis Services to a specific instance of SQL Server and to configure it in such a way, that the Analysis Services (SSAS) and future Reporting Services (...
1
vote
1answer
43 views

Deadlock - Due to update causing Index page movement within an index

I am still troubleshooting deadlock mentioned in this questions. In that I got help in understanding why a select would hold on to a shared lock and I was able to test and confirm that also. Now I ...
1
vote
1answer
39 views

Avoiding code duplication for counting and selecting the same resultset

I have a requirement to return the total number of records and paged data from a stored procedure. The row count should only be computed if a boolean parameter is true. How do I perform the 2 queries (...
0
votes
1answer
36 views

How to Replace Hash Match (Partial Aggregate) with Stream Aggregate

My execution plan shows a hash match (partial aggregate) instead of a stream aggregate even though I have a non-clustered index (which basically means sorting is already in place). It gives me a ...
2
votes
0answers
27 views

Query involving TVF very slow on SQL Server 2008

I have a query that runs in under 1 second on SQL Server 2014. A similar query takes over 12 minutes when run against a table on a linked server running SQL Server 2008. I don't know if it's because ...
1
vote
2answers
42 views

Can't change authentication mode from Windows to both SQL Server and Windows

As the title suggests I'm having problems changing the authentication mode from Windows to both SQL Server and Windows mode in version 12.0.2. I have tried right right-clicking the server, go to ...
0
votes
0answers
31 views

Edition upgrade on SQL Server failover clustered instance

We did Edition upgrade on our active node for the SQL Server instance SQL01 and that was successful. I have a question/doubt --> Do I need to proceed the same on other node as well since SQL Server ...
1
vote
1answer
30 views

How can I permanently remove replication by script if I only have access to the subscriber?

We've been building a replication network of DBs to give us some degree of Always On without paying the full price tag. Currently we have a master DB which replicates to a handful of slaves, so that ...
0
votes
0answers
17 views

Trouble dealing with deadlock in a Dynamics NAV 2009 instance

I'm having a lot of deadlocks ocurring in a Dynamics NAV 2009 instance. Lots of select * ... and select top 1 * ... with (updlock) coming from the application. Is there anything I can do without ...
1
vote
0answers
29 views

Recreating Layers based on data already entered

Background: Construction events take place on a certain block of road. The events are based on data like Lane#, Layer#, and SP and EP (Start Position and End Position). I created a picture to make ...
2
votes
2answers
36 views

Database Log File Growth change does not reflect on secondary replica sys.master_files

Environment: Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) ...
0
votes
1answer
23 views

Redirecting Report Server connections using host file

I am connected remotely to a server called New_Server and trying to connect to the Report Server which is installed on it by referring to another server: I've changed the host file on NEW_SERVER to ...
0
votes
0answers
30 views

Enabling TDE on PRODUCTION Servers - Guidance , Suggestions and Testing TDE ways

First of all just want to say , I am completely new to Transparent Data Encryption. Can I get some guidance on things to keep in mind while I am enabling TDE on Critical PRODUCTION Servers. I have ...
0
votes
0answers
32 views

Access Denied issue, SQL Server LocalDB on Windows Server 2008 R2 SP1

Running SQL Server LocalDB 2012 and 2014 on Windows Server 2008 R2 SP1 VM I have some bizarre issues which don't happen when running on Windows 7 or Windows 10. I can create databases, save data to ...
-1
votes
0answers
80 views

Is it common to store compressed byte array in SQL Server?

We are not using SQL Enterprise Edition. We have to do audit trail by ourselves. I refer the sample from https://www.codeproject.com/Articles/1019511/Audit-trail-and-data-versioning-with-Csharp-and-...
2
votes
1answer
56 views

Troubleshooting Deadlock, Victim holding on to shared lock

I am trying to troubleshoot the below deadlock situation Looking at the deadlock xml, both session are using read committed isolation level. Question is, in read committed isolation level, in this ...
1
vote
1answer
69 views

Distribute multiple payments to invoice lines

I'm having a problem allocating payments to invoice lines. We’re using MsSql 2014 Data looks like this: Invoice lines table (sales): lineId invoiceId value 1 1 100 2 ...
1
vote
0answers
29 views

Version store blowing up, but responsible sessions have no open transactions

Tempdb is growing and it's all version store data. When I run: select hostname,elapsed_time_seconds,session_id, transaction_id, is_snapshot, blocked, lastwaittype, cpu, physical_io, open_tran, cmd ...
1
vote
0answers
50 views

Help with Frequent Data Updates on Clustered Columnstore index [closed]

I’ve been asked to look at a SQL Server 2014 Enterprise VM with 6 cores MAXDOP set to 3, 256 GB Ram. The CPU is running very high but there isn’t enough activity going on to justify the CPU workload....
2
votes
4answers
115 views

Best practices for transferring data across long, wide tables

As part of my data warehouse project, I'll be transferring data from our OLTPs to the Data Warehouse. Some of the tables are long and wide so obviously, I'll only be transferring required columns. ...
0
votes
0answers
32 views

Moving tempdb from shared to local drive in a failover cluster

We have an existing failover cluster that I would like to have the tempdb in local drive. I'm not sure how to proceed. My thought process is below: Create two drives on each node one for tempdb and ...
0
votes
1answer
49 views

How do I allow non sysadmin accounts the ability to create/query linked servers in SQL Sever 2014?

I want to allow a non-admin account the ability to query a linked server. If I create a linked server with the following under a sysadmin account like below. EXEC master.dbo.sp_addlinkedserver @...
1
vote
2answers
94 views

Will Re-Seeding an Identity column back to 0 cause page splits?

Simple table with an identity table as a clustered primary key. We unexpectedly hit the max value for the column, so I requested our DBA re-seed the table back to 0 to give us some time to examine ...
0
votes
0answers
48 views

Cleaning up the environment on old cluster after migrating to newer SQL Server cluster

We migrated many instances of SQL Server 2008/R2 on Windows 2008R2 and 2012 on Windows 2012 to newer Windows 2016 with SQL Server 2014 and 2016 versions. The old clusters were configured with either ...
0
votes
2answers
23 views

Transactional replication failure handling

Web Application is High school record management system (HSRMS). Users of the application are High school management (Normal Users) Data verifying users (Admin) User group 2 will allow the User ...
1
vote
1answer
33 views

Cleaning up MSrepl_errors, SQL Server Replication

Our MSrepl_errors table is around 15GB and got records from the beginning of time. What is the correct way of cleaning up this table. Do we simply delete from it? Edit The distribution agent clean ...
2
votes
0answers
46 views

DBCC TABLE CHECK command won't stop

We have a SQL Server 2014 with 140 user databases, each of which have approx. 180 tables. A maintenance plan was scheduled for once a week but it was discovered that the Check Database Integrity (DBCC ...
0
votes
0answers
34 views

Tracking parameter passed to Stored procedure - extended events

I am trying to track a slow performing SQL and I am trying to capture the parameters that are passed in for the slow runs. I am tracing module_end and rpc_completed. The stored procedure is run as ...
0
votes
1answer
36 views

Enabling TDE on SQL Server AOAG 2014 Databases - Guidance

I am completely new to Transparent Data encryption and please help me on below questions related to enabling the TDE on existing PRODUCTION Databases. Primary Question: What are the things which I ...