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
2
votes
2answers
124 views

Find specific query in Query Store

I have enabled Query Store on a database. I have a specific query which I want to track. I have a lot of details about the query from sp_BlitzCache (like SQL Text, SQL Handle, SQL Hash, Plan Cache ...
0
votes
0answers
16 views

SQL Server Group Managed Service account

Is it the best practice to use same gMSA account for both SQL Server and SQL Agent services? or should I use separate gMSA accounts? I couldn't find any Microsoft documentation on this specifically. ...
0
votes
0answers
122 views

Generic “An Error Has Occurred” When Editing SSRS Subscription - No Parameters Appearing

I'm relatively new to SSRS Reporting. I'm attempting to manage a data-driven subscription for a two-part report on a SQL Server 2016 Developer instance that is acting as a dedicated SSRS server. At ...
3
votes
1answer
50 views

Reporting table needs to get refreshed without killing user's read connection to it, any ideas?

I'm investigating a solution that will allow a reporting table to be refreshed that can potentially have users reading from it at a time that it needs to be refreshed. Has anyone implemented a way of ...
0
votes
1answer
27 views

Is it possible to restore a database with only database roles membership?

I have an existing database, that i want to restore from a backup by overriding the existing database. at the moment I have db_owner db_backupoperator roles I thought that would be enough to ...
0
votes
2answers
40 views

SQL Server AlwaysOn cluster having issues seeding large availability database creating strange behavior

I set up a 2-node SQL Server 2016 AlwaysOn Availability Group cluster (1 primary, 1 secondary). The underlying hardware is fairly powerful; 4 CPUs, 16 GB of RAM and local-attached SSD disk. Then I ...
-3
votes
1answer
27 views

Reformatting Date with Pivot or Unpivot [closed]

I'm struggling trying to covert the format of a table with the following fields: Customer|Fiscal Year| September Amount| October Amount|November Amount|... to: Customer|Fiscal Year|Month|Amount I'...
0
votes
1answer
19 views

Is there an easy way to delete all records in multiple tables (but not every table)?

So I "fixed" an issue the other day, which has caused a snowball of new issues (imagine that). I have a large group of tables that serve as Temp tables for many import/export processes that flow to ...
0
votes
1answer
58 views

SQL Server - Space Available

I can't find exact answer on my question. When i right click on any database in SSMS, i see "Space available parameter". Is this free space in mdf file only or it include transaction log file too (...
0
votes
1answer
50 views

Optimise XOR search on int list

I have a large table (TableA) with approx. 120 mio. datasets. This table contains a single primary key column (type: int). Another large table (TableB) contains the same ID column with some other ...
-1
votes
1answer
58 views

Obtain the first business weekday of the NEXT month

I'm trying to derive a date field that represents the first business day (in this case, it means the first weekday - also excluding holidays) of the next month. I was able to derive the first day of ...
0
votes
0answers
31 views

Cannot Connect to New Server

Disclaimer, I'm not a dba. I have been handed the job and have to do it. For day to day operations, I can handle the job, but now they want to upgrade the server and I'm stuck on this one issue. I ...
1
vote
1answer
46 views

With SSMS on my local machine, is it possible to login to a remote SQL server on my VPN network via my Active Directory credentials?

I'm trying to login to a SQL server on my work network with a VPN connection to the network and by using my AD credentials. When I'm directly on my office machine, I use "Windows Authentication" as ...
1
vote
1answer
39 views

Select into query, insert fails, but the table is created

I am using SQL Server 2016 and I tried to the following query. SELECT CONVERT(BIGINT, 'A') col1 INTO #tmp This query is obviously in error. Because it does not convert. However, the temporary table (...
0
votes
1answer
16 views

Adding a field to a table (but in another database)

I understand that this is a strange question but I'll try to explain it the best I can. We have a database (I'll call it MachineDB) on our MSSQL Server 2016 that gets many datasets each day from a ...
3
votes
2answers
281 views

Why is the result of a totally deterministic and all-encompassing “case” in select query in view nullable?

I have a table, with Id as the primary key. create table Anything ( Id bigint not null primary key identity(1, 1) ) When I look at this table in Object Explorer, I see this image of course: As ...
1
vote
0answers
62 views

How can I make this query faster without adding indices?

I have a read-only database, from which I have to report on all our Wuhan virus cases. The execution plan and query can be found here. I can't share the data, since it's health records, but this ...
0
votes
0answers
21 views

