Questions tagged [sql-server-2014]
SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.
2,519
questions
1
vote
0answers
97 views
Performance of Query where time captured from SP statement completed differs by large from RPC completed
I am unable to understand the performance of below query when collected data from XE events:
Query
DECLARE @Importdate DATETIME = ISNULL ((
SELECT ImportDatetime
FROM
Customertable
where CustomerID ...
0
votes
0answers
18 views
Always on Availability Group not allowing restores on secondary replica
I can’t restore backups or t-logs to my secondary replica and perhaps you can lead me in the right direction or if you've seen this before, give me a hint.
I inherited an environment with zero ...
0
votes
0answers
30 views
How to configure a single report server with 2 different SQL servers? [closed]
I have the SQL report server configured in a web server(server1).
It used to be pointing to a single SQL server called sqlserver1.
Now I have set-up a new server called sqlserver2. I want to set-...
0
votes
1answer
26 views
Query to insert records from one table into another table based on the column of the first table [duplicate]
I have a table with the structure below :
Table(customer_Num int , Channel_Id Varchar(50) , Branch_Code int)
and some example data for this table is :
Table
...
0
votes
1answer
32 views
dbmail failing to send intermittently [duplicate]
We have a SQL Agent job that runs daily that sends emais out to our drivers with their schedules for the next day.
For 85% of drivers, the emails are not getting out of the SQL server. The other 15% ...
1
vote
1answer
59 views
Lost SQL credentials and unable to connect to SQL via SQLCMD
I have a machine running SQL 2014 but have neither the windows authentication or sa authentication details due to mismanagement of a previous support team.
Having tried logging in to SQL Management ...
0
votes
0answers
24 views
Check constraint on grouped conditions
Take the following table as an example:
GroupID AlphaID BetaID
1 0 1
1 1 1
1 2 2
2 0 1
2 1 2
...
1
vote
3answers
55 views
SQL Query taking long to execute especially with offset and fetch for pagination
This is the query I'm running for a report. It takes about 20 seconds to run without the offset and fetch. With those two, The query doesn't even complete in 10 minutes.
select *
-- h.ActionDate as ...
1
vote
2answers
122 views
Can I move SQL Server backup files on a running database?
Our backup drive is filling up with TRN and BAK files. I have administrative privileges on the server, but I don't have a login to the database.
Can I move the oldest files off to somewhere else ...
0
votes
1answer
20 views
Upload multiple files to SQL Server without access to the Server Box
I am trying to create a process that would bulk upload multiple csv files periodically to individual tables in SQL server. The issue I am having is those files are not located on the same server.
...
0
votes
2answers
27 views
Query to find the 'Branch_Code' of the 'Channel_Num' with the lowest 'Issue_Date' using T-SQL
Hope you're doing well
I have a table with the structure below :
Customer_Channel_Branch:(Customer_Num int ,Channel_Num int ,Branch_Code int ,Issue_date datetime)
Some Example Data :
...
-1
votes
1answer
26 views
General Question as best practice to collect data from extended events
Hope ya all doing good and safe:-
I am collecting query level metrics from extended events using below events for -
SP statement completed
SQL Statement completed
SQL Batch Completed
RPC Completed
...
0
votes
1answer
8 views
The offset 0 is invalid for log file .xel Specify an offset that exists in the log file
First of all, I am not DBA but I have to perform some DBA jobs. I am trying to read an extended file using,
SELECT [object_name],[event_data],[file_name],[file_offset],[Event_Time]
FROM ...
1
vote
1answer
40 views
Unable to start SQL Server Integration Services 12.0 service (SQL Server 2014) after upgrade to SQL Server 2019 and patched to CU4
I upgraded a SQL Server 2014 Standard instance to SQL Server 2019 Standard (also required upgrading the OS from Windows Server 2012 R2 to Windows Server 2019), and then patched to CU4.
During the SQL ...
0
votes
1answer
64 views
Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim (Msg 1205) when update
I have some issue when i test 100 user fill in attendance with same time.
i got error message like this
Transaction (Process ID) was deadlocked on lock resources with another
process and has ...
1
vote
1answer
16 views
Insert rows that match a condition, and return rows that don't
I am using Sql Server 2014
I have an expensive query. I want to insert the results of that query into a table.
However the results should match a certain condition, if they don't I don't want to ...
0
votes
0answers
34 views
Optimise the selection of two distinct column pairs from the same table
I need help with rewriting or editing the query/logic to improve it's performance. SQL Server 2014
I have the table structure specified below. I want to select two column pairs:
(SourceID + ...
-1
votes
1answer
33 views
Microsoft SQL Server Database Miroring
I have few questions about Microsoft SQL Server database Mirroring.
Fail over of database is Automatic or Manual in case of Microsoft SQL database mirroring?
In case of failure in Primary database ...
1
vote
1answer
111 views
Setting MAX memory for SQL servers depending upon the model of CPU
I have a general question regrading setting up the MAX SQL memory settings-
For our current CPU with below specs, server team bumped up OS memory from 400GB to 1TB:
Intel® Xeon® Processor E5-4640 v2 ...
0
votes
1answer
15 views
Recreate a Certifcate Encrypted by Password to another database
I have created a stored procedure which requires permissions across a number of databases:
USE MyDB;
CREATE PROCEDURE dbo.MyProc
(
@ScheduleName NVARCHAR(260),
@KeepSchedule BIT = 0
)
AS
...
2
votes
1answer
34 views
SQL Server - dump schema into separate files keeping installation order
While dumping SQL Server's schema, I have these two options: single file or multiple files. Single file is good because is can be used to install schema on clean server - parents are installed before ...
0
votes
0answers
21 views
Safely unistall SQL 2014 without impacting SQL 2017 install [duplicate]
As the title says, we have two instances of SQL installed on a server - SQL 2014 and SQL 2017 with Server 2019 as the OS. I attempted to perform an upgrade of 2014 to 2017 several months ago, but we ...
1
vote
2answers
60 views
Very large table makes the application performance too slow
We have an old application that has problems with performance. I narrow it down to few spots in the system that definitely require attention.
I found one table referenced in this app with 1,332,730,...
-2
votes
2answers
37 views
query tuning help [closed]
All,
Would need some tips / tricks to tune this query please:
WITH Candidates(ActivityId) AS(
SELECT ActivityId
FROM EmailHashBase WITH (NOLOCK)
WHERE ActivityId IN
(
...
2
votes
1answer
22 views
Copy SSRS reports to another server
I am new to SSRS and I have been tasked to copy the SSRS reports(from PROD) to another Server(QA).
I found many documentations that show how to migrate the reports by Restoring ReportServer, ...
0
votes
1answer
27 views
How to correct a primary key field in MS SQL 2014
I have a table that uses serial numbers as the primary key in a database. We had a bad import of some serial numbers, where a single apostrophe was included at the beginning of the field.
I attempted ...
0
votes
2answers
39 views
SQL Server instance level action tracking
How can we track actions happening at instance level in SQL Server 2014 .e.g. would like to know who created database, who changed recovery model, who setup backup strategy, who gave permissions to ...
1
vote
1answer
21 views
How to grant SQL Agent proxy to a public role in MSSQL 2014
I want to grant SQL Agent proxy to a public role in msdb database in MSSQL 2014.
I have the following query and I want this to return me some valid output.
USE [msdb]
SELECT
sp.name AS proxyname
...
2
votes
2answers
30 views
Force update statements to take page locks in the same order?
I have two update statements on the same table that take page locks in different orders. This leads to deadlocks.
Process A owns an update lock on page 1, and requests an update lock on
page 2.
...
0
votes
2answers
57 views
Getting Error 10054 after renaming the server
When I open SQL Server I have error 10054. Recently I changed the server name and I probably have this problem since. How can I solve it?
0
votes
0answers
27 views
Adding article to transactional replication causing blocking - schema mod lock
We have a transaction replication initialized from backup on a highly transactional OLTP database. When we add new articles(either from SSMS or using T-SQL) it causes massive blocking as the adding ...
0
votes
1answer
29 views
Retrieve by inserting ORDER of Merged table in SQL Server
I have a 3 tables such as "BASE_Customer", "BASE_Invoice" and "BASE_Payment" the below structure,
Query:
CREATE TABLE BASE_Customer
(
CustomerId INT IDENTITY(1,1),
CustomerName VARCHAR(45)
...
0
votes
0answers
37 views
How to Retrieve the order and Credit Debit and Balance from many tables
Tables are INVOICE, PAYMENT, RETURN_INVOICE, RETURN_PAYMENT, etc in my database, i want to retrieve data CREDIT, DEBIT AND BALANCE customer wise and order should be DATE wise and table insertion order,...
2
votes
1answer
79 views
sp_Blitzcache doesn't return as many results as sys.dm_exec_query_stats for a given query
I am trying to find plans for a particular set of queries (those that update MyTable) using sp_blitzcache:
EXEC sp_Blitzcache @SlowlySearchPlansFor = 'UPDATE [My Table]'
this returns 3 results, ...
1
vote
1answer
43 views
why would a simple query have poor performance on MS SQL Server 2014 or 2014SP3 when on other versions it performs well?
I am running the following query on MS SQL Server 2014 (and also on 2014SP3)
SET NOCOUNT ON
CREATE TABLE #GUIDs(
PartyNames_GUID UNIQUEIDENTIFIER NULL,
Party_GUID UNIQUEIDENTIFIER NULL,
...
1
vote
1answer
38 views
How to Retrieve Credit Debit and Balance From More Than One Table in SQL Server
I have more than 1 table such as INVOICE, PAYMENT, RETURN_INVOICE, RETURN_PAYMENT, etc. i want to retrieve data CREDIT, DEBIT AND BALANCE each customer wise
below are tables and insert query ...
1
vote
2answers
45 views
Measuring the need of Capacity or scaling SQL server
This would be more of General approach question rather than too technical:
I have one of my application reach out to me wandering if we need to scale up SQL server in terms of CPU memory disk or CPU ...
0
votes
1answer
67 views
Sql Server 2014 update path advice
I recently inherited a SQL Server 2014 installation and I'd like to install updates. The virtual machine runs Windows Server 2016 standard. The server has about 50+ databases with 400Gb of data. I'm a ...
3
votes
2answers
246 views
Replace a sequential set of numbers with special character
I have a varchar(200) column that contains entries such as,
ABC123124_A12312
ABC123_A1212
ABC123124_B12312
AC123124_AD12312
A12312_123
etc..
I want to replace a sequence of numbers with a single * ...
0
votes
0answers
45 views
Quick question on Scalar UDF's
I am trying to browse online but can not get enough info on how to measure performance of scalar UDF's in SQL server 2014.
Newer version yes i do see from plans we can get the same and newer dmv's
...
0
votes
0answers
30 views
SSIS code page / unicode conversion issue
We have a process that loads flat files into a staging table. The table has varchar(2000) columns, and the SSIS package is using code page 1252. We've noticed that some unicode characters are being ...
1
vote
1answer
53 views
Replication fails with “Transaction log for database is full due to LOG_BACKUP”
Replication is failing with the subject error.
The log_reuse_wait_desc states LOG_BACKUP.
I have performed both database and log backups.
After changing the recovery mode to single, performing ...
0
votes
0answers
40 views
Range X-X lock on table with primary key value
I have a table with a primary key and clustered index. I am updating the table as below,
update MyTable1
set
SomeColumns = @SomeColums
where
MyPkColumn=@MyPkColumn
I have another ...
1
vote
2answers
58 views
Most efficient way to identify records with any number of key columns
I work with large third party data-sets. Long experience has taught me that it is a really good idea to give incoming lines of data a surrogate ID as soon as it enters my system so I can track it ...
1
vote
3answers
62 views
Table with image data base64 per row causes deadlocks/block
We have a table,
CREATE TABLE [dbo].[MyTable](
[MasterKey] [uniqueidentifier] NOT NULL,
[DetailKey] [varchar](100) NULL,
[JSON] [nvarchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [...
0
votes
1answer
55 views
Recreate ##MS_SSISServerCleanupJobLogin## user as [##MS_SSISServerCleanupJobUser##
During an install of SQL Server 2014 Service Pack 3 we received the following error:
Error: 15151, Severity: 16, State: 1.
Cannot find the user '##MS_SSISServerCleanupJobUser##', because it does not ...
0
votes
1answer
63 views
How to fill/insert fake data into a MS Sql Server
I'm trying to test out some configuration settings for an MS Sql Server FILEGROUP and to do this I wish to fill my FILEGROUPdisk up so it will start populating the 2nd FILEGROUP.
I've just created a ...
0
votes
2answers
65 views
Transaction Log Backup Affecting Applications Performance
We have a database around terabytes of data. We are taking FULL backup every week, differential backup daily and transaction log backup after every hour. Sometimes this transaction log backup takes ...
0
votes
0answers
18 views
Full text sql server strange results
I have 2 documents let's say A ( word document) and b ( pdf version ) stored as blob and full text search enabled.
I have 2 words which are present in both documents. Searching for the first word ...
2
votes
1answer
48 views
What are the main principles of SQL Server AGs depending on WSFC?
We have WSFC with 4 nodes + a file share witness.
Only nodes 1-3 have SQL server on it with Availability Groups (AGs), and node 4 is shut down gracefully.
Node 1 has the primary AAG server.
Now,...
