Questions tagged [sql-server-2014]
SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.
2,734
questions
0
votes
0
answers
25
views
Backup Maintenance Plan Overhead Longer than Actual Backups
I created a maintenance plan to perform transaction log backups on a single database. The plan is scheduled as a job that runs every fifteen minutes. The plan has two steps:
clean up log backup files ...
- 1,074
0
votes
0
answers
39
views
How can i store execution plans for certain stored procs
Unfortunately we cannot use query store as we are on SQL 2014 and need to find a way to store execution plans of certain stored procedures.
I saw XE like sp statement completed but unfortunately it ...
- 703
0
votes
1
answer
32
views
In Always on AG How can we setup jobs with a failover
In SQL Server 2019, We have primary and secondary replicas. if primary goes down then secondary will become the primary server, In such case how to handle the SQL jobs.
Please help me to get it ...
0
votes
0
answers
10
views
faster way to create AOAG with cloned disks
We have some projets going on to move databases from one server to another.
for example we have 3 servers.
We cloned the data/log files from the production server (AWS) and moved into the 2 other ...
- 7,100
-2
votes
1
answer
46
views
What other options do we have for Having?
I deleted my old question to be more precise on what I need.
we have a big query containing lots of smaller queries. This query runs really fine, until it reaches to the "having max" clause:
...
- 7,100
3
votes
1
answer
548
views
Is there a way to Delete inserted data, or vice versa?
I'm trying to get Data inserted and then Deleted in the same query.
I'm trying to delete it in chunks to not cause LOG issues:
DECLARE @BatchSize INT
SET @BatchSize = 100000
WHILE @BatchSize <> ...
- 7,100
1
vote
1
answer
35
views
Why automatic failover does not occur when I turn off SQL Service in SQL Server Failover Cluster Instance?
I faced unusual behavior associated with automatic failover so that automatic failover does not work in the case of turning off SQL Server service. It seems that clustered disks are still remaining ...
- 1,163
0
votes
1
answer
148
views
Free node memory and low PLE
We run a SQL Server 2014 standard edition on a 94 GB RAM server with 82 GB RAM dedicated to SQL.
I know PLE is not the answer to all and we should not focus on it too much. But if you look at the ...
- 1
-1
votes
1
answer
48
views
SQL Server 2014sp2 / SQL Server 2014 sp3 CU4 Primary Key Violation on Primary Key constraint on insert
Prod Env: SQL Server 2014 sp2
Test Env: SQL Server 2014 sp3 CU4
Prod and Test matches up. Recently updated Test Env to sp3 cu4. Since updating proc_a (within a SQL job) fails due the below error. ...
- 11
3
votes
2
answers
300
views
Is there a way to add INCLUDE for a UNIQUE NONCLUSTERED index?
we have a table with 4 columns ( example ):
create TABLE [dbo].[myTable]
(
[Id] BIGINT NOT NULL,
[Id2] SMALLINT NOT NULL,
[Id3] SMALLINT NOT NULL,
[IdUnique] ...
- 7,100
0
votes
0
answers
40
views
Very imbalanced memory usage across NUMA nodes
Our SQL Server 2014 Standard edition uses 82 / 94 GB RAM on our server and we have 2 NUMA nodes configured.
When I check the LPE for both nodes, instance 1 has an very high value, 90000+ and steadily ...
- 1
-1
votes
1
answer
29
views
How to get partnumber that have at least have one source type Website and at least one stockid equal 1 Based on PartNumber?
I work on sql server 2017 i face issue i can't get partnumber
that have at least source type website and at least have stockid equal 1 per partnumber
so with another meaning i need to get part numers ...
- 155
3
votes
2
answers
53
views
Estimating the best restore time from saved backups in SQL Server
I am looking for guidance and any advice on how to estimate the restore time for SQL Server databases once copied locally to the server.
This is just to layout a strategy what will happen if in future ...
- 703
1
vote
1
answer
26
views
Log shipping report to show average daily volume?
Is there a SQL log shipping report (or query) that I can run to see what my average daily log shipping volume is in terms of gigabytes of log data shipped (by DB or in total)?
- 922
2
votes
0
answers
70
views
Why is my SQL instance suddenly increasing i/o
I have the following query:
WITH CTE AS (
-- Acknowledged: false
SELECT TOP 5
dbo.AlertDetail.AlertDetailID,
dbo.AlertDetail.AlertDateTime,
dbo.Vehicule.Name,
...
- 263
6
votes
1
answer
260
views
Why is statistics update slower on a clustered columnstore than on a rowstore?
I've moved several large tables (each with >10^9 rows and a couple of dozens columns) from clustered rowstore to clustered columnstore indexes on a SQL Server 2014 instance and noticed that ...
0
votes
0
answers
34
views
SQL Server 2014 consuming more memory than max server memory setting
For the past month, I have noticed our SQL Production server has been consuming more memory than we allocated for it. This is the current structure:
Total OS Ram: 31Gb
SQL Server currently using ...
- 63
0
votes
2
answers
111
views
Which TLS version is database mail using?
How can we find out which TLS version our database mail is using for sending e-mails?
We have issues with database mail (mails are sometimes not sent, even after 10 repeats), and it seems that TLS 1.1 ...
- 235
-1
votes
1
answer
40
views
how to display category name for c and x from table category without using self join?
I work in sql server 2014 I need to get categories c and x without using self join.
But, I don't know how to do that.
My data sample
create table #category
(
categoryc int,
categoryx int
)
...
- 155
2
votes
1
answer
94
views
Error 9002 - State 4, Class 17: SQL Server reports full transaction log
I have the problem when running an external program that accesses a SQL Server 2014 database. I get the following error message.
The transaction log for the 'TService' database is full. The
...
- 21
0
votes
1
answer
35
views
Tracing Remote Sql Execution
Recently in our database server we have found that there are some queries executed by unknown client/user/application. We have found out the query client_name is the name of the same server (means ...
- 1
0
votes
0
answers
69
views
Maxdop setting in SQL Server 2014 during index rebuild offline and online
We are having issues performing index, table and stats maintenance in one of our databases.
There are over 200 indexes, some of them on tables having more than 1b rows.
We can't bring application down(...
0
votes
1
answer
40
views
Find what queries are reports using on SSRS
EDIT: It seems they're using "semanticQuery" and this is why I cant see usefull information?
We will be decommissioning one of our functions, and we noticed that some reports are still ...
- 7,100
0
votes
1
answer
68
views
Why is the result of DATABASEPROPERTYEX not consistent for AdventureWorks?
I'm using the function DATABASEPROPERTYEX to check the Recovery property of databases, but the result is wrong for the database AdventureWorks (later I noticed the same strange behavior for ...
- 4,384
3
votes
2
answers
323
views
Identify all queries that target a given table?
Is there a good way to identify all queries that target a given table (extended events, procedure cache, etc)?
- 922
0
votes
0
answers
34
views
How to get first row group by partid and compliance typeid and document type?
I work on SQL server 2014 I face issue I can't get only one row
based on part id and compliance type id and document type .
so if have part id as 3581935 and compliance type id 1 and document type
Web ...
- 155
0
votes
0
answers
51
views
Trace id 2 stopped because disk space, but I can't find this trace
I received the famous alert:
Trace ID '2' was stopped because of an error. Cause: 0x80070070(There is not enough space on the disk.). Restart the trace after correcting the problem.
But running both ...
- 7,100
0
votes
0
answers
52
views
SQL miss transfer some data to another table under transaction
I have query which transfer data from one database to another.
Query is controlled by Maintenance plan and it's executed each 10 sec.
SQL server isolation level is read committed.
Over query we are ...
- 1
0
votes
2
answers
64
views
Creating an if then clause in SQL
I'm fairly new to SQL and I am am used to writing formulas in Crystal Reports.
I want to recreate the following formula from Crystal Reports into an SQL statement to create a column:
if (not isnull ({...
0
votes
1
answer
37
views
Does AOAG needs a Windows failover cluster?
I'm reading some windows docs about AOAG and I got confused:
Always On availability groups, the high availability and disaster recovery solution introduced in SQL Server 2012 (11.x), requires Windows ...
- 7,100
0
votes
1
answer
79
views
SQL Server 2014 mirroring Backup
Is it necessary to stop working on the database when preparing the mirroring process?
If I take an old backup of the main database and restore it, will all the new data be transferred when running ...
Max Sh
-3
votes
3
answers
133
views
Slowly running a query that always runs fast
I have two tables that 99% of operation on it is simple select. Rarely we have insert/delete and updates.
There is a inner join on these tables and execution plan of the query is index seek. 99% of ...
- 439
0
votes
1
answer
53
views
Getting database-level audit logs without database audit specification
I have server level auditing on SQL Server 2014.
Why am I receiving database-level audit events (such as SELECT, INSERT, UPDATE, DELETE, etc.), even though there are no database audit specifications?
...
- 13
2
votes
3
answers
339
views
SQL Full DB Backup performance on different Hardware
I wrote a fairly simple script that creates in a periodic interval a backup of our DBs, switching automatically into differential in case a full backup already exists.
This runs great on my system. A ...
- 123
2
votes
2
answers
222
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
1
answer
181
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 ...
- 63
2
votes
1
answer
309
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 ...
- 12.2k
3
votes
2
answers
4k
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 ...
- 235
1
vote
1
answer
87
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 ...
- 113
0
votes
0
answers
115
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 ...
- 63
2
votes
2
answers
224
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 ...
- 7,100
6
votes
3
answers
373
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 ...
- 14.9k
0
votes
0
answers
81
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 ...
- 63
0
votes
1
answer
120
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 ...
3
votes
4
answers
410
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 ...
- 137
2
votes
1
answer
883
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 ...
- 31
-1
votes
2
answers
98
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 ...
- 257
0
votes
0
answers
18
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 ...
- 63
1
vote
1
answer
39
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 ...
- 2,076
0
votes
0
answers
67
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 ...
- 151