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

I wish to stop use OLA automation

We use OLA automation methods such as, SP_OAmethod, Sp_Oacreate etc.. to send SMS to users from sql server. I wish to stop use OLA automation, Is there any alternate way to send SMS?
dbajayy's user avatar
2 votes
1 answer
68 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
1 answer
67 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
25 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
35 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
63 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
85 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
75 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
64 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
15 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
86 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
1 vote
0 answers
46 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
  • 11
1 vote
0 answers
27 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
45 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
49 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
34 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 ...
MattT's user avatar
  • 11
-4 votes
1 answer
83 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
52 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
61 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
  • 223
1 vote
1 answer
53 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
102 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
  • 139
0 votes
1 answer
34 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
46 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
52 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
  • 21
-1 votes
1 answer
55 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
114 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
105 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.1k
1 vote
1 answer
62 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
0 votes
1 answer
63 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,960
0 votes
1 answer
93 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
56 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
  • 573
3 votes
1 answer
294 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
43 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
477 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
0 votes
3 answers
171 views

Partitioning an existing table yearly in SQL Server with huge data

I have a logs table for my application which is almost 3TB data. I want to partition this table yearly. Below is the scripts for Table, View, and indexes script: GO CREATE TABLE [dbo].[AuditLogs]( ...
Mohd Waseem's user avatar
0 votes
1 answer
56 views

Adding custom table to local third-party ERP database

We have a locally hosted third-party SQL Server ERP database I've been working with for a while. For one of our KPI reports we need historical data. This data is not generated by the ERP database ...
fjdksflds's user avatar
0 votes
0 answers
340 views

Report Server WMI Provider Error: Invalid namespace when connecting to Report Server Instance on Reporting Services Config Manager

I have two SQL Server 2016 instances (Instance A and Instance B) on the same server. Both instances have SSRS. At one point I upgraded Instance A to SQL Server 2019 but then due to the changes made to ...
Solizmi's user avatar
0 votes
1 answer
96 views

Failover in AG without listener

We have SQL Servers with versions SQL Server 2016 and SQL Server 2019. We already check that we can do failover on SQL Server 2019 without listener but I can not test it on SQL Server 2016. Do you ...
adam.g's user avatar
  • 335
-1 votes
1 answer
274 views

SP_SERVER_DIAGNOSTICS_SLEEP wait type

I have one problem. SP_SERVER_DIAGNOSTICS is in suspended state and starttime is not refreshed. I execute procedure SP_SERVER_DIAGNOSTICS manually and it is working normally. Some forums say its ...
natiq rzazade's user avatar
2 votes
2 answers
1k views

DBeaver does not recognize declared variable when executing SQL script on SQL Server

We are using DBeaver Community Edition (v23.2.0.202309041200) as a client connecting to Microsoft SQL Server 2016 (v13.0.5101.9). The issue happens when executing the below SQL statements in DBeaver ...
James's user avatar
  • 149
0 votes
2 answers
192 views

Compare two tables for discrepencies

I am trying to compare two tables in two separate databases (same instance) for a new data extract The tables have around 4 millions rows and due to how they've been extracted are not in the same ...
QueryQuirk's user avatar
0 votes
1 answer
82 views

SQL Server Service Broker on a High Availability Group

I'm using a instance with Microsoft SQL Server 2016 || Enterprise Edition and I need to enable Service Broker for my database with this sentence: ALTER DATABASE DB SET ENABLE_BROKER; The size of the ...
Carolina's user avatar
0 votes
1 answer
331 views

Msg 9002 - The transaction log for database [dbName] is full due to 'CHECKPOINT'

The database in question is set to SIMPLE recovery mode. The disk is not out of space. The database is essentially read only. I can't change any of its properties or run a checkpoint. The transaction ...
praxidyke's user avatar
0 votes
1 answer
123 views

Sql server migration side by side ( 2016 to 2019)

What is the best (i.e. fastest) way (backup and restore) for migrating a 6 TB database in SQL Server 2016 (Windows 2012r2) to another server, SQL Server 2019 (Windows 2019)? Can we, for example, ...
dba_rs's user avatar
  • 7
0 votes
1 answer
80 views

Why does data file growth fail even with plenty of disk space?

We have a SQL Server 2016 (v13 SP3) Enterprise Edition server hosted in a Windows failover cluster / SQL Availability Group with two nodes (primary & secondary). The two nodes are running inside ...
nateirvin's user avatar
  • 750
3 votes
1 answer
565 views

What is "execution count" in Query Store?

We are using SQL Server 2016. We have some problems in terms of queries duration on 2023-09-11. Would like to check it on QueryStore and I saw on query store executed on 2023-09-11 05:00 PM: That we ...
adam.g's user avatar
  • 335
0 votes
0 answers
45 views

SQL Server CEIP login account

One of my servers have login of "DOMAIN\MACHINENAME$" in both login_name and original_login_name for SQLServerCEIP when checked using sp_who2. Under services, SQL Server CEIP settings shows ...
JieLong's user avatar
  • 167
0 votes
1 answer
111 views

Got an error when trying to get ER-diagram on SQL Server

We are using Microsoft SQL Server 2016 Management Studio (version 13.0.16106.4) connecting to an instance of SQL Server 2016 running on Windows 2016. The authentication is "SQL Server ...
James's user avatar
  • 149
4 votes
1 answer
548 views

Compilations/sec high when load testing a simple procedure

I am trying to load test a simple insert stored procedure: CREATE TABLE _test(ID BIGINT) GO CREATE OR ALTER PROCEDURE dbo.test_sp AS BEGIN SET NOCOUNT ON; BEGIN INSERT INTO _test ...
Artashes  Khachatryan's user avatar
0 votes
1 answer
94 views

Text Search on Multiple Columns without enabling FULL TEXT Search

I have a scenario where we need to enable a multi-keyword search within a text field. This search should involve scanning the keywords across several columns (approximately 4-6) in multiple tables (...
AA.SC's user avatar
  • 4,053

1
2 3 4 5
56