Questions tagged [sql-server-2016]

SQL Server 2016 (major build version 13.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
0
votes
2answers
41 views

SQL Server Setup Error

I'm getting the following error at the very end of SQL Server 2016 setup. Updating permission setting for file 'Local Machine' failed. Principal 'SYSTEM\CurrentControlSet\Services\Eventlog\...
2
votes
1answer
31 views

What's the best way to concatenate a multi-column foreign key?

We are expanding our data warehouse to include a new source system. In the new system, we want a single column for a primary key, but in the old system it's two columns. For exmaple: create table #...
1
vote
2answers
41 views

What realistic issues can occur from shrinking the log files on my database (either through the SSMS UI or issuing a DBCC ShrinkFile command)?

I've heard talk of potential corruption or data loss occurring from shrinking the database data or log files. I'm curious in particular about only shrinking just the log files, what are some real ...
0
votes
0answers
24 views

Getting extra " double quotes while downloading report to TEXT or CSV

Recently developers created a report where they are concatenating each value of each column with " (double quote) in between. Like select '"'+first_name+'"'+last_name+'"' from table_name; On the ...
2
votes
1answer
49 views

Extended Events Collation resolution error

When trying to expand the sessions under extended events I am getting a Collation Resolution error in SSMS 18.2 (15.0.18142): Looking at a trace I have found that this query is what is giving me the ...
1
vote
1answer
44 views

Why can't the query processor produce a query plan with a FORCESEEK hint when I'm INNER JOINing by the clustered index on both indexed views?

I'm getting the following error when I try to use the FORCESEEK hint with the query below: Msg 8622, Level 16, State 1, Line 96 Query processor could not produce a query plan because of the hints ...
0
votes
1answer
23 views

Why when I JOIN to a casted field in an index view, the execution plan warns about “type conversion in expression”?

I thought an indexed view was materialized and therefor stored on disk. If that's the case, I would've thought that any type conversions I'm doing in the view would materialize as that datatype on ...
1
vote
1answer
40 views

Do filtered indexed views cause contention on the underlying table when rows are added to the table that fall outside the filter?

If I create an indexed view on a table and the query on that table includes a filter, when new records are added to that table that don't fall into that filter (and therefor wouldn't show in the ...
1
vote
1answer
55 views

SQL Server experiencing THREADPOOL waits while still having plenty of available workers

I recently took a look at my production SQL Server's cumulative threadpool waits, and it's literally days worth of waits. So I started doing some digging. I'm on a 32 core, 64 bit server (running on ...
10
votes
1answer
2k views

Why is casting a DATE field to VARCHAR datatype non-deterministic and is there a way to make it deterministic?

I'm trying to create an indexed view where the index is on a field in the view which is a hash of a bunch of the columns of the underlying table in the view. Here's an example: CREATE VIEW ...
2
votes
1answer
90 views

Is there an easy way to SELECT FROM another session's temp table?

Is there an easy way to select the data from another session's temp table? I need to do so ad-hocly to debug an issue that I have limited visibility on otherwise. E.g. if I have access to TEMPDB, is ...
4
votes
2answers
101 views

Does the command SET STATISTICS…ON turn statistics information on for all queries running throughout the server or just the current connection?

In regards to SET STATISTICS TIME ON and SET STATISTICS IO ON I noticed in the remarks of the Microsoft doc, it says the following: When STATISTICS IO is ON, statistical information is displayed, ...
4
votes
2answers
625 views

Query execution plan is horrible until Statistics are Updated

I hope you guys can help me here. Our application polls a message table every 3 seconds looking for notifications to send out. This works well on all our Customers (single-tenant DB) except one. They ...
3
votes
1answer
98 views

If sp_ExecuteSql creates a new session, how come I can access a local temp table created (prior to it's execution) outside of the dynamic SQL?

If local temp tables are only available to the current session, and sp_ExecuteSql creates a new session to execute the dynamic SQL string passed into it, how can that dynamic SQL query access a temp ...
3
votes
1answer
147 views

SQL Server job stuck even after restart

I have a SQL Server Agent job stuck in "In Progress". It's a FULL backup that usually takes 30 minutes, but it's taking 7 hours and counting. I've restarted the Agent and then the whole OS and it ...
2
votes
1answer
39 views

Does enabling LPIM work even if the memory pressure is coming from SQL Server itself

I have a scenario where I have 128 GB Physical memory for the SQL Server 2016 machine. It is a SQL Server dedicated machine. I have 110 GB allocated to SQL Server itself. I have performance issues ...
0
votes
2answers
41 views

How to drop a procedure across a lot of databases (SQL Server 2016)

I have a simple procedure that exists in over 150 databases. I can delete them one by one, which is the most stupid work to do in the world of software development. Or I can somehow dynamically delete ...
1
vote
1answer
46 views

Migrating old SQL Servers

I found myself in a rather complex situation and need some advice... We are moving our complete environment to a new Server and virtual servers. I have reached a point where i found out that some of ...
1
vote
1answer
39 views

Can I avoid TABLOCKX on DELETE?

Goal: Delete several billion records old records (roughly 600GB) across three tables with zero downtime. Methodology and Background: I plan to delete batches of records associated with 1 Million ...
0
votes
1answer
44 views

Will a MERGE between two tables ever be faster than a DROP and SELECT INTO on the second table?

Is there a scenario where it's faster for synchronizing the data from TableA to TableB by using a MERGE query as opposed to dropping TableB and doing a SELECT * INTO TableB FROM TableA?
2
votes
2answers
50 views

What type of locks does BEGIN TRANSACTION cause against a specific table that's being read from?

In the following example, what kinds of locks are generated from the explicit transaction block that wraps the SELECT INTO query? BEGIN TRANSACTION T1 SELECT Field1, Field2, Field3 INTO TableB FROM ...
1
vote
0answers
28 views

Does dynamic SQL executed by sp_ExecuteSQL typically take longer to parse and compile, especially as the query complexity increases?

I'm noticing a batch of queries that used to parse and compile pretty instantaneously now take anywhere between 5 to 15 seconds to parse and compile as a dynamic SQL statement that is executed with ...
1
vote
0answers
11 views

Is there a way to provide a client without administrator access additional information when they receive Error 18456?

When a user fails to log in to SQL Server, they receive this error: Login failed for user 'foo'. (Microsoft SQL Server, Error: 18456) On the client side, this error is always State 1. This is ...
0
votes
1answer
25 views

Is there a way to determine what caused the last growth operation of my database (MDF file)? [duplicate]

If I don't already have any logging in place, is there any way to use the DMVs or anything else on SQL Server to determine which query / event caused my database's last grow operation?
0
votes
1answer
36 views

SQL logic tuning - approach [closed]

I've simulated my requirement in the below SQL and the o/p is as expected. My question is - if this is the optimal way of achieving or I'm missing something very obvious technique ? I need to find ...
-1
votes
1answer
21 views

DATEPART YEAR from 'yyyymmdd' Formatted Date

I'm working with a table that stores the date field as 'YYYYMMDD' and I need to filter the query by the YEAR, using the DATEPART from that same date field. WHERE DATEPART(yy,'YYYYMMDD') = 2018
2
votes
1answer
26 views

How to decrypt dtsx package?

I've got some SSIS packages stored as varbinary, and I can load them into a temp table like so: Use SSISDB if object_id('tempdb..#package') is not null begin drop table #package end ...
2
votes
1answer
24 views

User and Schema Recreated during Stored Procedure Creation

As mentioned in the subject, a new user and schema are being created when a developer I'm working with creates a stored procedure. Originally I made a user and login for that developer individually ...
6
votes
3answers
769 views

Keeping data in old Database (SQL 2008) identical to new Database (SQL 2016)?

I am moving database to new server. New server has SQL Server 2016 running, current server has SQL Server 2008. I need to have backup/fallback plan in place for few weeks (in case something goes ...
0
votes
1answer
49 views

autogrowth set higher than available disk

First all our autogrow settings are either turned off or the file size is set to the max autogrow size. I guess same difference as I came in after the setup. Although I've never had this problem, I'...
0
votes
1answer
47 views

How to print more than 8,000 characters at a time to the SSMS Message window, without compromising text formatting?

Whenever I write dynamic SQL, I typically include a PRINT @DynamicSQL statement in a comment right above the EXEC sp_ExecuteSQL @DynamicSQL statement so that the dynamic SQL can be easily read and ...
0
votes
2answers
19 views

Migrating ssrs reports between different domains

I have an SSRS server in Domain A and i was asked to copy all the folders and reports from domain A to an SSRS server in domain B. The network team has opened the ports between the two servers in the ...
0
votes
1answer
28 views

SQL Server 2016 | MS Exam

According to Microsoft computation here: The maximum compute capacity usage by one instance is limited to the lesser of 4 sockets or 24 cores. To me a socket is the recepticle where a CPU is ...
0
votes
1answer
46 views

Is a separate execution plan generated for each dynamic SQL query that is executed within a stored procedure?

If I have a stored procedure and I re-write the logic so that some of the queries are executed as dynamic SQL, will those queries now get their own individual execution plans in addition to the stored ...
2
votes
0answers
37 views

Why is it faster to SELECT my logical CASE field into a new temp table as opposed to UPDATing the original temp table to SET the field?

I have a pretty large temp table (about 4.5 million rows) with no indexes, so it's stored in a heap. Let's call it #TempTable1. #TempTable1 has a BIT field called ToInclude which is set in a ...
1
vote
2answers
39 views

What can cause what appears to be missing data in a single table [duplicate]

I have a situation where it appears as if I've lost data in a single table. I have a .NET Core API, with a logging middleware. This middleware takes the entire request and response and stores in the ...
0
votes
0answers
22 views

Differential backup failing - Ola Hallengren Script

So I have installed Ola Hallengren's maintenance solution on our SQL 2016 servers. I have set the differential backups to take place every evening 7pm and full backup is scheduled for once a week. ...
0
votes
1answer
17 views

Limit TLS and Cipher Suites on SQL Server 2016-2019

I'm wanting to configure some SQL 2016+ servers to use only TLS 1.2+ and a subset of Cipher suite options (no ciphers considered weak or compromized). I'm having trouble tracking down how to ...
2
votes
2answers
46 views

Is an in-memory table faster to read from than a table cached in memory?

Is an in-memory (aka Hekaton) table faster to read from than a non-in-memory table that's already cached in the memory of a SQL server? I ask this because I have a case where I'm considering ...
-1
votes
0answers
17 views

Group/Transpose values based on Distinct value [duplicate]

I have this data set: I want my results to look like this: Basically, group data based on distinct values in column 1
0
votes
1answer
42 views

Installation failure with error code 2068119551

I have been making attempts at installing MS SQL Server 2016 SP2 on my Windows 10 physical machine. Installation keeps failing with the following exit code: 2068119551, saying that it "attempted to ...
1
vote
0answers
24 views

SQL Server 2017 Distributed AG's manual failover

We have configured SQL Server 2017 SQL Distributed Always-ON from DC1 to DC2. Can you please review below steps for manual failover from DC1 to DC2 and vice-versa. Please add steps If i missed any ...
1
vote
2answers
56 views

How to convert an integer that was the end result of a bitwise OR to a table of the individual integers that the bitwise OR was applied against?

How can I convert the final integer of a bitwise OR back to it's original set of integers that the bitwise OR operation was applied to? For example if I have the following set of bit values: {0, 1, 2,...
2
votes
1answer
45 views

Processor queue length but not very high CPU usage

I've a virtual server running SQL Server 2016 (4 vcores) where most of the time there is a processor queue length of 4 (sometimes it goes up to 15) but the CPU is using 25% on average. There are about ...
2
votes
2answers
73 views

how to add data compression to a primary key of a table?

We work with tables with over 300GB of size, a small percentage of them tables use sparse columns although that doesn't always agree with CPU utilization as this question shows. For the tables that ...
1
vote
3answers
65 views

When possible, are clustered indexes that are declared as unique more performant than clustered indexes that aren't declared as unique?

When applicable, will using the UNIQUE constraint for a clustered index help improve performance in any way under any scenario?
0
votes
1answer
38 views

What makes for a good index in this scenario?

I have a pretty big temp table (~5 million rows) that I need to update a few fields on. Here's an example update statement: UPDATE T SET CarModel = CASE WHEN CarModel = '350z' AND ...
0
votes
1answer
59 views

Invalid length parameter passed to the LEFT or SUBSTRING function when inserting data into a table that has computed columns

I have a process that needs to split a string of key-value pairs. To do so, I'm using a combination of a temp table that has computed columns to calculate the key and the value from each key-value ...
4
votes
3answers
82 views

SQL Server User Can't See All Logins

I have a SQL Server 2016 instance and I've just noticed that some user logins aren't listed for some users. For example, if I execute this query: select count(*) FROM sys.database_principals a where ...
0
votes
1answer
44 views

Trigger to prevent insertion of duplicate data

I have a table named dbo.Inventory in SQL Server 2016 like this: ProdCode UPCCode 1 11111 2 11112 3 11113 When adding a new row, I need to write a ...