We’re rewarding the question askers & reputations are being recalculated! Read more.

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
1
vote
1answer
34 views

Does querying the sys entities and DMVs lock the underlying tables and what happens when SQL Server tries to write to them?

If I do a vanilla SELECT * FROM sys.tables or SELECT * FROM sys.dm_exec_query_stats are those tables getting locked since I'm reading data from them? If so, what happens when SQL Server ties to write ...
0
votes
0answers
19 views

Availability Group Listener Loses Connectivity on Failover

I have 2 SQL server 2016 standard Servers. Production (SRV1). For reporting (SRV2) Both servers are nodes in WSFC. SRV1 replicates the production database (DB1) to SRV2 with the distributor ...
6
votes
3answers
260 views

What events still occur within a transaction that gets rolled back?

Is it true that all data modifications are undone when the transaction they are a part of gets rolled back? For example if a cursor that just executed a stored procedure 100 times that updated a ...
1
vote
0answers
23 views

Entries in suspect_pages but checkdb shows no error

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
35 views

Do table locks get released after a query finishes when WAITFOR is used after that query within the same batch and transaction?

If I have a query that reads from a table, if that query is proceeded with the WAITFOR keyword (within the same batch and transaction) will the table lock of the preceding query be held until the ...
-1
votes
1answer
22 views

Pulling the MINIMUM Date

I'm trying to join two tables together, that only share an "Account Number" as a joining field between them. One table (Table1) is static and contains just one record per account number, but the ...
6
votes
1answer
139 views

If a stored procedure is altered while it's currently being used in a cursor, does the cursor continue using the old query before it was altered?

If a stored procedure (or view) is currently in use within a long running cursor, and I alter that stored procedure, will the cursor continue using the old instance of the stored procedure until the ...
0
votes
3answers
65 views

How to SELECT a column based on a condition on the column's name?

I have the following table named MyTable: +------+--------+--------+--------+ | Code | U_Col1 | U_Col2 | U_Col3 | +------+--------+--------+--------+ | 123 | 1 | 2 | 3 | | 456 | ...
0
votes
1answer
36 views

Prevent compilation of a stored procedure to a linked server

I have a stored procedure, that queries a view on a linked server. This works fine on prod, but our development server has some firewall restrictions, that prevent the compilation. This is no problem ...
3
votes
3answers
236 views

Reducing table scans using group by and window functions

There is a code that I'm trying to improve that looks like this(simple example): SELECT DISTINCT a.col_a ,COALESCE(b1.col_c, b2.col_c, b3.col_c) FROM tab_a a LEFT JOIN tab_b b1 ...
0
votes
1answer
43 views

How do I force the delete of users from my database?

Background: I am making a clean .bak of our dev database for easy fresh spin-ups of the database. This database has about 200 test users that should be deleted to make it truly clean, but the problem ...
0
votes
0answers
21 views

Is there a way to figure out an executing query's plan handle by the query text?

Is there a way to determine the plan handle for the query plan of an executing query by that query's text? Perhaps in any of the DMVs? (I'm trying to find the query stats on an ad-hoc query.)
1
vote
1answer
35 views

Do a lot of error checks affect query performance and is it bad practice?

If I have a stored procedure with a handful of error checks that come before the queries are executed and throw exceptions as necessary, could this affect performance? Is it bad practice to do this? ...
0
votes
0answers
42 views

Database on secondary replica cannot join availability group [duplicate]

I am trying to add a database to an Availability Group Always On. I already have the other two DBs working fine on the same SQL Server 2016 Standard Edition Cluster (running on Windows 2016 Standard). ...
1
vote
1answer
33 views

Compatibility level of databases based on sql server version

We need to check for databases that are not in the correct compatibility level on a particular instance. For example, on a SQL Server 2016 instance, if there are databases that are not in ...
1
vote
3answers
28 views

Invoke-SQLCmd Date output format

I’m having a problem with a result set that I am getting from a stored procedure when running a PowerShell script (Using Invoke-SQLCmd) to get the results and send the output to a .csv file. The date ...
0
votes
0answers
43 views

How to determine high tempdb disk IO that goes down after restart