How to migrate the SQL Server instance and system databases to a different drive (mapped to a different letter) on the same server? [duplicate]

I'm tasked with moving our SQL Server instance and system databases from the existing drive it's on to a new drive on the same server. I know I can stop the SQL Server service, literally move the ...
0
votes
0answers
18 views

How to get latest record of partition when Field1 & Field2 is not null or get latest record where one is null and 2nd latest where same field isn't?

Hard question to verbalize in the title with character constraint, sorry. I have Table1 with the fields PartitionField, ImportantField1, ImportantField2, DateField. ImportantField1 or ImportantField2 ...
0
votes
0answers
26 views

Match exact word/string in begining, in between & end in string

Here I need to match exact word but I am getting all results I used '%' + columnname + '%' in wildcard. I am looking for exact word match doesn't matter it is beginning, in between and end of string....
0
votes
1answer
39 views

Does DROPing a table create a bunch of transactions in the transaction log for all the records being purged?

If I have a table with billions of records, is it faster and does it create less data in the transaction log to TRUNCATE it first and then DROP it or can I just DROP it all the same?
2
votes
1answer
101 views

Adding COMPRESSION_DELAY to an existing COLUMNSTORE INDEX

When I create an COLUMNSTORE index I can specify COMPRESSION_DELAY = x [minutes] in the CREATE-Statement. It delays the movement of the rows in an closed segment from the delta store to the compressed ...
16
votes
2answers
3k views

SQL estimates are way off on DELETE statement with Triggers on huge tables

I'm working with Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 ...
1
vote
1answer
52 views

If I kill a looping INSERT query, will it rollback all inserts since the loop started or only the last iteration?

Do all inserts or only the current one rollback if the following loop query was killed in the middle of looping? DECLARE @LoopId INT = 1; WHILE (@LoopId < 100) BEGIN INSERT INTO Table2 ...
0
votes
0answers
13 views

Facing sql principles issue in report server and corporate web portal

i am facing issue in report server from last few days, actually i created one package and i deployed it into sql server then i created job for it, i called that job in ssrs report, report is working ...
1
vote
1answer
59 views

out of all columns in a table, how can I find the one datatype that is cheapest and I could convert all other columns datatypes into?

I sometimes have to unpivot tables, that means, converting all columns into rows. Generally that becomes a table with 2 columns (just to simplify things) - the first is the column name and the second ...
0
votes
3answers
121 views

When is it ok to use an Index Hint on a query in production code?

I've recently been working with decently sized tables (between 100s of million of rows to 10s of billions), and have ran into a few cases where I was forced into a corner to use query hints, which I ...
0
votes
2answers
147 views

How to debug a growing Redo Queue Size in the Availability Group Dashboard?

The Redo Queue Size has been growing pretty big over the last couple of hours on my secondary replica's server (SQL16). There's nothing long running or heavy running that I've found over the last hour ...
0
votes
0answers
53 views

Disabling TLS 1 & 1.1 kills Biztalk 2016 fp3 with MSDTC on SQL 2016

We are mandated to harden the TLS/SSL and use TLS 1.2. Upgrading Biztalk to 2016 fp3 should default to TLS 1.2 and it does - for outging traffic. The problem is that I cannot get MSDTC to call back ...
0
votes
1answer
96 views

How to insert into temp table on Server A results from dynamic SQL open query stored proc call to remote server B via Remote Query showplan operator?

I have to call a stored procedure on server B from server A using open query via dynamic SQL, and insert the results into a temp table created on server A. I noticed in the execution plan, it's using ...
0
votes
1answer
96 views

SQL How to get multiple values into one column (MS SQL)

I have the following Tables: CREATE TABLE #table1 (Batchnumber INT NOT NULL, BOXID TINYINT NOT NULL, ErrorID TINYINT NULL, Weight INT); CREATE TABLE #table2 ...
0
votes
0answers
29 views

Why is the Page Life Expectancy (PLE) not increasing?

We have monitored the PLE performance counter for several SQL Server 2016 instances for over a year, and have had alerts which triggered when this value persistently dropped to zero or froze. ...
0
votes
0answers
98 views

Microsoft Data Migration Assistant Failing

I am using the Microsoft DMA to migrate data from a Microsoft SQL Server 2012 instance into a Microsoft SQL Server 2017 instance. I get all the way to the final step of selecting logins, start the ...
2
votes
1answer
103 views

