Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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 ...