Questions tagged [sql-server-2014]
SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.
2,763
questions
0
votes
1
answer
28
views
Sessions killed when AG group connection terminated
We had a strange incident last night on our SQL Server 2014 AG group. We have a two-node group setup in asynchronous mode. The clock on the secondary was off by about 30 seconds and adjusted itself ...
- 105
1
vote
1
answer
81
views
Users with role still able to INSERT despite not having a right to do so
I have a following requirement:
There is a AAD group called testgroup. Members of this group should have write access to table dbo2.Sales and read access to every table in dbo schema, and they shouldn'...
- 111
0
votes
0
answers
47
views
Renaming a Database in an Availability group without resynchronizing the secondary nodes again
Let's say we have a three-node Availability Group (AG) with nodes Node1, Node2, and Node3. We have a requirement to rename a database within the AG, but unfortunately, the database is 20TB in size. ...
- 1,760
0
votes
2
answers
51
views
Is FILEPROPERTY only used for database_files dmv?
I'm trying to mix some queries I created in the past, and some that I stole over the network to give me mount points size and free space together with database size and free space of the files.
Only ...
- 7,436
0
votes
0
answers
35
views
Whats needed to be changed if I'm moving SSRS DBs from a prod env to a test one?
I’m a bit confused on how I move the reporting database (and the temp one) from prod to test.
I know after moving DBs I need to export its encryption key, but I would like to not cause any report to ...
- 7,436
2
votes
1
answer
99
views
SQL Express CPU limitation ignored
I have a SQL Express 2014 running on a VM with 12 cores
SQL Express limitations state that it should be limited to either 4 cores or 1 socket, but when I run this query:
select count(1) "Nb ...
- 3,198
1
vote
1
answer
136
views
SP getting recompiled with "Statistics changed" reason even after disabling auto_stats for all tables involved
I was troubleshooting a performance issue and I need an SP to stop getting recompiled because of auto update stats.
I don't want to disable Auto stats update on the entire database so I thought I ...
- 1,760
0
votes
1
answer
65
views
SQL Server Failover Cluster Data Loss on failover
Currently running several databases on an Active/Passive SQL Server 2014 Failover Cluster on Windows Server 2012 R2. Recently there was an event that caused the active server to failover. The failover ...
0
votes
1
answer
50
views
Working with window patching for many SQL server with AOAG
We have hundreds of servers and 99% of them have AOAG strategy. Patching takes HOURS since we need to patch secondaries (Windows patch) , wait to complete, manually fail over to the other node, Patch ...
- 7,436
0
votes
0
answers
45
views
Always on availability group removing nodes
We have a SQL 2014 enterprise with 4 node availability group database with 2 Synchronous commit , 1 read only Asynchronous for reporting and 1 DR asynchronous.
Due to space shortage we need to remove ...
- 1,063
0
votes
1
answer
68
views
Does it make sense to schedule agent to create multiple #temp tables in parallel?
So I have this idea of scheduling sql server agent to create some #temptables in parallel before mass updating my large table (roughly 30Gb) to save some time. Currently, it took about 2 hours for the ...
- 13
0
votes
3
answers
93
views
Active-Active Availability Group configuration
Lets say we have two node AlwaysON availability group setup and each node has an availability group as primary.
EG -
Cluster Nodes - Node1, Node2
Availability Group AG1 is Primary on Node 1 and ...
- 1,760
0
votes
1
answer
105
views
Dropping Always On Availability Group Owners
I really tried to find info on this but all I could find was this doc: Who owns your availability groups?
We have many Always On Availability Groups. All of them are owned by a past user not in a ...
- 7,436
0
votes
0
answers
16
views
Initialized Replication Subscriber from Backup with @allow_anonymous,@immediate_sync off
I have initialized Replication Subscriber from Backup with @allow_anonymous,@immediate_sync = flase
It is working good.
Do I need t o change the setting back to true or just leave with false.
- 71
0
votes
3
answers
101
views
I could not add sp_BlitzIndex store procedure to SQL Server 2014 version
I cannot successfully add the sp_BlitzIndex store procedure for Microsoft SQL Server 2014 (SP3-CU3-GDR) and earlier versions. I think there is something related to the version. Because I was able to ...
- 55
0
votes
1
answer
70
views
Ideal VLF size - SQL Server transaction log
Is there an ideal size for the VLFs in a transaction log. This is a system with AlwaysON and Transactional replication. I was wondering if there is any cons to creating large VLF's. Eg, lets say ...
- 1,760
4
votes
1
answer
268
views
Unique index on computed column when contributing columns are already unique
I have a table like this:
CREATE TABLE
dbo.DiscountBarcodeList
(
ID int NOT NULL IDENTITY
CONSTRAINT PK_DiscountBarcodeList
PRIMARY KEY CLUSTERED
, Discount int NOT NULL
CONSTRAINT ...
- 23.1k
0
votes
1
answer
43
views
How to track whats causing DTC to keep closing the connection during a job execution?
We have a job with a few steps, and the last step is failing daily.
The step is a bunch of begin transaction, inserts and etc across a linked server.
I'm failing to track "what" is actually ...
- 7,436
0
votes
0
answers
27
views
Difficulty Shrinking Large database files [duplicate]
My company's primary client database (SQL SERVER 2014) is over 8 TB in size. As part of our latest project we have moved roughly one third of the data tables to separate AG databases. We are now ...
- 2,109
0
votes
1
answer
112
views
CDC cleanup doesn't work even when using sp_cdc_cleanup_change_table which completes successfully
I'm running the query below to find CDC tables with data beyond the configured retention depth:
SELECT DB_ID() AS database_id, ct.capture_instance, object_id, source_object_id
, rcounts....
- 473
1
vote
1
answer
278
views
Need help with conditional updating of a large table (50 milions) row
My procedure is as follows:
set [tb1].[SLD_VAR] = case when [CalendarDate] = [tb1].[FileDate] and ([tb2].[SLD] - [tb2].[LAG_SLD])/([tb2].[SLD]+1) >= 0.15 then 1 else 0 end,
...
- 13
-1
votes
1
answer
98
views
What would be a good strategy for a 5tb database and index maintenance?
We use ollas index maintenance with some changes here and there done by our team.
we usually have an issue with a huge database where its Alter index locks some processes. Its normally a reorganize , ...
- 7,436
0
votes
0
answers
77
views
SQL Server 2014 Negative log. Stuck at CHECKPOINT. Unable to do anything
I have a database that is stuck at CHECKPOINT for hours and I already tried all solutions that have been suggested in this site with no avail and I'm starting to lose faith. Keep in mind that the ...
- 1
0
votes
0
answers
39
views
SQL Server 2014 extended stored procedure gets stuck after Windows Server 2012 R2 updates
After running Windows Server updates (including server restart), SQL Server experienced problems executing XP_ commands. These are stuck forever without any response. The only solution I have found so ...
- 1
0
votes
0
answers
31
views
Comparison of rolap and molap in the tubular or multidimensional environment is better?
I want to write a comparison article on ROLAP and MOLAP, and I have installed SSAS. During the installation process, there was a Tabular section and a Multidimensional section that I needed to choose ...
- 1
1
vote
1
answer
2k
views
New SQL Server 2022 slower than old 2014 server
I'm planning and testing a new SQL Server 2022 install to migrate from an older 2014 database. I've noticed during performance testing some major discrepancies with some bulky queries.
Here is a query ...
- 143
1
vote
1
answer
61
views
Transactional Replication row data inconsistency
I am seeing data inconsistency in row count Transactional Replication. Subscriber is having 20 rows extra. The agent profile is used continue on data consistency error. The replication is working fine....
- 71
0
votes
0
answers
57
views
Reinitialize Transactional replication which is 5 months behind
I have given new server recently and found one of of the publication - subscription is out of sync and Distributer agent stopped as well. After checking with the team, looks like they wanted this and ...
- 71
0
votes
0
answers
92
views
Move Replication snapshot folder and Distribution database from C to another drive
I am planning to move Replication snapshot folder and Distribution database from C to another drive. Following is what I read blog post.
My setup - Both Distributor and publisher are in same server.
...
- 71
0
votes
0
answers
24
views
Transactional replication initialize from database snapshot spare file?
I would like to know, is it possible to initialize transactional replication from database snapshot spare file for existing replication which is inactive and expired default more than 72 hours.
I have ...
- 71
0
votes
1
answer
127
views
Joining SQL 2016 to a one-node cluster that has SQL 2014 installed
I have currently 1-node (it's a lab) Windows 2012R2 cluster with SQL server 2014 installed (patched to the last patch based on sqlserverupdates.com). The goal is to upgrade the cluster
to something ...
- 360
0
votes
1
answer
84
views
Why do I need to create endpoints after configuring a aoag?
So recently on new servers we are building, after creating the aoag and add servers, they don't go in sync, and I need to manually run this:
CREATE ENDPOINT Hadr_endpoint STATE=STARTED
AS TCP(...
- 7,436
0
votes
0
answers
69
views
Performance issue on same identical database with same execution plan
Need some help advise on one of the issues that we have bee working on.
For one of our SQL databases (SQL2014) we use database mirroring ( i know its old but we are working on migration) and there is ...
- 794
1
vote
0
answers
67
views
SQL Server unexplained network traffic gaps in traced events
This appears to have started happening a week, maybe two weeks ago.
Essentially, we cannot figure out whether it is the server or the client that is periodically pausing between SQL statements. In the ...
- 4,330
0
votes
1
answer
573
views
No catalog entry found for partition ID in database 7
I would like some help to fix the database problem.
I went to restore a database and I couldn't, so I executed DBCC CHECKDB (N'DATABASE_NAME') and the following result is displayed:
CHECKDB found 0 ...
0
votes
0
answers
30
views
Session locking nonstop other sessions
so Suddenly we have a NULL session locking everything else.
the rows goes on if the server are left for some minutes running.
DBCC INPUTBUFFER returns "No Event".
sp_who2 says background,...
- 7,436
2
votes
1
answer
671
views
Cursor loop all users all databases
so I'm messing around tyring to code a cursor to loop through all users and drop them for all databases.
this is the code that obviously doesn't work, because i'm cracking my head on the logic to grab ...
- 7,436
2
votes
1
answer
416
views
Adding a leading zero to single digits in a comma separated string of numbers
I have a table with two columns. ID and VALUE. The VALUE column contains a combination of a string of numbers from 1 to 45. EG :-
ID
VALUE
T123
1,6,7,9,17,22,43
T456
2,3,5,7,8,13,28,32,41
T789
...
- 25
0
votes
1
answer
81
views
How to Schedule Run query hourly
I have MSSQL Server Express 2014 Edition. Is it possible to schedule a query to be run by hourly?
As I want it to clean up the audit trail records.
Please kindly assist me.
- 1
3
votes
2
answers
468
views
Merge join asks for seemingly unnecessary sort before joining
I use one table to update another table. Both the tables have a clustered index where the first 4 columns are the same type of data.
CREATE UNIQUE CLUSTERED INDEX [...
- 65
0
votes
0
answers
259
views
SQL Database not working after OS upgrade
Let me begin by saying I’m a noob with anything SQL.
So I was tasked to upgrade the OS on a VM running Server 2012 R2 with SQL Server 2014 Services running on it. Mainly, there is an internal website ...
- 1
3
votes
1
answer
235
views
Trace database query
I need an advice.
I would need to trace one particular procedure in SQL Server. It happens to me that one procedure ends up with a timeout and I need to find out which select, insert or update this ...
- 35
1
vote
0
answers
200
views
Linked server query occasionally returns results slowly
I have an unfortunate situation where I need to pull a next-number from a table from both a local and a linked server. To do so, I've created a stored procedure to return the result. When I execute ...
- 331
1
vote
0
answers
230
views
SSRS 2014 Report parts and Subreports redirects to http: instead of https:
I am looking to find a solution for the SSRS 2014 server redirects to http url instead of https for all the subreports and other report parts like subscription , security , datasources etc .
What I ...
- 11
2
votes
2
answers
1k
views
Security Considerations/Risks/Best Practices for Enabling CLR Integration (Assemblies) for SSIS Catalog
The major pre-requisite in utilizing SSIS Catalog is enabling CLR Integration. The instance in question is running SQL Server 2014. I'm looking for feedback and resources I can mine which specifically ...
- 275
10
votes
1
answer
1k
views
SELECT Statement with , * in column list is faster than same statement without *
Situation
When querying a database with a SELECT statement with a defined set of columns, the results are received in around 21 seconds.
If there is an additional asterisk (, *) at the end of the list ...
- 18.1k
0
votes
1
answer
120
views
Are SQL Server 2014 Security Updates Cumulative
I know this question may have already been partially answered. My questions are as follows.
Are SQL Server Security Updates cumulative ?
On https://sqlserverupdates.com/sql-server-2014-updates/ ...
- 47
0
votes
1
answer
264
views
How does SQL Server know not to execute Anchor element in Recursion CTE for 2nd Iteration / 1st Recursion?
I've been reading up on the inner workings of CTE Recursion and I'm still not quite sure how SQL Server is implementing the Anchor element in their source code and how SQL Server Query Execution ...
- 275
3
votes
1
answer
131
views
Improving query response speed when joining a non-spatial table with a spatial table on a non-spatial column
I have a table of frequently updating events that affect certain geometries. The geometry areas are fixed, but there may be an event affecting multiple areas at once, so there's a many-to-many ...
- 33
4
votes
2
answers
2k
views
Is SQL Server able to use internal parallelism for an update statement?
I am struggling to find the best way to migrate some "varchar" columns to "nvarchar". One of the options i am using is to add new nvarchar column(s) then update the values from the ...
