Questions tagged [sql-server-2014]

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

398 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
10
votes
4answers
1k views

Foreign Key with a Constant

Let's say I have a table A, that has two columns: one is an ID for ThingA, and one is an ID for ThingB. The primary key is (ThingA, ThingB). Next, I have a second table, but this time it's restricted ...
9
votes
1answer
875 views

HADR high worker thread usage

Why would an availability group's number of worker threads in an HADR pool increase well beyond minimum usage of "typically, there are 3–10 shared threads" per replica? In one case we've observed ...
6
votes
0answers
98 views

Can oscillating number of fragment decrease FTS query performance?

I've got a FTS Catalog on a table containing ~106 row. It used to work like a charm, but lately, for an unknown reason it started to get very bad (query >30 sec) performances randomly. Reading ...
6
votes
0answers
261 views

SQL Server 2012+ & Service Broker: SSBDT Spam ending conversations after sending message to NULL conversation_handle

I'm mostly posting this for community awareness, but feel free to give feedback if you think there is a solution other than coding around the problem as we are doing. Crossposting this from my bug ...
6
votes
0answers
334 views

SQL Server Profiler: why the RPC Starting event does use an different decimal separator from SP Starting?

Trying to diagnose an problem, I've ran into that inconsistency. In SQL Server Profiler, the RPC Starting event uses the regional settings to write real numbers (example: in Portuguese(Brazil) 14.5 ...
6
votes
4answers
1k views

SQL Server Select Query Is Slow Even With NC-Index and Full-Text Index

I am attempting to query a maximum of 65,000 records in a database (minimum of 0). The server is using MS SQL Server 2014, and I have no way to change the hardware on the server. Schema [Id] (PK) ...
5
votes
0answers
337 views

Availability group/Failover cluster failing without signs of errors

So this is the second time it happens. Specs: SQL Server 2014 Enterprise Failover Cluster with 2 nodes No Quorum ( I Think ) Event Viwer has some events as follow: The information one always ...
5
votes
2answers
294 views

Columnstore index and low selective columns