Cursoring over sys.databases skips databases

I know this question has been asked several times and I know the solution, but I am trying to understand the underlying cause of the problem: I have the following code to perform database backups. ...
0
votes
0answers
32 views

Maintenance Tasks causes CPU to peg 100% utilization

I do not believe the maintenance tasks themselves to be the primary issue here, however CheckDB and index rebuilds cause the server to become unresponsive w/ 100% cpu utilization. It is on one ...
0
votes
2answers
459 views

Execute sp_update_jobstep to change SQL Agent job step type

As I was going to deploy a change to our Ola Hallengren indexing scripts, I noticed on some SQL Servers we have indexing job running as an "Operating System (CmdExec)" step instead of being called as ...
0
votes
1answer
34 views

How to get the settings associated with a stored procedure in relation to filtered indexes?

I quite like using filtered indexes in the proper context. I have added the following filtered index: USE [COLA]; CREATE UNIQUE NONCLUSTERED INDEX F_InterviewReport ON [dbo].[...
0
votes
2answers
35 views

Insert Records That Do Not Exist

We are upgrading from one database to another. Both databases have the same tables, and for examples sake let's say we have dbOld and dbNew. I need a way of for each table on dbNew check the ...
0
votes
0answers
15 views

Error Creating File DSN for MS SQL on AWS

I have a contained MS SQL database on Amazon AWS and it has contained users. Using SSMS, I can connect to the contained database by using the contained user's credentials. I'm trying to create a ...
1
vote
1answer
54 views

How to know what database user I'm using in SQL Server?

I'm learning SQL with SQL Server and I've see how to create users, I've created without login: CREATE USER Luis_Gerente WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo CREATE USER Mario_Supervisor WITHOUT ...
2
votes
3answers
108 views

Is it possible to track the progress of when I drop a the primary key clustered index on a table?

I have a large primary key clustered index I need to drop. I believe dropping it will also trigger index rebuilds on the nonclustered indexes (which are also pretty big). Is there a way to determine ...
0
votes
0answers
32 views

How can I tell which queries and nonclustered indexes are doing bookmark lookups on my primary key for a specific table?

Is it possible to get the queries and the names of the nonclustered indexes in my database which are doing bookmark lookups / key lookups in the execution plans? Would I have to go through the plan ...
-1
votes
1answer
42 views

How to configure DB server space alert step by step [closed]

Could you please help me on the question. Here is the requirement. I have one server and there is 2 drives are available (C and D). Due to mssql Log file growth E drive is always getting filled and ...
1
vote
1answer
52 views

Set FORCESEEK for only one of hinted indexes

Is there a way to use a TABLE HINT with two indexes on the same table and set the FORCESEEK for only one of them? The following statement causes an error because the first index is columnstore: ...
0
votes
1answer
22 views

Are there any security implications with enabling “Data Access” on a sever?

I'm trying to run an OPENQUERY statement within a local server but am getting the following error: Server 'MyLocalServer' is not configured for DATA ACCESS. If I set MyLocalServer's Data Access ...
9
votes
1answer
309 views

MS SQL Express 2016 on Amazon AWS: I Can Take Database Offline but Can't Bring It Online

I can take databases offline (via GUI) but can't bring them back online. The server details are as follow: RDBMS: MS SQL 2016 Express Host: Amazon AWS/RDS Free Tier Details/History of the Problem A ...
1
vote
1answer
69 views

Is it normal for it to take up to a minute for an execution plan to be generated (even an estimated one) on simple queries?

Once in a while (but uncommonly) my SQL server will take what seems like an odd amount of time to generate an execution plan. It just took 37 seconds to generate an estimated execution plan for the ...
1
vote
1answer
60 views

Is it possible to force a query to ignore the server option “remote proc transaction promotion” so it doesn't promote remote proc transaction?

I have a specific query that executes a stored procedure on a remote linked server and inserts the results into a local temp table. I don't want it to promote a remote procedure transaction but I can'...
0
votes
1answer
50 views

Would forced parametarization help?

We have a process that creates a new sequence using dynamic sql. We then use dynamic sql to get the next value from the sequence. We have a lot of these dynamic sequences, but don't call any ...
2
votes
2answers
149 views

:!!echo command not working from sqlcmd

I have a sql script file which is executed from a batch file and is supposed to execute some sql scripts and write errors and output to files. Here is an example of how the script file looks like: :...

1 2 3
4
5
46