Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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

Workaround for a view with joined data on encrypted column

I've got a view that currently has this within: SELECT * FROM customers_1 LEFT JOIN customers_2 on customers_1.last_four_of_ssn = customers_2.last_four_of_ssn AND customers_1....
Patrick's user avatar
  • 101
1 vote
1 answer
38 views

Unique constraint for single structure that simulates relations between tables

Imagine I have several tables in SQL Server 2016: Cat Cat carrier Cat transit box Cat car Every object can be stored in higher level, but not necessarily directly one above with N-to-1. So a cat can ...
Macok's user avatar
  • 11
9 votes
1 answer
657 views

Extra NESTED LOOP / INNER JOIN causing NO JOIN PREDICATE warning

I have the following tables in my database. LastNames Table CREATE TABLE [dbo].[LastNames]( [LastNameID] [int] IDENTITY(1,1) NOT NULL, [LastName] [varchar](50) NOT NULL ) ON [PRIMARY] GO ...
John K. N.'s user avatar
  • 17.9k
1 vote
1 answer
45 views

Index not being used for date predicate

I'm doing abit of self learning on indexes & performance in a SQL Server. I'm using stackoverflow's database as my sample DB for this activities. For my testing, I'm using the Users table & ...
Jason Oon's user avatar
0 votes
0 answers
60 views

In SQL Server database mail is not working

Database mail is not working: Below Error while sending a test mail Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail. (Microsoft SQL Server, Error: 14641) Checked ...
Ajith Kumar M's user avatar
0 votes
0 answers
19 views

Reduce primary DATAFILE size for an SQL Server Database 2016

In an AG always on configuration (primary) I have a database with 10TB datafile, 50% of the datafile is a free space. Then, I want to reduce this DATAFILE from 10TB to 8TB. How to proceed for that ...
user avatar
0 votes
1 answer
37 views

snapshot to apply just the schema changes - not the data

I am restoring databases to each side of my transactional and merge replications. Basically the publication and the subscriber are the same database initially. I still need to run the snapshot agent ...
Marcello Miorelli's user avatar
1 vote
1 answer
49 views

ApplicationIntent = ReadOnly and Writes queries

I'm confused about behavior of connection string that specifies ApplicationIntent = ReadOnly and write queries in SQL Server 2016 Enterprise Edition. Let's say, Availability Group has a primary - X1 ...
newprint's user avatar
  • 251
0 votes
2 answers
49 views

Trying to renumber a field in order grouping by another

So I have a table that has a column labeled 'sequence', it is not in fact a sequence data type, just a numeric field that translates in the object model to show items in a certain order. In a handful ...
Jonathan Beck's user avatar
0 votes
0 answers
47 views

Intermittent Error On SQLServer Hourly Database Backup Maintenance Plan

Some of our SQLServer machines' hourly maintenance plan with Back Up Database Tasks are failing intermittently, around once or twice per week. The next hourly execution runs without any issue. Each ...
JieLong's user avatar
  • 167
0 votes
0 answers
35 views

Data Loss and data redeundancy when forced failed over to secondary replica on DR Data Center in SQL Server 2016

We are using SQL Server 2016 Enterprise edition and planning to have 2 datacenters primary and the remote Disaster recovery(DR). We have 2 replicas (primary replica and secondary replica) in the ...
Manoj Jeganathan's user avatar
0 votes
1 answer
55 views

Extra `CASE WHEN` Removes Text from Dynamically Created String

I am trying to generate a CREATE TABLE statement based on a given table in my SQL-SERVER database using the INFORMATION_SCHEMA tables, but when I try and concatenate the DEFAULT portion of a field ...
javery's user avatar
  • 3
1 vote
0 answers
37 views

DST Adjustments to Ola Hallengren's backup jobs not working as intended [closed]

I have been using Ola Hallengren's backup/maintenance scripts for a few years but one thing that usually plagued me once a year was our backup location going near capacity after spring forward DST. I ...
Matt's user avatar
  • 365
