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
1 answer
24 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
0 votes
1 answer
39 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
  • 241
0 votes
2 answers
39 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
34 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
22 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
36 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
35 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
43 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
56 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
  • 1
-2 votes
1 answer
65 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
86 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
82 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
41 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
67 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
86 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
83 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
31 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
100 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
51 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
30 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
57 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
50 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
41 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
89 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
68 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
74 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
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
150 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
  • 123
0 votes
1 answer
38 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
69 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
152 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
165 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
70 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
95 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
118 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
58 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
302 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
483 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
197 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
57 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
451 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
114 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
  • 355
-1 votes
1 answer
341 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
2k 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

1
2 3 4 5
56