Questions tagged [sql-server-2014]
SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.
2,680
questions
1
vote
1answer
51 views
Why sp_WhoIsActive blocking_session_id IS NULL
Currently in my database server is too much slow. Running,
EXEC sp_WhoIsActive
@find_block_leaders = 1,
@sort_order = '[blocked_session_count] DESC'
Sometimes we get below error,
Msg 530, ...
-1
votes
0answers
17 views
View history when auto update stats has run for a given table [duplicate]
Is there a query we can run to view when the stats have updated automatically (either sync or async) for given table?
3
votes
2answers
235 views
Capturing query performance over time
I am trying to trend query performance over time. I made an assumption that query store will help me to do this, but the version of SQL Server I am working with is 2014, so no query store is available....
1
vote
1answer
20 views
how to disable encryption on SQL server 2014 connection that SSL is enforced by windows
In windows 10 , Test connection with a udl file is successful. however, if "Use Encryption for data" was changed the to true. UDL Use Encryption the connection fail with error message:
"...
0
votes
0answers
61 views
Why does Index Seek Used When Order By Is Present [duplicate]
I have this query:
SELECT t.some_id AS id, t.*
FROM some_table t
WHERE (t.first_col = '123'
OR t.second_cold = '321')
AND t.third_col != 'aaa'
AND t.some_date >= :someDate
AND t.some_date <= :...
1
vote
1answer
79 views
Cannot continue the execution because the session is in the kill state under compatibility level 120
SQL Server 2014 SP3+CU4.
Database compatibility level is 120.
My SQL is like:
select top 1 id
from xxx
where col1=xxx
Executing this, SQL Server reports:
message 596, severity 21, state 1, line 6
...
0
votes
1answer
41 views
SSMS 2014 can't view Indexes on views
In SSMS 2008R2 you can navigate to a Database > Views > Find a specific view that you have an index on and unfurl the Index(es)
In SSMS 2014 (possibly other modern versions) you cannot. When you ...
0
votes
0answers
26 views
Delete data from big replicated table
I have a table with more than million rows. I need to copy data to temporary table, make changes to table structure and copy back all data. This table is on transactional replication. And I wanted to ...
3
votes
1answer
61 views
Properly pivot an XML column with XQuery
I would like to pivot the attributes and values of an SQL XML column and display it in an ordered columns and rows format.
To get started, this is what the XML column looks like in SQL:
<Model ...
3
votes
1answer
44 views
Database Unallocated Space - Correct way to measure
I am confused over the web regrading few scripts on calculating the database size and free space within same.
Most of the scripts over the web uses sys.databases_files dmv to calculate free space like ...
0
votes
1answer
21 views
SSISDB - Certificates expired
I was doing an audit of certificates in our environments to find those that have either expired or are expiring in 30 days. I used the following query:
SELECT *
FROM sys.certificates
WHERE ...
0
votes
1answer
26 views
Sql failover cluster issue with different edition setup ( enterprise with standard)
I have come across one client who has setup like this where dba has installed
sql server 2014 instance with enterprise edition setup on node1 and on node2 he has added mssqlserver 2014 in failover ...
1
vote
1answer
114 views
Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation
Need urgent help on this. We have applied latest sql patches on one of the server (SQL 2014, SP3, CU4) and somehow we were not able to start the SQL due to following error.
Cannot resolve the ...
0
votes
0answers
56 views
Help on understanding deadlock
A deadlock issue is encountered when adding column to an existing table, from my attempt to understand the graph, a linked server calling sp_columns_100_rowset is having an update lock on sysschobjs, ...
0
votes
1answer
13 views
Why does SQLPackage include my table-valued function in deployments when it hasn't changed?
I'm using SQLPackage in TFS to automate an SSDT project/DACPAC deployment with SQL Server 2014. I have a table-valued function that appears in the deployment report and script with every deployment, ...
2
votes
1answer
60 views
Adding AD Security group to a SQL linked server
I have a question about adding permissions for a user group on a linked server in SQL Server. I am not a security specialist, so I appeal to everyone.
Here is the topo:
I have private software that I'...
0
votes
0answers
20 views
How will SQL Server distribute filegroup writes in this scenario?
Have a multi TB db with one large data file for the single file group on a SAN drive. For write performance reasons, it should have multiple data files. When adding a new file to this filegroup, ...
2
votes
1answer
138 views
Seamless SQLServer Database Name Change
I need to change the name for a few databases, however the databases are used by a lot of applications managed by several different teams. It would take some time for all the applications to finish ...
0
votes
1answer
40 views
Finding Culprit Blocking Sql
Whenever in our system there is a block (everything stopped). I see the below query is the culprit,
(@P1 numeric(10))SELECT "Col1128","Tbl1029"."A_NAME" "Col1124&...
1
vote
1answer
28 views
Select distance between cities in two directions
I have a distance table and a cities table.
I have joined them in a way where i now have: cityA_id,city_A,cityB_id,cityB, distance in km
i need to get the distance betweens cityA and CITY B and
then ...
4
votes
3answers
506 views
MS SQL Server 2014: which stored procedure is my error in?
As a general question or three on Microsoft SQL Server 2014, that perhaps I should know the answer to already, or even perhaps did once -
When my stored procedures run other stored procedures, how can ...
-1
votes
1answer
30 views
How to view size of clustered index for a given table? [duplicate]
Inherited the following audit table:
Table UserActions
AppID int
ActionTime datetime
Action varchar(25)
UserID int
Description text
Clustering key is on AppID, ActionTime, Action columns.
RowCount = ...
2
votes
3answers
131 views
Urgent help with TempDB log file growth mystery
I need some urgent guidance for a tempdb issue for past last week:
The tempDB log file is growing starting a specific time, lets say 2 PM. And almost continue to grow upto 150 GB and then comes down ...
0
votes
0answers
21 views
How To Grant A Specific Login Under Acitve Directory Group To View Server State In SQL Server [duplicate]
I want to grant only a login who is under an active directory user group to view server state in sql server. How can i do it?
1
vote
1answer
27 views
How to BCP export three related tables in a transaction consistent manner?
Have three related tables that I would like to bcp export to file(s). This three tables have relationships to each other. Is there a good way to bcp export these tables in a transactionally ...
0
votes
1answer
47 views
Too many Partitions, removing older years
This is a follow-on to this question a few years ago Splitting Existing Partitions in SQL 2014 where now there are partition years for 2011 all the way to 2021.
I need to archive partitions 2011 to ...
0
votes
1answer
24 views
Properly delete data forever and free up the space it was using? [duplicate]
I've been having issues finding a real answer to this question as the response is always "Don't do this, it's a bad idea, that space was obviously needed and it will just get used up again in ...
0
votes
0answers
23 views
Reporting & Logging Failing on SQL Server Job
System Info: SQL Server 2014 Enterprise, Windows Server 2012 R2, running on a VM
I have a Maintenance Plan set up to perform a log backup of a database every 17 minutes. The job normally runs fine ...
1
vote
1answer
67 views
Enable Toolbox window in SQL Server Management Studio
I am assuming that the Toolbox windows in SSMS is used to store code samples. So I am able to highlight code in the editor and drag it to the Toolbox window.
However, if I wanted to use the code ...
0
votes
1answer
37 views
Concurrent Update On Primary Key Column Making Deadlock
I have simple table T which have Col1, Col2,Col3 and Col4. columns. Col1 is primary key. Now I am running some concurrent code,
Parallel.For(0, list.Count, new ParallelOptions { MaxDegreeOfParallelism ...
1
vote
2answers
41 views
Unable to understand correlation between MaxWorkers created and CPU utilization
I am having a strange issue which i am unable to understand:
On my server with 64 Logical processors having maxworkers 1472, there are peak periods in day where workers created for an hour lets say ...
0
votes
2answers
42 views
How to query plan cache for all query's using a table variable?
Is there a way to query the sql plan cache for all query's using a table variable?
2
votes
1answer
46 views
Exponential log file growth on quiet database with log backups
I have a small (~3GB) database in Full recovery mode, with full backups at midnight and transaction log backups at noon.
Despite being a testing database with virtually no activity, the size of the ...
0
votes
4answers
46 views
MS SQL Express - Find when a Query was run
I have an ecommerce website in .NET and using MS SQL Server Express, Version info:
Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27
Copyright (c) Microsoft Corporation Express ...
1
vote
2answers
122 views
Ola Hallengren scripts: after a job failure, how to skip the databases that worked and backup only the remaining ones?
In our organization we have more than 150 databases in one instance and full backup should run for every day.
Our backup script should be in such a way that if backup job fails while taking 100th ...
1
vote
2answers
36 views
How to size SQL Server VM so that it is comparable to bare metal primary SQL server?
Are there any guidelines for sizing a new Windows SQL Server VM in Azure so that it matches performance of the on-prem / bare-metal primary SQL server?
1
vote
0answers
34 views
Ola IndexOptimize Deadlocking On Self But No Error Record In CommandLog Table
I have seen this a couple times. There is a job running IndexOptimize that completes successfully, but running sp_BlitzLock will show a deadlock for one specific table. The deadlock command is ...
1
vote
1answer
74 views
Executions/min and CPI ms/sec inside Activity Monitor is too high
We have executions/min is too high, around 20,000 to 30,000 for a query. Does this means the query is executing 20,000 per minute? I don't think we have that much load on server that a single query ...
0
votes
1answer
32 views
Is there a way to tell if a GUID column contains sequential or traditional / random GUID's?
Have a table that is populated by Entity Framework 3.1.1 code. The table contains a clustered index / primary key on ID which is defined as of type GUID with no default value. Is there a way in SQL ...
0
votes
2answers
32 views
How to query cached plans that contain a specific missing index?
I see a potential missing index for a table but would like to first query all cached plans that have that specific missing index (so I can later test those queries after the new index is built). How ...
0
votes
1answer
36 views
How can I measure the impact of queries with errors being rejected by SQL Server?
After configuring XE on November 23, 2020 to capture some errors I could count 2,163,665 occurrences of the same error coming from requests of a legacy app (that probably won’t be corrected so soon). ...
0
votes
1answer
40 views
Identify table usage in sql server before we stop the load
I tried browsing this across lot of links but cant get much of help so i am posting this question which sound bit similar.
We have quite some tables which are being planned from our app team that they ...
0
votes
1answer
40 views
Find if and when a Query was run?
I am trying to get to the root of a problem and need to see if and when a specific Delete Query was run on my MS SQL Server.
The command would have come from my .NET website.
My Server is Windows 2012 ...
2
votes
1answer
109 views
Allow user to GRANT certain permissions to others
I have tried googling this but may well be using the wrong terms... permissions and the like aren't my strong point.
What I want to do is first create a role - eg 'Snr_Analyst' - with certain ...
2
votes
1answer
37 views
Why do SQL Server auto-created statistics sometimes include the primary key?
I had always understood that SQL Server auto-created statistics only include one column. In other words, they are not multi-column statistics.
However, today I came across some auto-created ...
0
votes
1answer
50 views
Running select filename from dbo.sysfiles returns short foldernames with tilde
I am running the following statement in SQL Server 2014(in Windows 2012R2):
select filename from dbo.sysfiles where fileid = 1
However it returns:
c:\progra~1\micros~1\mssql1~1.sql\mssql\data\master~...
0
votes
1answer
54 views
Why is the built-in distance function so much slower than my custom one?
I'd like to find the distance between 2 sets of coordinates. First I use the built in function:
declare @lat1 real = 33.1, @lon1 real =-117.1, @lat1 real = 39.6, @lon1 real =-98.7
declare @source ...
1
vote
1answer
135 views
Configure Connection String to Use Target Machine Credentials
I am trying to develop an SSIS package that will be run on several different machines. I am stuck because I do not understand how to create a connection string that doesn't explicitly point to one ...
0
votes
1answer
60 views
Create LINKED SERVER to MYSQL using SSH
Is there any way to create a MS SQL Server Linked Server to connect to MySQL through SSH?
I managed to connect to MySQL through SSH using other database management tools like HeidiSQL or DBeaver, ...
0
votes
1answer
55 views
How to avoid implicit conversion when using XQuery value() method in TSQL?
I have the following xml that I am parsing using TSQL - XQuery value method.
<Root>
<FooAudit>
<BarIntField>..</BarIntField>
<BarDateTimeField>..</...