0 votes
2 answers
47 views

database involved in availability group - showing state as online but it is synchronizing and does not accept connection - how can I find that out?

how can I find the list of databases I actually can connect to? I am using sys.master_files to check the state of my database files however, on the secondary, the database is synchronizing and it is ...
Marcello Miorelli's user avatar
0 votes
0 answers
68 views

SQL Server Database Audit used with a trigger to monitor CREATE TABLE events

Got SQL Audit enabled on a database with SQL Server 2016 SP1. Trying to catch a CREATE TABLE event with a trigger, and add that table to the audit to log data operations. Problem is that changes to ...
Razvan Zoitanu's user avatar
0 votes
1 answer
86 views

OPENROWSET on sp_executesql with two dynamic parameters

Can someone give me a working example of using OPENROWSET on SQL Server with dynamic SQL (sp_executesql) executing a stored procedure with two parameters as variables, preferably date and integer? I ...
mk SQL's user avatar
  • 5
-2 votes
1 answer
68 views

Is there any way to send SMS without OLE Automation? [closed]

We use OLE Automation methods such as SP_OAmethod, Sp_Oacreate, etc.. to send SMS to users from SQL Server. I wish to stop use OLE Automation. Is there any alternate way to send SMS?
dbajayy's user avatar
2 votes
1 answer
87 views

SQL Server 2016 Deadlock

I want to validate my understanding of the sqlserver deadlock report: context: parent_table(id); child_table(parent_id, col1, col2, ...); constraint primary_key(id) on parent_table; constraint ...
brownsugarpearl's user avatar
1 vote
2 answers
85 views

QueryStore plan forcing errors

We have a weekly stored procedure that ocassionaly picks up wrong execution plan that causes much longer execution times. I've tried to force correct execution plan but still every few weeks the ...
Andrzej Błoch's user avatar
0 votes
0 answers
32 views

Updates and Row Level Security - No Difference Issue

We are using RLS (row level security) in SQL Server 2016, yet we're seeing no difference in effect with using BEFORE versus AFTER in our update block predicate. Using the below code as an example for ...
rclouder98's user avatar
-2 votes
1 answer
42 views

Best Practice for full backup and transaction log truncation [closed]

I am new to SQL Server DB. Could someone please let me know what would be the best practice to do a full backup and truncate the transaction log. If you could please let me know the strategy you are ...
ab san's user avatar
  • 9
1 vote
1 answer
71 views

how can I see how many records have been affected by a transaction before it is committed? or how costly would be the rollback if I have to kill it?

I am using sqlwatch to monitor some of my servers and it has a job that deletes in batch from a table called dbo.sqlwatch_logger_snapshot_header I had lots of problems of locking and blocking as this ...
Marcello Miorelli's user avatar
1 vote
1 answer
88 views

how to find out the free space inside a database file when the database is not online?

