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
0answers
13 views

Is “SQL Server Auditing” what I'm looking for?

Man... this feels like a broad question, so apologies in advance. The word "auditing" seems to have some depth in the DBA world, and I may not be asking the right question when I google. I'm trying ...
0
votes
2answers
31 views

How to automate creating credentials in a secure way

We have created an automated process to stand up a new SQL Server using TFS and powershell. As part of a new server build, we deploy a database and a set of jobs for dba use. As part of the install ...
2
votes
2answers
41 views

PowerShell command 'New-Item' fails when invoked through SQL Server Agent job

I have the following PowerShell script to create a folder at a share location. The share has full control permissions for everyone. I have granted all permissions for testing. The problem is the ...
0
votes
1answer
18 views

Error during sp_refreshsqlmodule after altering table type

I am trying to add a column to a user-defined table type. I have done some research and here is what I have now: -- First, rename existing table type to something else EXEC sp_rename '...
31
votes
2answers
3k views

Is it generally faster to select into a temp table than selecting into an actual table?

I thought I once read somewhere that writing to tempdb is faster than an actual table not in tempdb. Is this true in any capacity? I thought I recall it saying something special about tempdb and ...
2
votes
2answers
36 views

Virtualized SQL Server hits 100% CPU and 0 Active Memory

We recently experienced an odd crash with one of our server. We know what caused it, but we don't know why SQL Server responded the way it did. SQL Server 2016 Here is what happened: Friday: ...
1
vote
1answer
24 views

How can I see the plan for ONE statement in a stored procedure?

I've got some SQL which I've run several times a day in development, generally getting execution time of 3-8 minutes. Now I've wrapped it in a stored procedure (with no parameters), the performance ...
3
votes
1answer
94 views

Change tracking causing latch contention

I am using Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64) Jun 15 2019 23:15:58 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 ...
0
votes
1answer
27 views

How to Grant A User Under A Sql Domain Account To Insert and Update Records

I have a sql domain account in sql server like DOMAIN\GroupName. There are many user accounts under that account like DOMAIN\user.name. I want to give insert and update permission only to a user under ...
1
vote
0answers
15 views

How to select specific databases in a backup retention policy maintenance plan cleanup task?

I have full backups of three different databases. Right now all of their backups follow the same retention policy, but I need to setup the retention to a different amount of time for each database. ...
0
votes
1answer
65 views

Sql Server 2016 uses low RAM [closed]

I have an SQL server 2016, version: (SP2-CU4-OD) (KB4482972) - 13.0.5239.0 (X64) The server is a Win 2016, with 64 GB RAM. I have configured max memory for SQL to 54 GB, but it uses less than ...
0
votes
1answer
61 views

MSSQL 2016 - Restore SSISDB from MSSQL 2012

On "MSSQL 2016 (SP2-CU11) (KB4527378)" I created empty SSISDB. After that I restored SSISDB from "MSSQL 2012 (SP4) (KB4018073)" to "MSSQL 2016 (SP2-CU11) (KB4527378)" On DB I set owner to sa and ...
0
votes
0answers
22 views

SQL Server: Replicating a Table to a Subscriber back to the Publisher

We have a Transactional Replication that replicates all tables to a subscriber. Without going into detail, we've been facing performance issues on the Publisher database whenever there are ...
2
votes
2answers
76 views

How to make DDL changes to large tables and cause minimum server and object contention?

I work with a lot of large tables (billions to tens of billions of records in each) in a database I recently inherited. I see a few clear DDL changes that would benefit the use-cases of the database ...
-3
votes
0answers
47 views

SQL Server 2016 sp_blitzfirst results - anything sticks out as concern for performance issues?

Performance issues random, not able to pin point issues using my normal baselines. Maybe I am overlooking something in the results in the screenshots.
1
vote
1answer
34 views

UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' in Agent job

In brief, I added a computed column to a table and then Agent jobs began failing and reporting the error: UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'...
0
votes
1answer
38 views

SQL Server Log Shipping for Server Migration - What's the final step?

Working with a server running SQL Server that was the log shipping target for two other servers as part of server consolidation/migration. In other words, server B was log shipping to server A and ...
0
votes
1answer
49 views

