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
1answer
30 views

SSIS: Extract SQL Data and Split across Flat files based on row count

I have created a simple SSIS package that queries a table and extracts data to a flat CSV file. In production this extract could be millions of rows and I want to split the flat file destinations into ...
-1
votes
0answers
22 views

How to add a SUBTREE COST collumn on this query?

I could create mixing a lot of other queries, a query that will list the most costly queries running on a server. SELECT TOP 50 SUBSTRING(qt.TEXT,(qs.statement_start_offset/2)+1, ((CASE ...
0
votes
0answers
36 views

Restore sql server DB between Cumulative updates

Respected Sir, Please I want to know - Can we restore one database on version- 13.0.5888.11(5001092 Cumulative update 17 (CU17) for SQL Server 2016 Service Pack 2 Latest CU) To 13.0.5103.6 (4583460 ...
0
votes
0answers
28 views

SQL Server Replication - accidentally unselected table from snapshot: how do I add it back in again?

Using SQL Server merge replication, one of the tables was accidentally unselected (check box unticked). How do I add it back in to the snapshot again? It was unfortunately a large table with plenty ...
-3
votes
1answer
45 views

Expose SQL server externally [closed]

I currently have a sql server database and I want to be able to expose this over the internet so that someone external to my network can access the tables/data. How best can I achieve this and what ...
0
votes
2answers
35 views

changing SSMS settings via Powershell

in order to enable the dark theme to SSMS (not available on the picture below as you see): following this webpage: SQL SERVER Management Studio 18 - Enable Dark Theme - SQL Authority with Pinal Dave ...
1
vote
1answer
73 views

Ola hallengren SQL Server Index and Statistics Maintenance solution

We are using the Ola hallengren SQL Server Index and Statistics Maintenance solution from the past 6 months in our production system. Script is only used for Update Statistics not Index maintenance. ...
0
votes
1answer
58 views

How to restrict Users to see only their databases where has access? [duplicate]

I need to restrict the list of database for our users in a SQL Instance In order to move forward this I revoked the view any database permission to the user desired. But I figured out that only ...
5
votes
2answers
515 views

Can a server wide setting of 'Max Degree of Parallelism' = 1 cause Brent Ozar's sp_BlitzCache to flag execution plan as 'forced-serialization'?

I am using Brent Ozar's sp_BlitzCache store procedure and I'm attempting to nail down why it is reporting: "Something in your plan is forcing a serial query. Further investigation is needed if ...
1
vote
1answer
29 views

Automatically expiring records in a temporal table?

I have a need to store some results that are only valid for a rolling 5 day window. An application will insert or update records and the results should be viewable for the 5 day window. However, there ...
1
vote
1answer
50 views

Distribute Guests over Rooms Evenly

I have a table of packages: create table Package ( PackageID int, Guests int, Rooms int ); insert into Package(PackageId, Guests, Rooms) values (1, 1, 1), (2, 2, 1), (3, 2, 2), (5, 3, 2),...
0
votes
1answer
76 views

Optimizer Not using Non Clustered Column Store Index for Update / Delete Statements

My Table tbl_FieldeData_ColStore Data Example: /NonClustred Column Store Index on Table/ CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI ON tbl_FieldeData_ColStore ( PDID,FieldID ) /Below Select was ...
0
votes
1answer
30 views

How to Ensure all Log backups are restored on secondary server in log shipping

I have some concerns regarding the restore job on secondary server in log shipping in Microsoft SQL Server 2016 for below scenario : I have two servers, primary and secondary. The backup, copy and ...
3
votes
2answers
49 views

Question Regarding ISDATE Function and DATENAME Functions

We have had an issue this morning with a report failing due to the following error: "Conversion failed when converting date and/or time from character string" The issue is that in the ...
-1
votes
1answer
61 views

SQL service stop date

is there a Powershell command or through windows to know since when my SQL service is stopped Thanks
0
votes
1answer
28 views

Help in querying hash PK and FK

I was trying to get the name of the PK and FK. However, this is the output that I get: PK: PK__adm_lib___3214EC067E4F7CCA FK: FK__adm_lib_S__fk_Co__7093AB15
1
vote
0answers
21 views

Can't delete secondary mirror DB stuck in (Mirror, Disconnected / In Recovery) [duplicate]

i'm facing an issue with a corrupted secondary database on SQL Server 2016. After a massive outage we were able to restore most of the databases immediately without issues. Two databases were in the &...
4
votes
3answers
236 views

How can we restrict tempdb usage for specific users or queries on SQL Server?

In our SQL Server production environment some users run ad hoc queries that run huge data extracts into tempdb and fills it up, causing issues on a production server. And as these reports/queries run ...
0
votes
0answers
45 views

Super complex Schema will not migrate to PaaS

Using a Dedicated 2016 datacentre SQL Server 2 vcpus, 14 GiB memory for testing. We have currently found a migration issue with the database Migration SQL 2016 to Azure PaaS. The transfer of schema ...
0
votes
1answer
25 views

SQL job sending email even if not configured to send notifications

I am using this version of SQL Server. Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on ...
0
votes
0answers
16 views

SQL Mirroring Replace Certificates

I have inherited two SQL 2016 servers with two mirrored databases. They have been built with certificates as they are in a workgroup, the certs expire in six months. I am trying to understand the ...
2
votes
1answer
67 views

When to use BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION in SQL Server?

For this question lets assume there are two servers, ServerA and ServerB, and there is a linked server setup on ServerA to ServerB. Let's also assume I have REMOTE_PROC_TRANSACTIONS set to ON for both ...
0
votes
2answers
61 views

Solution for syncing a database between two server and secondary can be used for all DML operation

I need a solution to synchronize a database (SQL Server 2016 Enterprise) between two servers, and I would be able to connect my secondary application to the secondary database which is getting ...
1
vote
2answers
37 views

Number of connections differ between performance monitor and SQL Server Profiler

WHAT I'M TRYING TO FIND OUT How many logins/s and logouts/s I'm having on my SQL Server 2016 Standard. WHAT I DID SQL SERVER PROFILER In SQL Server Profiler, I made a trace selecting only 'Audit Login'...
0
votes
0answers
31 views

Bad estimates for 'dbcc checkdb with estimateonly'

I'm trying to compute the estimated tempdb space needed to run CHECKDB on one of our largest databases of size 7TB using the command DBCC CHECKDB WITH ESTIMATEONLY. The estimates came out to be 1.7 TB,...
0
votes
1answer
57 views

String aggregation using STUFF

Let's say I have table #DOC like this: polNum boxNo batchNum 111111 null qwe1234 111111 ff/55 rto1235 111111 ee/59 yhn1238 111111 ww/55 rto1235 222222 dd/58 jkl1234 222222 null fgh1234 333333 ...
0
votes
0answers
18 views

External verification of `SIGNBYCERT()` signatures from SQL Server 2016

I want to sign plaintext using a SQL Server certificate and then verify that signature using the certificate's public key on an external system. Here's what I've tried. -- Create a self-signed ...
0
votes
1answer
26 views

XE process_login_finish

Can anybody explain the purpose for process_login_finish extended event? I trying to catch some events for XE session with this event, but no one messages arrives to the session. However event "...
0
votes
1answer
17 views

Adding new user info to current old user accounts

Hey all so I have a database that has been up and running for a few months now. I had a request to add the users First and Last name as well as their organization. This was easy enough to do for NEW ...
0
votes
0answers
24 views

TSQL - Take a single row query and transpose based on field value

I'm trying to create a fact table with data I was given. We have a monthly goal number, but we want to be able to figure out how many weeks are in that month and divide the goal number by the weeks in ...
-1
votes
1answer
18 views

Why does query rowcount change when I enable “include actual execution plan” and “include live query statistics” together?

I have a moderately complex SELECT query that utilises inline views and joins. I am intending to see if I can rewrite portions of the query to improve performance. I pasted the query into two SQL ...
0
votes
0answers
11 views

SSRS High IOPS and Slowdowns

Please let me preface by saying that I understand poorly formed reports can cause poor SSRS performance and I have people looking at that issue separately. I have an SSRS server that is seeing ...
0
votes
1answer
59 views

What would cause this SQL Server deadlock?

I have a node.js script that is attempting to geocode records in a table. The script selects 100 records at a time that do not have lat/log data, geocodes them using a third party api and writes the ...
0
votes
1answer
16 views

Query store data maintenance background tasks

Query store has been running for years on the server without any noticeable disk IO spikes on log backups. We run on SQL Server 2016 EE version 13.0.5622.0 Recently we experienced a sudden surge in ...
-1
votes
1answer
33 views

Search string data with special characters removed

We have a table with ~6 million of products: ID Name Code 1 One 123.456.789-M 2 Two 852.789456 3 Three 1-123654.P What is a good way (with regards to performance) to filter products by code ...
0
votes
1answer
28 views

what databases are already set up in the log-shipping, and which ones are not?

I am setting up log-shipping from server1 to server2. there are many databases on server1 and I want to automate the process. I have already done a few databases manually, while I am working on the ...
2
votes
1answer
42 views

How to trim the log file that stores information about job history execution?

I was having a look at my sql server jobs and proxies on an specific server in the production environment. I pretty much have an script to check the status of the jobs but in case I have to ...
2
votes
2answers
160 views

Is partial synchronisation possible between two databases on different servers?

Imagine a scenario where you have a primary instance of a database and a secondary instance that is automatically synchronised - pretty much as with Availability Groups. My question is whether I could ...
0
votes
0answers
45 views

Comma separated value string and compare against other values

Hey all I have been trying to wrap my head around how to go about doing this for a while now and I'm not really coming up with anything. Here is my query: DECLARE @ATID AS VARCHAR(3) = 12 DECLARE @...
0
votes
0answers
30 views

SQL Server performance issue (Log Shipping became slow)

I'm facing the problem that MS SQL Server 2016 suddenly become slow. For example, restore log process (log shipping). I can see LASTWAITTYPE is PAGEIOLATCH_EX in SYSPROCESSES table during the process. ...
0
votes
2answers
30 views

does SQL automatic backup runs even the machine/server is offline

I am wondering if the SQL jobs like automating the database backup runs even the machine/server is offline?
1
vote
1answer
34 views

Update same column, same DB with specific records

We are migrating a site from one Active Directory system to another but in the third-party app, we need to keep the old accounts, so we renamed the old accounts with "_Old" appended to the ...
4
votes
3answers
224 views

Why do subsequent unmet conditions in my CASE statement affect my return data type?

I have the following variables and CASE statement: DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021' DECLARE @CONVERT_CODE VARCHAR(1) = 'I' SELECT CASE @CONVERT_CODE WHEN 'I' THEN DATEDIFF(day, '04/21/...
1
vote
3answers
58 views

Tsql help - doing a large update from CSV file to a table in ssms

I have been given a csv file with just ids in there and those ids are in a table in ssms with columns that require updating. Out of 90,000 ids in that table, only 83,000 of them require updating from ...
0
votes
0answers
59 views

SQL Server Replication: How do you change the distributor?

I've been given the task to change the distributor to a new server for pre-existing replication (both snapshot and transaction). Server A: Existing Replication (Publisher) Server B: Existing ...
6
votes
5answers
2k views

Is OPTION (RECOMPILE) used in production environments?

Is OPTION (RECOMPILE) used in production? This option seems to get a lot of bad press. Is it deserved? I have a DBA who, so far, is not a fan of the idea of OPTION (RECOMPILE) within the meat of ...
1
vote
3answers
157 views

How can parse column names using ordinal position (not name) from XML data in SQL Server?

I have several hundred tables in a database that have the same structure: SomeId, Pos, Varying Number of Other Fields So, for example, one table may look like this: PersonId, Pos, Hobby, Degree 12345, ...
2
votes
1answer
49 views

Use CTE to fetch most recent row for each ID

I just recently started learning about the wonders of CTEs and how they can be used, instead of WHILE loops, to optimize performance. For context, I am automating the process of updating firmware on ...
4
votes
4answers
657 views

Why changing the column in the ORDER BY section of window function “MAX() OVER()” affects the final result?

I have a table with below structure and it's data : create table test_table ( Item_index int, Item_name varchar(50) ) insert into test_table (Item_index,Item_name) values (0,'A') insert into ...
1
vote
1answer
31 views

Does SQL Server upgrade to table locks at total locks or session locks?

I’ve a table that both has mass sequential inserts at the end of the CI and random (very distributed) reads+updates. Naturally, the mass inserts should not block the random access. RCSI is used, so ...

1
2 3 4 5
50