Normally when the database is online I use the following query and get how much space is free inside each file: SELECT CONVERT (decimal(12,2) , (CONVERT(...
Marcello Miorelli's user avatar
0 votes
0 answers
103 views

Incorrect Next Run on Monthly Agent Job

Users have reported that a job on a recurring schedule has not been firing. This is also evident in the execution history. After some experimentation, it looks like it might be a bug in the SQL Agent. ...
Peter Godwin's user avatar
0 votes
1 answer
65 views

what sql server is holding in the used memory?

I have a query that shows me how much memory sql server is using, the settings like max memory and min memory, cpu, etc. here is the query: --===========================================================...
Marcello Miorelli's user avatar
0 votes
0 answers
47 views

How to find a deleted SSRS subscription that still runs

In troubleshooting an 'SSRS won't email subscriptions' problem I created a test subscription. I then found the SQL Agent job and changed the owner (I read the disclaimer in the job description, ...
dogyog's user avatar
  • 101
2 votes
1 answer
120 views

RESTORE DATABASE - The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time

While doing a restore of a big database 1.8 TB into a different server I came across this error message when the restore was about 90% completed: 90 percent processed. Msg 3013, Level 16, State 1, ...
Marcello Miorelli's user avatar
2 votes
0 answers
53 views

Views that were created in SQL Server 2008 R2 are encountering compatibility issues when re-created in SQL Server 2016 with identical definitions

We've created a view in 2008 R2. Provided below are the definitions for both the base table and the view. Base table: CREATE TABLE dbo.test( fcrecid char(25) NOT NULL, fctype char(1) NOT NULL, ...
Yashas's user avatar
  • 21
1 vote
0 answers
31 views

AWS SQL - High CPU Usage from select * from #spproccol

SQL 13.x will intermittently spike a CPU usage (60-90%). It will stay high so we added AWS Performance Insights. These queries are not something we have defined or used but something is calling them ...
Brad G's user avatar
  • 11
0 votes
1 answer
74 views

Convert numbers to words | Spell out DECIMAL USD 1259.75 as 'One thousand two hundred fifty-nine dollars and seventy-five cents'

ALTER FUNCTION [dbo].[NumberToWords](@Number DECIMAL(18, 2)) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Words NVARCHAR(MAX) SELECT @Words = COALESCE(@Words + ' ', '') + CASE WHEN @...
Luis Avalos's user avatar
0 votes
0 answers
51 views

what to do before restoring a database that is involved in TDE (transparent data encryption)?

I use the following script from John Eisbrener to get the chain of backups, in order to restore them: WITH BackupHist AS ( SELECT s.server_name , d.name AS ...
Marcello Miorelli's user avatar
1 vote
0 answers
44 views

Modified_extent_page_count shows 0 extents modified on a 1.7TB database, but running a differential takes takes 4-5 hours and generates a 50GB file

As per the title - modified_extent_page_count shows 0 extents modified on a 1.7TB database, but running a differential takes takes 4-5 hours and generates a 50GB file. I wondered if anyone has come ...
DB-Ay's user avatar
  • 11
-4 votes
1 answer
99 views

DBmails are not working after modifying the sp_send_dbmail procedure

I have modified sp_send_dbmail procedure in sql server. Laster mails stopped working. Dbmail is shutting down automatically and getting this error while I restart process 1) Exception Information ====...
dbajayy's user avatar
0 votes
0 answers
95 views

Connection Retry via OLE DB VS ADO.NET in SSIS

Problem: We want to achieve Connection resiliency in SSIS Packages(OLE DB vs ADO.NET Connection manager) Environment Details: Database: SQL Server 2016 SSIS Connection manager: OLEDB OLEDB Provider : ...
ishan verma's user avatar
1 vote
0 answers
86 views

"MSSQL\repldata\unc" Is it safe to manually delete old files that belonged to old SQL Replication?

I am trying to see if it is safe to manually delete old files located under "C\Program Files\Microsoft SQL Server\MSSQL13.XXX\MSSQL\repldata\unc". I realized that data in this folder is ...
Java's user avatar
  • 233
1 vote
1 answer
54 views

Release Database Transaction Log File Freespace In SQL Server 2016 [duplicate]

Good afternoon, We have 4 node SQL Server configure in HA/DR. Periodically, we purge the old data to free up the space. Recently, after the SQL Job to purge the data was executed, we notice an ...
rushabh138's user avatar
1 vote
1 answer
264 views

Can SSIS read the results of a stored procedure and write them to CSV, without populating a table?

My current workflow is as follows: A 'Execute SQL Task' task runs a stored procedure that populates a table. After this, a 'Dataflow Task' task does the following: 2A. Read every row from this table ...
J. Mini's user avatar
  • 431
0 votes
1 answer
42 views

what is stopping my transactional replication services?

I will be straightforward and put my question before I add any other information, as I want to keep it objective and to the point. the question is: How to find out why the replication services are ...
Marcello Miorelli's user avatar
0 votes
1 answer
47 views

SQL Database MaxSize higher than total drive capacity/space

I received a SQL database backup that I need to restore that is only about 1.5GB, but the MaxSize is several hundred GB. The PC/server that I need to process the data on this backup only has a 250GB ...
Jason Z's user avatar
0 votes
0 answers
93 views

SSRS 2016 running slow with blocking

SSRS 2016 - Standard Default Settings. Work Horse for Reporting. Some large reports can run 5 hours+. Most reports cannot be optimized/tuned - 3rd party vendor. On occasion noticed procs "...
jay's user avatar
  • 31
-1 votes
1 answer
58 views

Automatically fetch USD Exchange Rate from API and Update it in Microsoft SQL Database

I want to update a table in a database but I can't even fetch the rate from the API. I get this error: Msg 39007, Level 16, State 2, Procedure sp_execute_external_script, Line 1 [Batch Start Line 0] ...
Luis Avalos's user avatar
0 votes
0 answers
192 views

SSIS Error: "Metadata could not be determined because procedure uses temp table"

We have recently restored SSIS Packages and I'm facing this error whenever I try to execute a SSIS package: Unable to determine metadata because « INSERT INTO #File_Results EXEC Master.dbo....
Cristhian's user avatar
3 votes
0 answers
176 views

Why am I getting RESOURCE_SEMAPHORE timeouts when there should be plenty of memory available for grants?

We occasionally have queries time out due to RESOURCE_SEMAPHORE waits but there appears to be plenty of available memory for memory grants. For example, I'm able to see timeouts by executing two ...
Joe Obbish's user avatar
  • 32.2k
1 vote
1 answer
81 views

Why does a SAVE TRAN outside of a CURSOR loop not work, but inside does?

Why, when my SAVE TRAN SavePoint is at location (A) do I get these errors on the third instance of falling into the CATCH and beyond... "Msg 6401, Level 16, State 1 ... Cannot roll back ...
Chris Marriott's user avatar
1 vote
1 answer
171 views

Database Mirroring - Failed Logins on primary after failover

We have a Database Mirroring setup between PROD and SECONDARY and we have failed over to the secondary replica (SECONDARY) . We are now getting a number of almost constant failed logins showing in the ...
SE1986's user avatar
  • 1,970
0 votes
1 answer
132 views

Batch update in a large dataset

We have 3 tables: Product (product) Stock (product_Stock) Price (product_price) In each table, we have more than 290 bilions of rows. These tables have a compound primary key: product_fk and ...
Gadonski's user avatar
  • 191
0 votes
1 answer
68 views

Creating AGL for Named Instance on SQL Server 2016 [duplicate]

I have installed a Named Instance on an existing server so the set up is like the following: Server 1: QA01 - Default Instance QA01\BU0 - Named Instance Server 2: QA02 - Default Instance QA02\BU0 - ...
kaitlyn's user avatar
  • 583
3 votes
1 answer
313 views

Are there any possibilties to mitigate data loss risk on delayed durability

Due to poor disk performance I need to evaluate using the delayed durability setting in SQL Server 2016. Microsoft states clearly, that this comes with a data loss risk because transactions are ...
Mainecoon21's user avatar
0 votes
0 answers
45 views

SQL Server 2016/2019 mixed AG causing constant DB transaction log growth

We have a SQL Server 2016 Always On Availability Group (AG) setup with 3 nodes and recently added 3 new nodes with SQL Server 2019. After the addition of the new nodes, we noticed that the database ...
user avatar
-3 votes
1 answer
486 views

System dbs have different collation

i am new on this forum.i have installed an instance of sql srv 2016 sp2 called DNF containing 2 dbs and also the system dbs.after providing the instance to the dev team the realise that they need to ...
Toni's user avatar
  • 1

1
2 3 4 5
56