SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.
0
votes
0answers
8 views
Publication shows up in secondary server
I have created log shipping and the primary server is also used for transactional replication. Now after creating log shipping I have noticed the publisher shows up on the secondary and it wont let ...
0
votes
0answers
6 views
Issues setting up a publisher for transactional replication on a SQL Server with a custom instance
I'm trying to set up transactional replication of a fairly large database (Business Activity Monitor database from BizTalk Server 2013R2) (>500 GB).
I have an active/passive cluster which holds the ...
-1
votes
1answer
47 views
Can ETL access Data while Rebuild Index Operation is running
Can I get some help to understand below questions:
We have a ETL job running (Datawarehouse Server) and weekly index operations (Production Server)(Ola Hallengren script) , below questions are ...
-3
votes
0answers
21 views
Assembly and types objects [on hold]
I got a question from the teacher:
I need to create an object that meet the following requirements:
Run managed code packaged in an assembly that was created in the Microsoft.NET
Framework and ...
-1
votes
0answers
63 views
SQL Server upgrade from 2014 to 2017 - Why degraded performance? [on hold]
We've worked with Microsoft over the month on a SQL Server 2014 to 2017 upgrade. We've made no progress with the expected performance improvements ~30-70%. We've actually had ~20-40% degradation.
...
0
votes
0answers
17 views
Schema changes in transactional replication
I have a publisher (SQL Server cluster) in one box and the distributor/subscriber together is on the other box. I have a requirement by the vendor that if there is a new build (schema/database ...
0
votes
1answer
22 views
Error when restoring database from disk using a variable [on hold]
I have the following query in SQL Server 2014. The backup of the database completes in approx 150 seconds but the restore/verify step fails with an error.
Can you tell me where I'm going wrong?
I ...
0
votes
2answers
21 views
TDE using EKM device
We use an external HSM device to create and store keys and use it for TDE.
Our auditors are asking questions about where the keys are store. So obviously the key is stored in the external HSM device. ...
1
vote
2answers
36 views
What is the potential fallout of changing SQL Server's log on account?
I need to be able to read files from a UNC path in TSQL and am getting an access denied error. I've read I must change the user SQL Server logs in as to a domain user, it's currently running as NT ...
0
votes
3answers
66 views
Trouble using Max() function
I have 2 tables: Customer and Billing, Customer contains generic details (name, address, you get the idea) and Billing contains items that are billed.
The end date of when the item gets billed to is ...
0
votes
1answer
32 views
The log.trc file is missing
The log.trc file is missing on the server, but there are archived trace files like (log_15.trc, log_16.trc, log_17.trc, log_18.trc, and log_19.trc). Is this something to be concerned about?
Is ...
0
votes
0answers
35 views
Application becomes slow during maintenance of 4.5 TB database [closed]
We have a very large database (4.5 terabyte). When maintenance jobs are run, application users experience slowness. How do you solve the slowness problem? We have 4 Active-Active SQL servers, with one ...
0
votes
0answers
20 views
Deadlock when dropping multiple constraints on multiple tables at the same time
Background
We have a data warehouse process that calls the following code:
exec sp_executesql N'EXEC maintenance.sp_fk_statements @P1,@P2,@P3',
N'@P1 varchar(3),
@P2 varchar(9),
@P3 ...
1
vote
1answer
36 views
Delete Joined to Derived Table Causes Index Scan and Table Spool
Our application uses a popular 3rd-party extension to Entity Framework that allows for valuable things that native Entity Framework is not capable of or designed to do, like bulk deletes and updates.
...
-1
votes
2answers
50 views
How to run Stored procedure on SQL linked Server [closed]
Msg 7202, Level 11, State 2, Line 11 Could not find server
'192.168.1.45' in sys.servers. Verify that the correct server name was
specified. If necessary, execute the stored procedure
...
4
votes
2answers
79 views
Temp Table Filtered Index Creation is Blocked When Stored Procedure is Run Multiple Times Concurrently
Are there any known blocking issues in SQL Server 2014 that are specific to creating one or more filtered indexes on a temp table after the initial table creation? (i.e. not an inline index creation)
...
2
votes
2answers
57 views
Upgrade in-place with low disk space on C: drive
I am due to upgrade SQL Server 2005 Express to 2014 Express. This will be an in-place upgrade.
It's not my preferred way of upgrading, but being a physical machine and in a specific secure network ...
0
votes
2answers
36 views
Store the result from a cursor with exec into a temp table without explicitly named columns
I need to execute a query in each db I have got on my server and the result needs to be stored into a temp table. Due to the query model, I can't explicitly set column names for temp table. I've tried ...
1
vote
0answers
29 views
Switch-SQLAvailability error during DB Restore in AlwaysON Group
Can anyone share your inputs on below and how to fix the issue and possible causes of failure?
I am trying to restore a DB using PS script and at one step it is failing during the role change test ...
-1
votes
0answers
12 views
Sending job notifications from SQL Agent jobs (from Primary node only) [duplicate]
In my current setup, I have an Availability Group cluster with 3 nodes. 1 being Primary, 1 secondary failover and another is just a read only listener. All of these have backup jobs and they sometimes ...
1
vote
3answers
62 views
Understanding SQL Server Cache
I have a tricky question and I do not exactly know where to find relevant information to this.
I have SQL Server 2014 Enterprise installed on a database server. On this server there exist multiple ...
0
votes
0answers
19 views
unable to restore .bak file in sql server [duplicate]
I have taken the backup .bak file in the backup file path. when trying to restore the database into the Sql getting the error. I have given the error message in the screenshot.
1
vote
0answers
10 views
Snapshot Folder Location for Replication with Clustered Instance
I am assigned to create a transactional replication with a SQL Server clustered instance for reporting purposes. I'm planning to use the Distributor and the Subscriber on the same box.
Regarding the ...
1
vote
1answer
33 views
Can't use CAST, Convert with UDT in Sql Server 2014 [closed]
My tables consist of many decimal columns with different precision.
I wanted to standardize the usage of precision by creating several UDTs with predefined precision and use them as column type ...
0
votes
2answers
49 views
How to stop the SQL Server Scheduler with T-SQL?
I have a job that I manually start in coordinated time periods that does a bunch of heavy lifting (mostly restoring backups and configuring for a different server)
There are several jobs scheduled to ...
4
votes
1answer
74 views
TSQL - create a function that takes a multi-row query result as an argument
I work with a variety of servers on mostly SQL Server 2014 and earlier.
I have boiler-plate code that can take the results of an arbitrary select statement (returning a list of varchar or the like) ...
0
votes
1answer
45 views
Getting around Primary Key requiring non-nullable columns
Word Problem
I need to be able to create a table that has a column which is a foreign key and is nullable, but is also part of the (composite) primary key. Obviously the problem is that (at least ...
0
votes
1answer
33 views
ISO 27001 - resetting passwords for SQL Server accounts
We are implementing security changes in order to pass an ISO 27001 audit.
We are in the process of setting all domain accounts (user accounts, admin accounts, service accounts) to have their passwords ...
2
votes
3answers
62 views
Very complicated query is suddenly taking 35 minutes to finish in SQL Server 2014
I have a very complicated query that produces correct results slowly but ok in SQL Server 2012. After upgrade to SQL Server 2014 and after setting compatibility mode to 2014 (120) for the database, it ...
2
votes
3answers
63 views
Cleaning up specific domain logins on about 50 servers and therefore the users
There is a specific need to clean up logins from the SQL Servers after migration from one domain say abc to new domain foo.
After migration as we still have those old logins and users under naming of ...
0
votes
0answers
18 views
Perfmon Counter stop collecting at random Intervals
I have created a collector set in Windows Performance Monitor which logs to a SQL table using an ODBC connection. The collector set runs everyday from 00:00 and starts polling and stops polling for ...
2
votes
1answer
59 views
IO Statistics for Index Seek
Does Statistics IO (Logical/Physical Reads) include index pages that are not leaf-level?
The following example is lifted directly from this old post from Gail Shaw:
https://sqlinthewild.co.za/index....
0
votes
1answer
59 views
What is meant by “nonclustered legacy instance of SQL Server”?
When I research nonclustered legacy instance in SQL Server, all I get is some results about indexes. This is obviously not related to or affiliated with indexes, so what is meant by "clustered ...
0
votes
1answer
21 views
Copy database wizard - any change in lsn number of source database
After Copy database wizard - database restore in another server . Any change in LSN number of source database . is it same as copy only backup?
1
vote
1answer
49 views
What does “Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 64” in the SQL Server Log mean?
Every once in a while, I see the error
Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 64
in the SQL Server log. Oddly this seems to get thrown at off hours where there is no job or backup ...
2
votes
1answer
20 views
Error when using WITH (DROP_EXISTING = ON)
Okay so I've been condensing files off of some tiny files and moving them onto an FG that I created for these miscellaneous tables. I've been moving these tables by recreating the indexes with the ...
3
votes
2answers
32 views
SQL Server - WITH REPLACE
What is the difference when using or not using "With Replace" when restoring database. I know that With Replace, I would get identical database as backup file even log file would be replaced/...
5
votes
1answer
160 views
Minimizing/avoiding data repetition and choosing between compound keys and unique indexes
I am absolutely new to database development and i'm trying to build a question bank basically.
The questions have to be categorized based on Class, Subject and Topic.
Since each Class can have many ...
0
votes
1answer
27 views
Need some help in Group by StateID - TSQL Query
Can anyone help me on below scenario I need small help on group by state ID
Below is my query and I am tried to get all the out put in single line .
But I am getting different lines for each year . ...
1
vote
1answer
42 views
Proactively Determining Bad Plans/Poor Performance at a Query Level
tldr; I'm looking for ways to monitor server performance to alert when individual, small, fast queries get a bad plan that will in aggregate hurt the performance of the business.
Over the past week ...
0
votes
0answers
11 views
SQL Server Backup Service Master Key - Multiple Passwords
Ignoring the reasons behind the request, if I backup the Service Master Key twice, using a different password each time, is either a valid backup for restoration purposes (assuming the correct ...
10
votes
2answers
129 views
Calculating stock quantity based on change log
Imagine that you have the following table structure:
LogId | ProductId | FromPositionId | ToPositionId | Date | Quantity
---------------------------------------------------------------...
0
votes
1answer
50 views
Stuck at optimizing “not in clause” for a medium-sized query across two tables
I have a system to teach English. For each word someone learns, I record:
LearnedWords
-- Id
-- UserId
-- WordId
And for each new text that I give to a learner, I want to indicate what ...
0
votes
2answers
32 views
Creating an auditing strategy when a user has been defined in a table instead of in SQL
To preface this post, I'm pretty new to database design, so please excuse me if there's already a simple or immediate answer to this post.
In my DB I have a table called t_employees which acts as a "...
0
votes
2answers
40 views
Partitioning - when to create new scheme?
I have the following partition setup on a table.
I'm wanting to create a new partition that begins on 2019-01-01.
Is the the correct way of doing it, and can it be run now so it's ready to go for ...
1
vote
2answers
47 views
Unable to update MySQL table from linked server on SQL Server 2014
I'm using SQL Server 2014, with a linked server to a MySQL database on AWS. I need to update rows on that linked server, using data that exists on another linked server. And if that wasn't complicated ...
1
vote
1answer
29 views
service broker with mirroring
After we tried configure a database with service broker into an always on environment and failed (the service broker is not working properly) we are now considering to try mirroring for high ...
0
votes
1answer
34 views
How to get the consolidated ServiceID with the sum of File Size associated with over an year
Can anyone please help me on below to get the consolidated ServiceID with the sum of File Size associated with it over an year.
Complete Code (For reference)
If(OBJECT_ID('tempdb..#temp1') Is Not ...
3
votes
1answer
83 views
Bad row estimate following Compute Scalar operator in plan
I'm struggling to understand where a row estimate is coming from in an execution plan.
Paste the plan link
declare
@BatchKey INT = 1, @ParentBatchKey INT = 1,
@QuoteRef varchar(50) = 'Q00018249',
@...
4
votes
0answers
27 views
Variable column size overflow when merging partition
Supporting a product that out of the box does ETL to an OLTP DB every night for that day's activity, and every day is a new partition. Well, over x number of years this gets kind of messy. So in the ...