I have a table with almost 1T rows. create table bigtable ( K1 int, K2 date, -- PK columns C1 ..., C8 ...., -- columns with various data types like float, date, varchar, ... B1 bit, B2 bit, .......
5
votes
1answer
5k views

What causes “attribute key cannot be found when processing” errors?

I'm consistently getting variations on this error when I process the cube in my first SSAS project: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: '...
4
votes
1answer
660 views

Table size increases after setting nullable column to null

I'd like to reduce database size by removing some data that is not useful anymore from a SQL Server Express database - 10 GB database size limit. So I have a table that is 5783 MB and has 4 columns ...
4
votes
1answer
72 views

How to calculate required ressources from a baseline and what should be measured at all?

I have to deal with the following situatiuon: Currently we have 1 cluster with 5 nodes running 10 instances (SQL Server 2008 Enterprise edition), and 1 cluster with 2 nodes running 5 instances (SQL ...
3
votes
0answers
95 views

Change Tracking generating enormous transaction logs

I'm troubleshooting a 24GB DB that is generating approx 2GB (compressed) of transaction logs/60min. Using the following command I'm tried to examine the contents of the log: SELECT Operation, ...
3
votes
0answers
333 views

Why the disparity between Filestream size and Container folder size?

I have an issue with one of my SQL Server 2014 databases. It appears to me that the Filestream size is significantly larger than the actual Filestream container folder size? ~360Mb larger. The ...
3
votes
2answers
842 views

Getting error running DBCC Checktable(tablename, repair_allow_data_loss)

I ran DBCC CheckDB and got some consistency errors in the database. In an attempt to recover all the data I have exported out all the data from the database to a new database except for one table. ...
3
votes
1answer
98 views

High-resolution CPU usage information for batches and statements

I'm working on a tool that I use to evaluate the performance of SQL Server queries in a development environment. I use, and highly recommend, tools like SentryOne's Plan Explorer, but my tool reports ...
3
votes
1answer
485 views

Online index operation blocking inserts

I am trying to create a non clustered index on a table which is around 500GB. The table already has a clustered index on it. Since we do not want other transaction to be blocked we tried using ONLINE=...
3
votes
1answer
178 views

sp_primary_keys_rowset is consuming a lot of CPU

I manage a database server that supports a C#/VB.NET application and we've had a lot of CPU usage problems. I am using the following dmv query from Glenn Berry at SQLSkills: SELECT TOP(50) DB_NAME(t....
3
votes
1answer
1k views

How to fix an odd LSBackup job error in Log Shipping?

Configuration: LSPrimary01 - Log Shipping Primary LSSecondary01 - Log Shipping Secondary OddServer03 - This server is not part of the LS configuration, the odd man out Sean\LSServiceAccount - The ...
3
votes
1answer
738 views

Log Shipping always fails, after a while

I have a problem with Transaction Log Shipping on the SQL Server 2014 edition. I have to log ship 4 databases, 2 of them are fine and the other 2 ALWAYS fail but not at first. When it fails, SQL ...
3
votes
1answer
589 views

Deadlock in SQL SERVER 2014

I am experiencing sporadic deadlock behaviour when my application tries to execute any DROP TABLE statement against my database. I tried capturing the deadlock graph and this is the output from ...
3
votes
0answers
78 views

Incremental statistics with failover

We have two AlwaysOn availability replicas - secondary readable. We do have an incremental statistics on a large table. When a failover happens, SQL Server tries to update statistics on that table ...
3
votes
0answers
3k views

SQL Server 2014 - Full text index never stops populating

I have a server that is running SQL Server 2014. I have full text indexes on some of my tables in my databases. The problem is that it's always saying populating and it never finishes. So my queries ...
3
votes
1answer
327 views

SQL Server 2014 Availability Group failed

I have 3 servers on VMware workstation: Win 2012 R2 as ADDC -Cluster service. Win 2012 R2 as SQL server primary replica . Win 2012 R2 as SQL server secondary replica (auto fail-over). My ...
3
votes
0answers
1k views

How to set ALL the replication to DEADLOCK_PRIORITY LOW?

I have just got this email here: Then looking at the sql server log I find this deadlock was caused by one of our procedures (that was the victim) against the replication. O.K. in this case in ...
3
votes
0answers
5k views

Why does an SSIS package fail when executed from the IS Catalog?

When executed via the SQL Server Agent under the same user context, is there any reason a package would successfully run when using the file system (i.e. DTExec.exe), but fail to execute when launched ...
3
votes
1answer
466 views

Query with conditional JOIN running slow

I have a query the first part of which looks like this - With CTE AS ( select DISTINCT A.col1, A.col2, . . . from tableA A JOIN tableB B ON ( ...
3
votes
1answer
4k views

“Access is denied.” for xp_cmdshell for network share

On a SQL 2014 SP 1 instance a SQL Agent job I'm using xp_cmdshell to return a list of files from a network share. This has recently stopped working and returns "Access is denied." The command is ...
2
votes
0answers
49 views

sys.database_files DMV, max_size column

I was just modifying my script that I use to get size of database file and wanted to use max_size to display if a max size was set for a database file. As per Microsoft Docs, the column description ...
2
votes
0answers
895 views

Execute of job failed. See the history log for details

I am learning SQL Server administration and setting up log shipping for the first time. SERVER1 is primary database and SERVER2 is secondary database. I was able to setup log shipping successfully ...
2
votes
0answers
254 views

Errors during CHECKDB

We have a db which reports errors when trying to do a backup: Starting to dig in, the first thing that was suggested was to run DBCC CHECKDB. I've been trying to run that all day now, and googling ...
2
votes
1answer
55 views

Migrating a SQL Server 2014 AG to a new 2017 AG

Our current production server runs on SQL Server 2014 AlwaysOn AG. We are planning to upgrade it to SQL Server 2017 AlwaysOn. Due to the configuration issues with the present cluster, we can not use ...
2
votes
1answer
110 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 ...
2
votes
0answers
478 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 ...
2
votes
3answers
65 views

Tracking Unintended Row Changes

I have a custom eCommerce web site, using third-party web services, that is processing dues payments for our members. Twice in the last day a specific data row for 50,000 users is having two of the ...
2
votes
1answer
90 views

Snapshot Folder Location for Replication with Clustered Instance

I am assigned to create a transactional replication with a SQL Server clustered instance for reporting purposes. I'm planning to use the Distributor and the Subscriber on the same box. Regarding the ...
2
votes
0answers
57 views

Mirrored TDE DB Performance

Performing a large index reorg on a mirrored SQL Server 2014 TDE database creates a large redo log on the mirror and the mirroring monitor displays an infinite restore time. The exact same process ...
2
votes
0answers
596 views

SQL Server: Replicated transactions are waiting for the next Log back up or for mirroring partner to catch up

I have two SQL Server DB instances on the same machine with one hosting a replica of the other. In addition, I have transaction replication setup for all tables on the main instance to the replica. I ...
2
votes
0answers
52 views

In SQL Server 2014 Is it normal for for maintenance job to pick up a table that does not exist in database

As above, we are hitting an error on the maintenance plan running on SQL Server 2014. During update statistics when we got the error. "Msg 2706, Level 16, State 6 , Line 1" Table Flow Detail does ...
2
votes
0answers
487 views

Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statemen

We have a three node AlwaysOn availability group with Node 3 being used for reporting. There is a job which runs on Node 3 and queries a bunch of tables and selects data out and it runs every three ...
2
votes
1answer
66 views

SQL DBA data type is changed after upgrade

We have recently upgraded our SQL Server Database from SQL 2005 to SQL 2014. We are experiencing slowness in both SSMS and application as well while executing a SP in SQL 2014. After checking through ...
2
votes
0answers
1k views

DBCC Refuses to Shrink a Data File - drive was full

Yes, fully aware of Paul's article, and "DON'T", et al... This is SQL 2014 Enterprise - Build 12.0.5538 - SP2 CU3 - iron - 2x8 1TB RAM - all SSD. There is an unused FILESTREAM data file: file_id ...
2
votes
1answer
25 views

How to Audit any change in Permission?

Is there any way how to know "Who did change the permission on users" Like: who give read permission or owner on a database for a user? Using SQL Server 2014
2
votes
0answers
1k views

SQL Server SPNs not being created, state: 43

I'm trying to configure a SQL Server 2014 (12.0.5000.0) instance to use a regular user account in AD and Kerberos auth. During installation the default accounts (local system ones) were used, and the ...
2
votes
0answers
123 views

SQL 2014 not recognizing Backups

I am having a problem on two different servers, both are running sql 2014. The maintenance plan does not delete the older backups. I have checked the permissions and the formatting and everything is ...
2
votes
0answers
162 views

Howto preserve statistics when switching partitions of partitioned tables

Given are two partitioned tables T1 and T2 with no automatic statistics (their statistics where updated WITH NORECOMPUTE option). T1 has only partition N populated and T2 has partition N empty. Now I ...
2
votes
0answers
65 views

What can cause a static SQL query against a static data set take vastly different amount of time to complete?

I have this very strange issue on one of the production servers - a parameterless stored procedure is either taking 1-2 minutes or 45+ minutes to finish, and it seems completely random and not ...
2
votes
0answers
133 views

sp_send_dbmail sends query results from one server but not a second server

Both servers are able to normally send email without issue server a is able to send the email for the query below, but server b has an error message saying, "mail could not be sent to the recipients ...
2
votes
0answers
262 views

What are the intended use cases for Remote Blob Storage (RBS)?

Microsoft's two main sources on RBS do not mention SharePoint explicitly: https://technet.microsoft.com/en-us/library/gg316768(v=sql.105).aspx https://docs.microsoft.com/en-us/sql/relational-...
2
votes
0answers
55 views

SQL Server Extended Events Sessions on VIEWs

I'm trying to monitor when Views are being called in SQL Server. Using Extended Events, I've created a Session and included event "sql_statement_completed". Then I've added 1x filters on this: ...
2
votes
0answers
428 views

Auto-update hierarchyid of multiple records in INSTEAD OF UPDATE trigger

I am working with an INSTEAD OF UPDATE trigger, where I have to calculate / re-calculate the hierarchyid of a row based on another column value. Thus far, I have it working. The problem that I run ...

1
2 3 4 5
8