(for context, I'm a developer, not a DBA) Environment Azure Windows VM SQL Server 2016 Windows Server 2016 Datacenter 14GB RAM on machine, SQL set to use up to 8GB, but never gets over 2GB ...
0
votes
0answers
12 views

Need to break up a large file into smaller files to send via sp_sendsqlmail

I have created a job to send a "report" to someone (actually two reports). My issue is that the query is returning some 150K+ rows of data (no lobs or xml or anything, so not large rows), but ...
0
votes
0answers
33 views

What are all the privileges granted to the ddl_admin role? [closed]

If I wanted to GRANT permissions that mimic the ddl_admin role, which permissions would I need to GRANT?
2
votes
0answers
38 views

Missing log block for availability database in SQL Server 2016

I've recently begun receiving alerts like this one: Always On Availability Groups transport has detected a missing log block for availability database "myDb". LSN of last applied log block is (xxxx:...
1
vote
0answers
40 views

Sequence number for COMMITTED concurrent inserts

For example, I have a table create table Queue (OrderNum int, Data varchar(1000)) --int - may be a different type and many concurrent inserts into this table. Is there is a way to fill the OrderNum ...
0
votes
2answers
52 views

Read uncommited does not work in UPDATE WHERE NOT EXISTS

I'm not able to force SQL Server (2016) to respect values updated in the same statement. What I need to do is to update old values (column a in table #t in demo code below) with mapping table (#u) ...
-1
votes
1answer
30 views

data compression does not show when scripting index on ssms

when I right click on SSMS and choose to script the create index: I get this script, without the data_compression: USE [APCore] GO /****** Object: Index [i_agencyId] Script Date: 07/11/2019 12:...
0
votes
3answers
47 views

how to automate DB restore to another server?

The plan is to have 1 backup set that will contain full and transactional log backup. (Server1) There is already a scheduled job that will append any transactional log to the file after 30 minutes. (...
0
votes
1answer
43 views

Could not complete cursor operation because the set options have changed since the cursor was declared - SQL Server

I get this error when I execute this cursor. Msg 16958, Level 16, State 3, Line 56 Could not complete cursor operation because the set options have changed since the cursor was declared. But ...
1
vote
1answer
44 views

How to design a database where parent table has default values

I have following tables (Its not actually accessories but I am using it as an analogy), Accessories {AccessoriesId, Name, other accessory related attributes...} ClientAccessories (ClientAccessoryId, ...
0
votes
0answers
26 views

SQL/Powershell job failing

Need more help from you brilliant folks. I am trying to import a CSV file to a SQL table with this code: $SQLServer = "MYSQLINSTANCE" $SQLDatabase = "DBATools" $SQLString = "Server=$SQLServer;...
6
votes
1answer
90 views

When are non-parameterized, non-trivial, adhoc query plans reused

I'm currently investigating an application that seems to generate 99% adhoc query plans against the database it is querying. I can verify this by running the following statement to retrieve a summary ...
2
votes
0answers
27 views

Will “Stack Signature for the dump…” appear in the SQL Server error log every time there is a memory dump?

I am currently monitoring sql server memory dumps as they appear in the error log. All I really need to know is if this occurred, I can find out the details another way. Right now my logic is that if ...
5
votes
1answer
1k views

Is the NOLOCK hint completely safe to use on a table that's guaranteed not to change?

If I have a table where the data is guaranteed to never change, is it completely safe to add the NOLOCK hint to my SELECT queries against that table? If I have a table where some of the rows will be ...
7
votes
1answer
468 views

Is there a reliable way to determine how long it took the optimizer to generate a query plan?

I'm not sure where I'd start, but is there a way to see how long it took the optimizer to generate a query plan for a query? Is it stored in any of the DMVs or something that's part of one of the ...
1
vote
0answers
26 views

SSMS Connect to remote SQL instance with Windows Authentication

I have a remote SQL instance (Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64) I can connect to the instance fine locally on the box through SSMS using both Windows and SQL ...
2
votes
2answers
48 views

charindex thrown off by extended characters

I've got a column with a filenames stored in a nvarchar(255). I'm trying to parse out the file extension using reverse( left( reverse(filename), charindex('.', reverse(filename) ) -1 ) ) This works ...
9
votes
1answer
143 views

Is it possible to re-use a query plan from the plan cache from one database in another database?

For example, if I hit the sys dynamic views to select a specific query plan, am I able to insert that query plan into another database's plan cache, that runs the same exact query? (I know the query ...
1
vote
0answers
33 views

If a Login is not granted the db_ddladmin role on a database, but is individually granted CREATE VIEW on it, will they still be denied?

Since SQL Server permissions work as "deny first", I was wondering if that applies downward to individual permissions that are granted, if the parent permission isn't granted e.g. db_ddladmin is not ...
6
votes
2answers
75 views

How to make sure change_tracking statistics stays updated

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 ...
2
votes
1answer
65 views

Why can't I create a new schema with the owner set to dbo?

I created a test account for checking permissions against. It currently has the db_datareader, db_datawriter, and db_ddladmin roles for the Northwind database. But when it tries to create a new schema ...
3
votes
1answer
40 views

SQL query with Like operator with Leading Wildcards

I have a query as follows: select top (30000) * from table1 where licenseplate like '%as43bv%' I have an index on licenseplate but the query will not hit the index due to leading wildcard. Since ...
2
votes
1answer
34 views

Indexed view referencing objects on two different schemas

When I try to create/alter a view to create an index like this CREATE UNIQUE CLUSTERED INDEX IDX_vSalPopulation ON sfdc.vSalPopulation (ID); I get the following error message Msg 1938, Level ...
4
votes
2answers
601 views

Is there an operator or an easy way to match an expression one or more times with the LIKE operator in SQL?

For example if I run the following query, the expression in the WHERE clause will only match on the first letter before a number but is there a way to match on one or more letters first (e.g. in the ...
2
votes
2answers
45 views

what happens to user connections when an AG fails over?

I'm not sure quite how to word this, but I am looking for anything a DBA can do (things outside of changing code in the application) to reduce the impact to users when an AG fails over. We have a 2 ...
0
votes
2answers
68 views

all the dependants of a stored procedure or function recursively - but shown only once

I often work with third party data professionals and they ask me to replicate whatever table(s) such and such stored procedure or function or view touches within one or more databases. so I have ...
2
votes
2answers
82 views

The difference between filtering a specific column in 'On Clause ' of a join statement and 'Where clause'

I have two tables with these structures: Table1(Date ,Code ,Name) Table2(Date ,Code) I need to filter a specific date for both tables. For instance I need data only for '10/28/2019' from each table....
5
votes
3answers
779 views

The logical order of execution plan of the SQL query with more than one join

I know the logical order of execution of the SQL query which is: FROM ON JOIN WHERE GROUP BY WITH CUBE or WITH ROLLUP HAVING SELECT DISTINCT ORDER BY TOP What will happen if there are more than one ...
0
votes
1answer
16 views

Set MultiSubnetfalilver=Yes to linked servers in standalone SQL Server 2016 implications

I am using 1 node SQL Server 2016, having linked servers using MSOLEDBSQL provider and Provider string "MultiSubnetFailover=Yes". Are there any implications/negative impacts, if i use "...
0
votes
0answers
54 views

Must declare the scalar variable in cursor SQL SERVER

I have this problem with this code. I don't know why, because before set values.I declare a variable. Moreover, How i can execute every results that cursor give Error message: Msg 137, Level 15, ...
1
vote
1answer
18 views

Microsoft SQL 2016 database for Malwarebytes Endpoint Protection

I don't know if this is the correct forum, but hopefully someone can help. I am a newly promoted entry level Systems Administrator and the first project I was tasked with was to spin up a new server ...
1
vote
2answers
64 views

SSMS login by external developers

This is my first question so please bear with me ! We have an external company whose developers need to login into an instance on our on-premise SQL Server 2016. They have AD logins, and these are ...
0
votes
1answer
36 views

quotename(object_schema_name(@objectId)) returns null when called as a synonym

I have a central utility database in each SQL Server instance, called Utility. In it, I have a bunch of centralized functions and procedures that I call from other databases. A function I have in it ...
2
votes
1answer
46 views

what could be preventing my transaction log autogrowth to be set to UNLIMITED?

I have been trying to set the autogrowth of one of my databases to unlimited but despite whatever I do it remains the same. First thing I have checked if something is holding any locks to my database:...