Questions tagged [sql-server-2014]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
401 views

SQL Cannot patch the latest CU4 for SQL 2014 SP3

I cannot patch the latest CU4 for SQL 2014 SP3. Checked "view installed program", 4500181 is already installed. but when I use "Select @@version" against my SQL instance, it still shows "12.0.6259.0"...
2
votes
1answer
145 views

How to remove Alter, Drop, Create SQL from deployment script created in VS 2015 using SSDT 14.06

I need to remove the below auto-generated deployment script for my SQL project. I am using VS 2015 with SSDT 14.06. Would this be something that can be set in the Advanced Deployment settings under ...
1
vote
1answer
156 views

Why do we add NO_TRUNCATE and NORECOVERY options while taking tail log Backup?

Assume that my mdf file is deleted and I set my database into single user mode. The first thing that I do is take tail log backup. My question is do I need to add NORECOVERY option while taking tail ...
9
votes
2answers
137 views

Change referenced index for a Foreign Key

I have something like this: CREATE TABLE T1 ( Id INT ... ,Constraint [PK_T1] PRIMARY KEY CLUSTERED [Id] ) CREATE TABLE T2 ( .... ,T1_Id INT NOT NULL ,CONSTRAINT [FK_T2_T1] ...
11
votes
4answers
2k views

Alternatives to Network Backup

In our environment we have some servers that are in an Always On Availability Group, and some that are standalone. We normally backup to a network share, but we have recently observed that as the ...
5
votes
4answers
100 views

Can lowering fill factor result in more page splits? [closed]

We've been having a problem with page splits on a table that is a particular nuisance - its an audit log of activities in the database and has grown over 1TB. The main indexes are on the record type, ...
2
votes
2answers
277 views

Does Log File contain both committed and uncommitted transactions?

My question is related to SQL Server architecture. Mainly log file. In the picture below it says that when transaction is committed, log record in LOG CACHE will be moved to Log file. Does it mean ...
1
vote
1answer
208 views

Remove expired certificate SQL Server

I am trying to delete an expired certificate from my database server. The certificate was used to encrypt connections to sql server 2014 r2. We no longer have a need to encrypt connections and so we ...
0
votes
2answers
39 views

How is my software conecting on my SQL Server if it failover via availability groups?

I'm a Little confused about this topic. I know with a default instance, we can create a cluster with let's say, with the ip x.x.x.10. then automatically this IP will connect to the x.x.x.9 that is ...
2
votes
1answer
48 views

Allow two users permission to execute single agent job

I am trying to allow two users to run a single agent job. With this in mind, I don't want to add the users to the SQLAgentOperatorRole role in msdb as that will give them excess priveleges. I tried ...
3
votes
1answer
57 views

Using a second table for unique key?

This is a fairly noob question. I work with an old-school PowerBuilder software (PowerCampus) that has some strange design decisions from the 80s and 90s. What's perhaps the most important table in ...
3
votes
3answers
531 views

Do I need “New SQL Server Failover Cluster installation” to run Availability groups with manual failover?

I'm trying to set up a test environment here to learn more and apply this on our production environment. I've created and installed the Windows Cluster with CLuster manager. Now I'm going to install ...
0
votes
1answer
719 views

SQL Server - MERGE duplicate SOURCE rows validation is only evaluated on UPDATE and DELETE clauses

I want SQL Server to throw an error at me if my SOURCE table contains duplicates as per the ON clause in every case, including INSERT, but it only does for the UPDATE and DELETE cases. Why doesn't it? ...
1
vote
2answers
207 views

SQL TDE and Certificate Handling Recommendations

I've seen the information on this post: But I don't think it answers this particular question/concern: I have multiple SQL servers with multiple databases. We want to use TDE on a few of them. ...
4
votes
1answer
1k views

Patching SQL Server 2014 Versus SQL Server 2014 Express

Need to apply a Service Pack to the following SQL Express install: Installed version : 12.0.2000.8 (2014) Express Edition Not sure if I can apply the latest SQL 2014 Service Pack SP4 or stick to the ...
4
votes
1answer
41 views

Truncate Table locking sys views

We have a proc that runs Truncate Table inside an Snapshot transaction. This seems to be causing a LOCK_M_S lock that blocks the sys view sys.partitions. Is there a convenient work around for this?...
0
votes
0answers
39 views

unable to set kerberos authentication in mssql 2014 express

Hello I've recently installed MS SQL 2014 Express in MS Server 2016 Essential I wanted to use Kerberos authentication so I've run through many pages but whenever I'm trying authentication always says ...
0
votes
0answers
35 views

Deleting records does not affect unused space in data file [duplicate]

I have a SQL Server 2014 database where one table had around 485 million records. After deleting around 120 million records, the amount of free space in the data file did not change. I figured I ...
3
votes
1answer
70 views

Scan on trigger Seek on SSMS [closed]

I'm having a performance issue with a trigger when insert/delete on a table, it scan an index associated to a view, but if I execute the update manually it generate a seek. I updated statistics for ...
0
votes
1answer
46 views

adding 2nd catalog for linked server to DB2

I am unable to figure out how to add multiple catalogs for a DB2 linked server. I have an initial Linked server setup and working properly, but I am struggling to figure out how to add multiple ...
1
vote
1answer
33 views

Agent Job Running as Computer Domain Account Rather than Service account

I have two SQL Servers as follows Microsoft SQL Server 2016 (RTM-CU9-GDR) (KB4058559) - 13.0.2218.0 (X64) Dec 27 2017 17:10:01 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on ...
0
votes
1answer
64 views

Two conversions of practically the same number giving two different results

To my surprise, I run the following queries in SQL Server 2014 and each returns a different result, as shown in this db<>fiddle... This Select convert(numeric(19,2),convert(float,9....
0
votes
1answer
60 views

Implementing gMSA account for SQL FCI

I have a question on implementing gMSA account for managing authentication to SQL server. Our goal is to use gMSA account to manage the auto password change activities for SQl server service and it's ...
1
vote
1answer
65 views

remove Analysis service from a SQL 2014 cluster

Is it possible to uninstall Analysis service from a SQL 2014 cluster. I’ve seen that it cannot be done in 2008/12. I’m hoping that this maybe fixed now
1
vote
2answers
80 views

Unable to retrieve query plan of running query because of blocking

I am trying to troubleshoot a long running query in a stored procedure that around once every month randomly runs for ever and never finishes. Probably some plan caching issue. But the problem is ...
0
votes
1answer
56 views

Help needed with SQL Server query: Accessing columns not in GROUP BY clause

I have a table whose columns are as follows: CREATE TABLE Tests ( TestDate date, EntityID int) Using a stored procedure, I want to find the ‘academic year’ of pairs of tests that happened in the ...
1
vote
2answers
33 views

How to find a specific column name in table design view?

I have a table in my database in SQL server 2014 with a lot of columns, and when I want to find a column for modify properties, it is difficult to find that column, how can I find that column (ex: ...
0
votes
1answer
44 views

Confusion about HA , LOG backups and availability

So I started to work with HA and I'm on a new job, in a new company that has already HA implemented, but nobody can remember who created or if it's working. 1) Availability Groups are in sync with it'...
0
votes
1answer
144 views

SSMS 2014 does not launch Graphical Execution Plan from clicking hyper-linked query plan - ShowPlanXML

I am working with an older version of Microsoft SQL Server Management Studio 2014. I run the following query: SELECT TOP 10 Q.* FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_query_plan( qs....
0
votes
0answers
32 views

How to recover specific corrupted table from database?

DBCC Checktable and DBCC CheckDb is throwing below error: Msg 596, Level 21, State 1, Line 0 Cannot continue the execution because the session is in the kill state. Msg 0, Level 20, State 0, ...
1
vote
0answers
159 views

Win 2019 + SQL Server 2014 slowly locks up the server

We have a 2019 + 2014 sql server box that was running fine for a few days after we first stood it up on Ec2. After a week I removed a temp drive (EBS) that we had initially used to load up the ...
0
votes
0answers
47 views

SQL Server - Mirroring from 2014 to 2017 check unsent log status

I configured SQL Server Mirroring from SQL server 2014 standard to SQL Server 2017 Enterprise. Steps I followed: Create an endpoint on both servers with certificate authentication. Dump and ...
0
votes
1answer
41 views

T-SQL Query to calculate Settele and withdraw amount in MSSQL- SERVER

I have a table with below structure : FactSatnaEvent(OPRDATE date,OPRCOD int, voucherno int, SATNAPAYATYPE int, CFCIFNO int , AMOUNT int) based on this table I wrote this query SELECT ...
2
votes
1answer
114 views

What does SQL Server use to do a count(*) on a table?

Internally, how does SQL Server determine the result for a select(*)? Is it different between a heap and a clustered index? Is it able to do smart things with the clustered index to not have to load ...
4
votes
1answer
78 views

Does SQL Server Date Correlation Optimization only work on DateTime fields, or will a Date work, too?

I ask because this is an older feature that I just discovered recently. The documentation mentions DateTime a few times but doesn't explicitly say that other date types are compatible or not. I have ...
-1
votes
1answer
26 views

Query to generate each row using startdate and enddate from the history table

I have a history table like below: Custid, custname, salary, startdate, enddate 1,aaa,5000,01-01-2018,31-03-2019 1,bbb,4000,01-01-2018,31-03-2019 1,aaa,6000,01-04-2019,24-07-2019 I need to generate ...
0
votes
1answer
77 views

Is it possible for a node to host multiple cluster in relation to Always On AG

In my example, I have 3 database servers with their own DR servers, own availability group, own clusters. Is it possible to setup another server which can be part of the 3 clusters and be a secondary ...
1
vote
2answers
66 views

Setting a date dynamically

I would like to set a date dynamically in the where clause of my query but I can't figure out how to do it. The query is doing a count of the number of rows with an entered on date >= 1st August ...
1
vote
1answer
120 views

Can BCP Out cause resource contention

I had these two things running in parallel in the same database - BCP "SELECT * FROM [ServerName].[DatabaseName].[dbo].[TableName] WITH (NOLOCK)" queryout "\\LocationToMyOutputFile\TableName.csv" -c ...
1
vote
1answer
45 views

SSDT Publish failing with an error without even running any statements

My publish attempts are failing with this error Msg 205, Level 16, State 1, Line 7572 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in ...
0
votes
1answer
97 views

Login permission on Availability group database

We have an availability group database. Just wanted to check on the below statement whether it needs to be run on all replica (By failing over and making it primary)or it can be run on the primary ...
4
votes
1answer
74 views

DMVs - Relating tasks to branches

In this excellent article, Paul White demonstrates branches in parallel plans. https://sqlperformance.com/2013/10/sql-plan/parallel-plans-branches-threads At the most fine grained level, we can look ...
0
votes
1answer
64 views

How to Query Which Table Is Inserted Mostly In SQL Server?

I have a database and there is not any trigger which follows the transactions in said database. Now, I have to make a report that shows which table has mostly inserted so far. How can I do that?
0
votes
1answer
39 views

Retrieve LOGINPROPERTY information via linked server

When I run the below query on a linked server it returns nulls in the "DaysUntilExpiration" column as per resultset. SELECT '<link server>' as Instance, SL.name AS LoginName ,...
0
votes
1answer
30 views

Proc runinng slow once in a while [duplicate]

I am trying to troubleshoot the performance issue on one of the SP which executes throughout the day daily every 20 mins and on avg completes under a min. For past couple of weeks it happens once in ...
0
votes
2answers
79 views

Implications of adding a trigger on production database

I am trying to add a trigger on a production database table which includes millions of rows and is used very heavily. There are around 70k-100k inserts per day. When I try to add a trigger to this ...
0
votes
0answers
10 views

How to make replication continue to work after repairing?

I would like to share with you a problem I faced today and will be glad if you can help me to tackle that issue. The issue related to transactional replication. So, let's go step-by-step to know how ...
-3
votes
1answer
172 views

Sql Server consumes CPU but no one is using the server

In my server I have a SQL process that consumes CPU (~10%) but no one is using the server. In query select * from master.dbo.sysprocesses order by cpu desc I see at least one suspect process: spid: 9 ...
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 ...
0
votes
1answer
192 views

lost all my databases after reinstalling sql server management studio

SQL server management studio was not working so, after googling this issue, I found that I have to reinstall it. So, after re installation, I lost my database and I didn't get a backup of it. So, is ...

1 2 3
4
5
50