Questions tagged [sql-server-2014]
SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.
2,557
questions
0
votes
0answers
5 views
What can cause log shipping copy step to occasionally fail with access denied?
Have sql agent job for log shipping copy step that fails every once in a while with "Reason: access is denied" but no other detail.
Here is what the failing step looks like:
"C:\Program ...
0
votes
0answers
33 views
SQL Server Setup Failure
Unable to upgrade the enterprise edition of SQL Server 2014 SP1 (12.1.4100.1) to SP3 version. I checked the registry key "ConfigurationState" and it's already 1 but still got the below error
...
0
votes
2answers
17 views
Schedule Job to Start at Different Steps
I am working on a system that generates a number of recurring emails as parts of various jobs scheduled in SQL Server Agent. The emails contain text-based reports that are loosely associated to the ...
0
votes
0answers
26 views
SQL Server 2017 Always-ON
I have configured Distributed AG from DC1 to DC2 (SQL 2017 Ent edition with CU22 version)
DC1- DB1, DB2 and DC2-DB3, DB4
I have restarted DC1-DB1 SQL services, after restart DC1-DB2 should be primary....
5
votes
1answer
168 views
How do pages get updated checksum when switching page verification mode from torn page to checksum?
Have an older but large db (6tb on the san) that still has its page verification option set to TORN_PAGE_DETECTION. How do you apply the new checksum to all existing pages when changing page ...
1
vote
3answers
50 views
Database Shrink, Backup, then Restore
I understand all the reason why it's not advisable to shrink
I only preface this to avoid falling into that conversation.. so, I have 1.4tb database.. about 700gb is free. This is due to the fact that ...
-1
votes
1answer
24 views
How to compare production datetime to the reference table?
I have Two tables one is "Reference" Table another one is "Production" Table.If i get a production report, "Production" table should refer the "Reference" Table ...
1
vote
1answer
22 views
SQL Server Identifying Connections that are utilising SQL from an application
Is it possible to identify connections in SQL Server 2014 that are utilising SQL that is passed from an application as opposed to calling an SP ?
-1
votes
0answers
23 views
How do I disable SQL Mini external dumper sqldumper.exe from running when SQL Server is restarting?
Context: On a SQL Server 2014 Enterprise instance, a restart because of a high memory usage (80 GB+) is taking 30 minutes and counting, when it talkes usually less than one minute.
I have noticed the ...
0
votes
1answer
16 views
Creating Linked Server Object to same instance
I'm trying to create a Linked Server Object back to the same SQL Express 2014 server instance (see below for the context), but I'm hitting the following error...
Cannot initialize the data source ...
0
votes
0answers
33 views
Slow on searching duplicate records in millions of records
I've below three tables
CREATE TABLE [dbo].[RegisteredUser] (
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[UserCode] char(9) NOT NULL,
[Email] [varbinary](5000) NULL,
...
3
votes
1answer
234 views
Divide, round and replace in one select statement - tranformation from bytes to GB in SQL Server
I have a database which has me a value, a sharesize in bytes and a sharename. Which is pretty accurate but not useful for my enduser. Therefore I transformed the value to GB.
Which worked great by ...
1
vote
1answer
29 views
Display all employee
i want to display all employee from tbl_Employee_Master ,weather emp_Id is exit or not in #tbl_assign_Allowance_Deduction table
I developed a query,but it fetching those record where asd.sal_ID is ...
-1
votes
0answers
16 views
Allowance and Deduction amount set in one row ,against each employee
Below is my query,I want Allowance and Deduction of each employee in one row,employee ID must not be repeated ,as you may see image Employee ID 30007 ,is being repeated.
SELECT (M.Emp_ID) as ID , M....
0
votes
0answers
29 views
SQL Server Availability Groups and CDC
I have a simple SQL Server 2014 Availability Group, one database on a two-node cluster. We need to use CDC to move data to another environment, in my case AWS.
This is a super-critical OLTP very large ...
0
votes
0answers
13 views
Restore the sql server 2014 database on specific time
Good day sirs, we encountered a problem in our database (lets call db1) and I hope you can help me with it :(
Last August 19, we generate a backup file but on Aug. 20 we fail do so. August 24 my co-...
2
votes
1answer
89 views
MASSIVE Identity column value jump in SQL Server 2014
So in the middle of basic coding and testing we saw a huge non-patterned jump in Identity values for multiple tables. We are unaware of any server blips or attempted bulk operations, but DBAs are ...
7
votes
1answer
372 views
Improving performance when ordering by a column of a joined table
I have a parent table that contains a foreign key to a lookup table (simplified example):
CREATE TABLE [dbo].[Parent] (
[Id] [uniqueidentifier] NOT NULL,
[LookupId] [uniqueidentifier] NULL
)
...
-2
votes
1answer
35 views
Date parameter is not fetching record as per giving date
CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
CREATE TABLE #OrderDetail (IDOD INT,OrderNO int,CodeItem int,orderqty int);
CREATE TABLE #Sections (...
0
votes
1answer
58 views
Accelerated log growth
The log file for my SQL Server 2014 database is growing rapidly (currently in 32 GB in COMPLETE RECOVERY mode). This is creating storage issues on my server.
The database has a set of job's with ...
0
votes
1answer
111 views
Query tuning update with hash flow distinct operator
Need some help in understanding the slowness in one of the UPDATE statements like below:-
UPDATE TOP (100) xyz
SET xyz.flag = 1
OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc....
0
votes
0answers
19 views
I'm considering removing SQL Server Partitioning from a Database. Is there anything I should watch out for?
I am considering removing SQL Server partitioning from a Table. I believe that it was implemented for performance reasons, which is not required due to the low volume of data involved. The version of ...
2
votes
1answer
35 views
SQL Server 2014 Restore DB only has “Verify Backup Media” option
I just downloaded SQL Server 2014 Management Studio. I got the backup files ".bak" from my colleague. When I try to do a restore from "Database > Restore Database...", the ...
0
votes
0answers
20 views
Cannot DROP/ALTER a VIEW because other objects depend on it
I need to ALTER a VIEW in order to add an additional field but I am unable to alter/drop it because other objects (several functions and views) depend on it. Is there any way to alter the view without ...
3
votes
1answer
77 views
What could cause a sleeping session to have an open transaction?
For one of our SQL servers there has been a proc which creates a blocking mess:
That stored proc completes under a sec most of the times but somehow is leaving transaction open. What i see from ...
0
votes
1answer
94 views
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
I want ascending wise data CID wise
select f.Category,f.Name,(f.Bigbale_QTY-f.Dispatch_QTY) as [Balance],(f.Bigbale_weight-f.Dispatch_Weight) as [W_Balance] from(
select e.Category,e.Name,isnull(...
1
vote
1answer
39 views
Can two SQL “Always on” nodes have different datafiles position on disk?
I have two Always on instances SQL server 2014 configured on two servers (primary read/write, secondary only sync). First instance has 13 db and the second has only one. I need to move all the ...
0
votes
0answers
17 views
Optimal selection of predefined non-materialised data
Take the following result set as an example:
Store Category Item Cost Stock Sold Ordered
ABC Accessories Lock 344.31 281 141 225
DEF ...
0
votes
0answers
13 views
Query modify add column of Closing and Floor_Total
I want of Column of Total_Floor= (Opening + C_Weight)
Then need another column of Closing= Total_Floor-S_Weight -B_Weight
Below is my query
DECLARE @FirstDOM DATETIME = ( SELECT CONVERT(DATE, ...
0
votes
1answer
35 views
How to use three query as a subquery?
Below is query ,i want to convert it into sub query
declare @FirstDOM datetime, @LastDOM datetime
set @FirstDOM = (select CONVERT(DATE,dateadd(dd,-(day(getdate())+30),getdate())) )
set @LastDOM = (...
3
votes
2answers
96 views
How many threads being used while query execution?
I have AdventureWorks2014 database installed on my test SQL Server 2014. I am planning to execute the following query:
SELECT *
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p ON sod....
1
vote
2answers
48 views
Why does TempDB spill happen even though statistics are correct?
I read a great article published by Brent Ozar and came up with some questions related to memory grant. I am unable to address my questions in the comment section of his article, so I thought to get ...
1
vote
1answer
52 views
How to compress a non-shrinkable table with nvarchar(max) fields?
We have a serious problem which was on our minds for pretty much two weeks already. We have a big database (~250 GB, MS SQL Server 2014) and we needed to shrink it somehow, since our SSD is almost ...
0
votes
0answers
39 views
Clearing plan cache improves performance - bad plans in cache?
I have run into this situation a number of times on a VM with SQL Server Express 12.0.6205.1 (SQL Server 2014 SP3 CU1)
Using SSMS, I'll run a stored procedure a few times. It consistently takes, say ...
1
vote
0answers
33 views
How statistics play a major role in legacy CE vs new CE
We have quite some issues going on for one of our SQL server 2014 SP3 after compatibility change to 120 from 100.
Yes i am aware of few hints to make use of legacy CE and have implemented as success. ...
0
votes
1answer
26 views
Collecting VLF information on all versions of SQL server
Based on my monitoring collection, i am having an issue while collecting info regarding how many VLF' i have for # of databases in SQL instance.
1st issue - Is there a single script which can be used ...
0
votes
0answers
14 views
SQL Server CDC: Can/should there be more than one variable value in the cdc_states table
Question:
Should there be multiple CDC state values (i.e., rows) in the cdc_state table when there are CDC packages running that are dependent on the result of other CDC packages? None of the ...
0
votes
1answer
27 views
How do get correct instance name on Multi instance cluster
Just a quick question and somehow unable to figure it out.
We have a Active Active cluster or should i say Multi instance cluster as below. node A and B are part of same window cluster
Node A --> ...
0
votes
0answers
25 views
How to remove linked servers provider?
I installed an Oracle OraOLEDB.Oracle provider. Now I want to remove that provider from the list. How can I do that?
0
votes
0answers
33 views
OPENQUERY giving wrong result on linked server
I have setup linked servers on my SQL Server and choose Oracle as a linked server.
SOMETIMES, When I run the query below under the SA username, I get different result. However when I run under my own ...
4
votes
2answers
97 views
is there any point of moving tempdb files into different disk drive if all LUNs are from the same RAID pool?
According to best practices, it is recommended to move all tempdb (not just tempdb) files into different physical disk.
I have a virtual server which had originally 4 LUNs from the same RAID 10 pool....
0
votes
0answers
22 views
Blocking task report is showing false positive
Here is the code I am using to send email alerts. It seems to send more false +ves, can anyone guide me how to resolve this ?
DECLARE @Waiting INT;
DECLARE @Message NVARCHAR(MAX);
SELECT @Waiting =...
3
votes
2answers
155 views
Changing database compatibility from legacy CE to 120
Just seeking an expert/practical advise from DBA point of view where one of our application DB running on SQL 2014 after migration had old DB compatibility level i.e 100.(SQL2008)
From DEV point of ...
0
votes
1answer
45 views
SQL server Service Accounts permission
I will be changing the existing SQL Server Service accounts from the default to Domain accounts from the SSCM which i believe will set all the required permission. One thing I am not sure whether the ...
0
votes
1answer
26 views
Chaining remote SQL Server Agent Jobs with success/failure feedback
I have 2 Microsoft SQL Server 2014 (SP3-CU4) instances, residing on different servers.
ServerA has a linked server, pointing to ServerB\InstanceB.
Both instances have a job, and JobA triggers the ...
0
votes
1answer
28 views
quoted identifiers on filtered indices
I´ve 2 questions. I am using a T-SQL Server 2014.
When creating a table with a filtered index on a T-SQL server, it is a must-have to set QUOTED_IDENTIFIER to ON. Why is this so?
I´ve some SPs which ...
4
votes
1answer
168 views
Compatibility mode change does not reflect on secondaries
I was of belief that changing compatibility mode on primary database in database mirroring would make the change for mirror database.
But it proved to be wrong. I queried using sys.databases on ...
0
votes
1answer
24 views
can't find column stats for newly created table
I am trying to simulate a plan recompilation due to auto update stats which is triggered when threshold(500 + 20 percent modifications) reached.
database current settings are:
I started with ...
0
votes
1answer
46 views
Does plan recompilation happens to plan guide?
Does plan recompilation happens for plan guide due to the, for example, auto update stats?
P.S Do not take into account adding a hint OPTION (RECOMPILE) in the plan guide
1
vote
1answer
33 views
Is filling up plan cache causes a decrease in space allocated for data cache?
SQL Server uses allocated server memory for different kind of purposes. Two of them are plan cache and data cache which are used to store execution plans and actual data correspondingly.
My question: ...