Should a filtered index theoretically perform faster in this example?

I have a SuperheroMovies table which has all superhero movies, and has a column called "UniverseName" which has either the values "Marvel", "DC", "Capcom", "Unknown". It also has columns called "...
0
votes
1answer
51 views

Can't help the engine to choose the correct execution plan

The stuff are pretty complex to share the original code (a lot of routines, a lot of tables), so I will try to summarize. Environment: SQL Server 2016 standard edition Objects: wide table with ...
0
votes
1answer
53 views

Database Engine Tuning Advisor drops index automatically on SQL Server 2016? [closed]

we are using SQL Server 2016 and currently we are facing some performance issues. Solarwinds tells us, that there is sometimes much wait time caused by a Lock "LCK_M_SCH_M". I was now able to check ...
1
vote
2answers
39 views

How to sort values starting with a letter followed up by a number?

I have a series of values, possibilities are : A1, A2, B1, B2, A100, A101, B100, B200, 1, 2, 3, 4 and so on. How can I sort it so that first the letter is sorted, and after that the number with the ...
1
vote
1answer
40 views

Unable To Restore Database From .BAK File

I am trying to restore my .bak file but I keep getting the error Restore of database 'DataWarehouse' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)System.Data.SqlClient.SqlError: ...
1
vote
1answer
12 views

Database Mirroring - Will forcing the service only lose non-committed data?

I have a database mirroring session in high safety mode between Server1 (Primary) and Server2 (Secondary) According to this, synchronous data mirroring means every transaction committed on the ...
0
votes
1answer
39 views

How to upgrade SQL Server 2016 and 2017 Standard to 2017 Enterprise edition

We have one SQL Server 2016 Standard instance, and two SQL Server 2017 Standard instances installed on the same server. Now my management wants to install SQL Server 2017 Enterprise edition and ...
0
votes
0answers
17 views

PowerShell Subsystem max_worker_threads reverts back to default after applying cumulative update for SQL Server 2016

We change the PowerShell subsystem max_worker threads to 10, but after we install the latest cumulative update, it reverts back to 2. Is there a way to keep that from happening? SELECT s....
2
votes
1answer
39 views

Cannot create a row of size 11842 which is greater than the allowable maximum row size of 8060

The suspect table has 299 nvarchar(max) columns, 1 integer column and 1 nvarchar(255) column. Please do not ask me why, this is 3rd party vendor database. Anyway, according to my math, in the worst ...
1
vote
2answers
77 views

how to list all features installed in a sql server instance?

There are other Installation related questions with similar details here and here. I could not find anything related to listing the installed features in sql server 2016 though. I can do it manually ...
0
votes
0answers
19 views

SQL Server Audit catching SSIS metadata check

I'm wondering if what I'm experiencing is intended behavior or a bug with SQL Server Audits. I'm leaning towards bug. I set up an audit to monitor updates, inserts, & deletes to two tables. I ...
2
votes
1answer
44 views

The SELECT permission was denied - View querying data from a different database

I created a view SchemaX.VW_VIEW on a DatabaseB. This view is querying data from another database DatabaseA of the same server. I have granted the SELECT permission on VW_VIEW to a user. However when ...
2
votes
1answer
2k views

Query runs in parallel but it is showing as blocked by itself

I have a query which runs in parallel with a MAXDOP of 8. When i looked at sp_who2 i see that the same session ID is being repeated several times(>8) with different connection id. I used the below ...
1
vote
1answer
32 views

Subtracting With GetDate()

I am trying to convert a postgresql query to ms sql query. This is what I have in postgresql round(cast((Current_Date - emp.hiredate) As Decimal)/365,1) now I thought it would be a simple ...
0
votes
2answers
41 views

Performing Sum in Case Statement

I am trying to get a sum inside a case statement, but I am getting the error Cannot perform an aggregate function on an expression containing an aggregate or subquery This is my query syntax ...
1
vote
1answer
231 views

Job waiting on CXCONSUMER

We have got a procedure on SQL Server agent jobs and this job started to hang on transaction for many hours with wait stats CXCONSUMER and never complete. The problem is after we noticed that it was ...
2
votes
1answer
54 views

Are stored procedure query texts stored in any tables?

