Questions tagged [sql-server-2014]
SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.
2,365
questions
3
votes
1answer
39 views
Seting MAXDOP for SQLserver 2016 and above
I was going through MS documentation reg: setting MAXDOP starting SQL2016 as that Automatic soft NUMA enabled by default and getting confused on how to make the changes to current SQ2014 and SQL2012 ...
0
votes
1answer
22 views
Database Architecture Identifiers
I am kind of new around DBA world, i am currently working with JMeter(front/back end performance testing) but when i have spare time (which is a lot lately..) i try to learn about DBA Development, ...
0
votes
1answer
49 views
Can SQL Server be forced to use a cached plan for executing a SP or SQL script?
Can we somehow force SQL Server to use a cached plan for executing a SP or sql query?
We have a parallel plan in prod which estimates data size in GB's and estimates number of rows to rows equal to ...
0
votes
1answer
14 views
How to solve a mail issue Failed to initialize sqlcmd library with error number -2147467259?
I got an error while trying to send an email:
Msg 22050, Level 16, State 1, Line 0
Failed to initialize sqlcmd library with error number -2147467259.
Here is my query:
DECLARE @Table TABLE (
...
0
votes
1answer
54 views
Changing Column Data Type with FKs
I want to change my column data type from "Datetime" to "Datetime2(7)". If I try to do it with the GUI SQL Server 2014 asks to save all FKs table dependencies and then I get a lock timeout because ...
0
votes
1answer
34 views
How to add multiple query results into one csv file?
By the help of the members of this community I have created a script which shows recent T-log backup for each database in the server. Instead of sp_MSforeachdb I used a customized script written by ...
2
votes
2answers
77 views
What is best way to move large number of existing indexes into new file-group - DB Size: 700GB
I have requirement to move all the indexes into different storage space (file-group) within existing SQL Server 2014 Database (Current Size: ~700GB)
I'm planning for approach where i can drop ...
0
votes
1answer
42 views
How to exclude tempdb from sp_MSforeachdb?
Below I have a code to get info about t-log backups for each database in the server. I want to exclude tempdb from this list.
exec sp_MSforeachdb 'SELECT server_name, sysdb.name AS DatabaseName, bkup....
0
votes
0answers
25 views
Collect a column as CSV in GroupBy
I have this schema:
Result
Story:
BILL_INFO has all bill headers. There is a business process called Deposit that occurs once a day: BILL_INFO are updated with a DEPOSIT_ID from DEPOSIT_INFO table ...
1
vote
0answers
16 views
Read all Captured Extended Events for a Given Extended Event Session Name
I want to be able to read all XE files for a dynamic XE session name. My XE session stores files to disk.
I have the query below
DECLARE @XeName SYSNAME = 'MyXE'
DECLARE @Path NVARCHAR(255)
SELECT @...
9
votes
2answers
569 views
Is it possible to PIVOT on a LIKE statement
Is it possible to group by elements (as in COLUMN LIKE='Value%') in a PIVOT table? I have a table [DBT].[Status] which contains various statuses (of databases, instances, etc.) and don't want to pivot/...
0
votes
1answer
60 views
Enabling write caching for TempDb data drives
Recently, we had issues with slowness for some queries where TempDb latencies on their respective drives had spikes.
We already have 8 tempdb files and trace flags 1117 and 1118 are not enabled. We ...
2
votes
1answer
109 views
Queries from different tables locking each other (LCK_M_X)
Background: Was hired to work with JMeter for performance testing but I'm also getting more and more into SQL performance (because of time, and because I like it). I still suck, and will for a long ...
1
vote
1answer
32 views
How to create a job that uses Powershell script? [duplicate]
I have a powershell script which deletes old backups from NAS storage. I have already tested it using Windows Powershell and it works but I couldn't perform the same thing in SQL Server. I have ...
0
votes
0answers
17 views
What are drawbacks of running CmdExec job step?
Is there any risk of running CmdExe job step in SQL Server? Which one is more secure, running powershell script via task manager (windows) or that of via SQL Server agent (CmdExec)?
0
votes
1answer
56 views
How to use the COUNT function to count up to the current row for each row?
I had trouble phrasing the question, but I think the following example will clear things up.
Context: Trying to create a query for Microsoft Report Builder, need to perform some logic on the data in ...
0
votes
1answer
23 views
How to grant xp_delete_file to non-sysadmin login?
I want to delete old backups from shared folder, which are located on separate NAS storage, using newly created SQL login. Is there any workaround to execute xp_delete_file using another account which ...
0
votes
0answers
22 views
Transaction Replication - re-initialization the subscription after changing an article's structure
I'm using SQL Server 2014 SP3CU2 Enterprise Edition.
I have a transaction replication on a DB with some very large tables.
I needed to change the structure of a column on one of the tables that were ...
0
votes
1answer
45 views
Pre allocate ids
I have an import to multiple tables, and would like to pre-allocate ids in the various tables, so that I can prepare all of the data without locking the tables.
Previously I have used this to ...
3
votes
2answers
41 views
How can I determine which permissions needs to be assigned to the newly created login?
We have 4 databases which contain different procedures and functions. Till now the actual application can access the database using only the sa account, now I would like to change the app to use ...
15
votes
2answers
2k views
What happens when there is no available physical memory left for SQL Server?
While googling I found some conflicting information.
Some sites state that when there is no physical memory left for data, then SQL Server moves the already existing data into TEMPDB (see: SQL Server:...
0
votes
1answer
48 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
51 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
35 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 ...
8
votes
2answers
125 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
1k 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
93 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
55 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
36 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
31 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
27 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
50 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
172 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 ...
1
vote
1answer
43 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
76 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
90 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
31 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
27 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
34 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
64 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
30 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
24 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
25 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
27 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
52 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
54 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
29 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
30 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
40 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....
