Questions tagged [sql-server-2014]
SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.
2,705
questions
2
votes
2answers
39 views
Are SQL Security Updates Cumulative? Are the vulnerabilities corrected with one Security Update also covered by the next Security Update?
I recently patched SQL Servers that had SP3 and CU4 with the latest available Security Update. After CU4 there are 2 Security Updates available, one from 2020/02/11 and the other one from 2021/01/12.
...
0
votes
1answer
35 views
DBCC CHECKDB job failed on SQL Server 2014
We have a DBCC CHECKDB job that runs weekly on Saturdays. This job was created thorugh Maintenance Plan wizard. Typical duration for this job is 1.5 hours. This past weekend the job failed with the ...
2
votes
1answer
62 views
How can I change the RSA key length from 1024 to 2048?
SQL Server Environment:
OS version: Windows 2012 R2
Microsoft SQL Server 2014 (SP3-CU4-GDR) (KB4583462) - 12.0.6433.1 (X64)
Instance type: Named instance
When I am attempting to connect from a Docker ...
1
vote
1answer
60 views
Database mail trying to send 2 times and then failing
Our database mail was running for 3 years without problems, but since one week, we are often (but not always) getting this error:
The mail could not be sent to the recipients because of the mail ...
1
vote
1answer
26 views
Calling triggers on replicated table when reinitalising from snapshot
I noticed my triggers weren't being fired after a subscriber was reinitialised. Is there a configuration to allow this functionality.
Currently my setting for 'Action if name is in use' is 'Delete ...
0
votes
0answers
70 views
SQL Server 2014 - Disk Space Issue
Currently we have a drive on your server that stores all user DB's data files along with one tempdb data file. (D: drive) It has 1.4 TB total space with only 98Gigs free space.
The biggest DB size is ...
2
votes
2answers
69 views
INSERT denied on tables. but login has permission for it
I'm running a procedure from database X to database Y.
On database Y there should be an insert from the user.
In this picture , the first column is the Login, the second one is the role that have all ...
5
votes
3answers
150 views
Alternate Solution For REPLACE() In ntext Column
Overview
I have a picture database that catalogue information of pictures taken with cameras. The database contains a table pictures which has 256 columns which contain information about the picture ...
0
votes
0answers
30 views
Sql Job Failing with Timeout Error
Recently I have noticed that a job is failing on our Production 2014 SQL Server with this error text:
Timeout error [258]. [SQLSTATE 42000] (Error 258) OLE DB provider "SQLNCLI11" for ...
0
votes
1answer
25 views
Error 15507 when restoring full backup
Trying to recover a full backup of a database (Version: Microsoft SQL Server 2014 (SP3-CU4-GDR) (KB4583462)) gives the following error:
Msg 15507, Level 16, State 30, Line 2
A key required by this ...
-4
votes
0answers
22 views
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000260000 i file DB_9BC044_rpdb_data.mdf
The operating system returned error:
21 (The device is not ready.) to SQL Server during a read at offset 0x00000000260000 in file 'E:\RealPrice\db\DB_9BC044_rpdb_data.mdf'. Additional messages in the ...
3
votes
4answers
379 views
How to use GROUP BY in a way concatenates data in one column, but filters for specific data in another
I am running SQL Server 2014
I have a table that looks like this:
ID | Name | AddressType | Address | Features
========================================================
1 | Bob | Home ...
2
votes
1answer
871 views
Is it ok to change drive names/labels (NOT Drive Letters!)
I realize this is likely a simplistic question but my searches only return results for Drive Letters or Database names if I include SQL Server. Please feel free to point me to existing articles or ...
-1
votes
2answers
56 views
Can still see CONSTRAINTs after running ALTER TABLE [mysch].[mytablename] NOCHECK CONSTRAINT ALL but why?
I am looking at a piece of code in a proc that seems to disable but never reenable constraints, namely
ALTER TABLE [mysch].[mytablename] NOCHECK CONSTRAINT ALL
but after the actions are taken in the ...
0
votes
0answers
15 views
Shrinking log files in SQL Server 2014
I ran sp_Blitz in our Production server over the weekend and it output a few results regarding the log file growth. Example:
The database [MYDB] has a 104 GB transaction log file, larger than the ...
1
vote
1answer
31 views
Where can I find sql server agent service logs?
I am using SQL server 2014.
I notice that - occasionally, the sql server agent service gets automatically stopped.
Where can I find logs to check the timing of when the service stop has happened and ...
0
votes
0answers
40 views
Is it safe to run DBCC CLEANTABLE on a production database?
I recently changed an application from storing images in a varbinary(max) column to cloud storage and migrated the data and removed the column. These images accounted for about 350GB of space in the ...
2
votes
1answer
78 views
LATCH_EX and CX_PACKETS for a stored proc seen when timing out
I am trying to troubleshoot an issue with below things noticed:-
There is this stored proc which gets timed out on some random days while other days or after retry may complete faster. Time out ...
2
votes
2answers
197 views
Explode a date range
I have the following table with the following data:
DECLARE @MyActions TABLE (ActionId INT NOT NULL, ActionDate DATETIME NOT NULL)
INSERT INTO @MyActions VALUES (1, '2021-08-01 01:00:00')
INSERT INTO ...
0
votes
1answer
23 views
Windows clustering in Azure VM (Windows 2012 R2)
I am going through this document to build a SQL Server Always On cluster. I read through this page to understand why we need a load balancer in Azure for the listener to work.
That brings me to this ...
0
votes
1answer
65 views
Finding Potential Primary Key In Tables
I have tables(more than 100) that are missing primary keys. Actually, these are legacy tables. Now as per some replication requirements, we need to add primary key on each of these tables. Is there ...
0
votes
1answer
72 views
Shortcut for 'Select Top 1000 Rows'
There has to be a better way!
When writing a subquery code I need all the rows listed from a table so I can manipulate what I want grouped/summed/etc. The only way I have found to do this is to find ...
-1
votes
1answer
47 views
Move logship database file
I want to move a ldf file from one drive to another. Some idiot who is definitly not me restored the log file to the wrong drive which is nearly full now.
The database is readonly/standby for a ...
0
votes
1answer
89 views
Query tables by IO stats?
Is there a MSSQLServer query I can run to show me:
Top tables by IO writes overall and for a given time window?
Top tables by IO reads overall and for a given time window?
-1
votes
1answer
33 views
What are my limits and pre req's for my Sql Server 2014 VM?
I have the following Virtual SQL Server (running in Windows Server 2016 Hypver-V environment):
The VM is on one specific host shared with other vm's and can be moved around on other hosts.
SQL Version:...
0
votes
2answers
188 views
An error occurred while updating the entries. See the inner exception for details.; [INNER] Execution Timeout Expired [closed]
One of our production SQL Servers database is performing slow. On the application side, they are experiencing this error:
An error occurred while updating the entries. See the inner exception for ...
1
vote
3answers
74 views
Search plan cache for all query plans using a given index?
Is there a query I can run to search the plan cache for all query plans using a given index?
0
votes
1answer
54 views
SQL Server data drive (.mdf files) running out of space
We have a D drive that has 999 GB of total space. Currently the production database data files are being stored there. It is running out of space.
A few weeks ago DB files were shrunk to temporarily ...
0
votes
3answers
65 views
Identify all tables with an identity column
Is there a query I can run that will show me all tables with an identity column? Trying to figure out if there are any identity based tables whose current identity is nearing upper bound of int32 or ...
0
votes
1answer
32 views
Turning off Secondary Replica in Always on availability group for maintenance
We are planning to turn off the secondary synchronous replica in our always on availability group for few hours for maintenance . Once we are done with the maintenance then we will need to failover ...
1
vote
2answers
84 views
Error when backing up from SQL Server 2014 to Azure blob storage
I'm trying to back up a SQL Server 2014 database to Azure blob storage however I'm getting the following error when trying to do so:
A nonrecoverable I/O error occurred on file
"https://...
1
vote
1answer
81 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, ...
3
votes
2answers
253 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
335 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:
"...
1
vote
1answer
226 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
50 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
31 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
87 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
56 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
40 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
27 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
487 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
61 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
23 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
143 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
144 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
43 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
32 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
537 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 ...