I don't readily see anything in the sys schema objects, other than the DM_EXEC_SQL_TEXT function that requires a plan handle, but the procedures I'm trying to get their query texts from aren't ...
-1
votes
1answer
20 views

Using Sum With Query

How can I get the Sum of data from a secondary table w/o using a join? Select emp.ID, emp.fname, emp.lname, emp.address1, emp.address2, emp.city, emp.state, emp.zip, emp.phone, Sales = SUM(Select ...
0
votes
0answers
16 views

Is there a way for the temporal tables to log to history table only when any of the value changes? [duplicate]

I want to Audit/insert the record to history table only when any of the value is modified in temporal table. Thanks!
1
vote
2answers
74 views

Is it normal to UPDATE STATISTICS this frequently? [duplicate]

I have a stored procedure that processes data from a source table and is executed many times (about 20,000 executions) throughout the day. I believe it's getting choked up sometimes, due to parameter ...
1
vote
1answer
49 views

Storing linked server in stored procedure variable

I have following setup: Server 1: CGENMONOSAMP01 Server 2: CGENMONOSAMP02 I have Server 2 linked to Server 1 and link works, I can do queries like this on Server 1: SELECT * FROM [CGENMONOSAMP02]....
0
votes
0answers
11 views

How to Script a Database from A local power Bi Data model to SSAS

After connecting SSAS to a local power bi model, I am attempting to replicate the model in the server version of SSAS using Script Database as> Script To > Create to, but the script generated is ...
2
votes
2answers
46 views

Can the FORCESEEK query hint be used on tables being queried against on a linked server? (I.e. as part of a distributed query)

I have a query that uses the FORCESEEK hint and runs fine on the server where the database I'm querying lives. If I try to run the same exact query remotely from a different server (targeting the ...
0
votes
1answer
26 views

Can we generate SQL Server MDS Model from an existing database schema?

I have googled this quite a bit and am surprised to not find an answer. I'm considering using SQL Server MDS to manage our canonical schema, but I certainly don't want to key in my entire database ...
0
votes
1answer
57 views

What causes sql server to generate a new plan for a stored procedure?

We've ran into an issue in production a couple of times now where it looks like a stored procedure gets a new, bad plan and starts causing havoc. Unfortunately, we weren't able to capture the plan ...
6
votes
2answers
116 views

What's the best way to order a list of tables based on their foreign key constraints

I'm working on an etl task that takes tables from one schema (with no constraints) and inserts them into the same tables (plus constraints). I don't want to type out a bazillion insert statements, so ...
0
votes
2answers
65 views

Deny a Sql Server user from a specific hostname

Is it possible to block a sql server user not to execute DDL and DML operations from a specific hostname (client)? I have a CRM web application. That app connects to my database with a sql server ...
8
votes
1answer
136 views

Permissions required for row counts to show up on Object Explorer Details

I'm having a weird security issue. I have a user that's using SSMS 18.2 on a SQL 2016 server. They are a member of db_datareader but when they pull up the the Row Count column in Object Explorer ...
1
vote
1answer
29 views

SSRS connection string Encryption

i am working on porject to encrypt the connection between ssrs reports and sql server. we have already installed ssl certificate on sql server. can i know how to encrypt the connection string in SSRS ...
0
votes
1answer
62 views

Differential backup same size as of Full Backup

I have a SQL Server database of almost 2 TB size, it has tables with image datatypes. Now the size of differential backups are same as that of full backup. Does image datatype makes any difference to ...
0
votes
0answers
21 views

T-Replication Periodically Falls Behind

We have two SQL Server 2016 boxes. One uses transaction replication to the other. There is also a distribution server. The one subscriber has been periodically falling behind in replication and the ...
0
votes
1answer
27 views

Conditionally track CDC on a table in SQL Server 2016

I have CDC working on many tables in SQL Server 2016 without any issues. I now have a requirement to capture data changes (CDC) on a table when a specific criteria is met. Is it possible to capture ...
0
votes
1answer
27 views

Differential Backup of A Table In Sql Server

I will transfer the datas of a two different table to a database in reporting server Daily but i don't want to do it manually and also don't want to transfer all the datas to that databases everyday. ...