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
7 views

Select query slow in the big table because of expired stats in Microsoft SQL Server

I have an application to select/search records from the database (application and database in separate servers). In the database, one table only is fast growing table (more than 2 Millions per day). ...
1
vote
1answer
293 views

How does one export/import registered servers with passwords?

I have a pretty extensive collection of registered servers on SQL Server Management Studio 2016 on my box. However, I got a new box and want to move the entire collection there. So I right-clicked ...
0
votes
1answer
86 views

how to edit a sql server profile template using a text editor like notepad or ssms?

I am running a trace where I need to filter the behaviour or about 50 users, and some 50 database objects. I would like, for this purpose to create a sql server profile template and later edit it to ...
0
votes
1answer
28 views

Creating an Index based on Execution Plan

I've been rewriting a view that was performing poorly - it had 28 LEFT JOINs to another view and I have managed to modify this to a single LEFT JOIN and we are seeing a pretty good improvement in the ...
5
votes
1answer
142 views

Is there any side effect if select statements are wrapped in BEGIN TRAN … COMMIT?

I am working on a rather old .NET project and have introduced some new features (on top) that have generated the following side-effect: all generated SELECTs (or groups of) are wrapped in BEGIN TRAN .....
0
votes
1answer
77 views

The OLE DB provider “MSDASQL” for linked server “(null)” reported an error

I created a linked server to MySQL database server on my SQL Server database server. I tested the connection. It succeeded successfully for MySQL connection. I can see the databases on MySQL instance ...
2
votes
1answer
127 views

SSIS Merge Join: Delete does not work

I've set up a Data Flow Task to sync a flat file with its destination table using a Merge Join with a Left Outer Join as described here (and elsewhere). Merge Join: Inserting and Updating works fine,...
0
votes
1answer
22 views

MS SQL Server 2016 - Database Security - Login Permissions

I have an SQL Server 2016 database with Always On structure. I have a login that I called x. My x login's properties are: Default database: abc Server roles: public (ticked) User mapping: Map (ticked)...
1
vote
0answers
23 views

sp_prepare vs sp_execeute PHP MSSQL

This question is regarding the PDO_SQLSVR driver for PHP and Microsoft SQL Server and how it interacts with the database, whether via sp_prepare (prepare statement execution) or sp_execute (direct ...
-5
votes
0answers
47 views

How to determine what is the potential max worker thread count on a server where the server property is set to 0? [duplicate]

I have a SQL Server instance where the Max Worker Threads server property is set to the default of 0. Periodically I'm receiving the the following errors (in the SQL Error Log): Error: 17189, ...
0
votes
0answers
41 views

Large number of connections to an SQL server results in read write bottleneck on hard drive?

I have the following problem which I can't find a solution for. We have a learning School System for more than 10000 students that upload photos and PDF of Homeworks as binary to an SQL server. During ...
0
votes
1answer
29 views

SQL Server 2016: Table can't grow while 313 MB is still available in the database

Yesterday, the below error was reported: Could not allocated space for object 'dbo.X'.'Y' in database 'Z' because the 'PRIMARY' filegroup is full After deletion of some records from the table by an ...
1
vote
2answers
91 views

How to move SSISDB database

I have a SQL 2016 instance installed. It has SSISDB for SQL server integration services installed. Now because it is taking up all the space on our system drive, I need to move its data and log files ...
0
votes
1answer
19 views

MS SQL Server 2016 - Always On Node-2 Stuck In Resolving State

For testing the Always On structure; we suddenly shut down the machine on which Node-1 was running. So we wanted to see that Node-2 turns primary. However, Node-2 was stuck in resolving mode. 3 ...
1
vote
2answers
5k views

“Untrusted Domain” error when connecting to SQL Server localhost and specifying port

I have two SQL Server 2016 instances running on localhost, and I can connect to both with no issues (localhost and localhost\test) using windows authentication. However, if I specify the port ...
0
votes
1answer
33 views

SQL database mail configuration to use a domain account that is not the SQL Service account

We have been asked to use a domain service account for everything releated to email (smtp). I looked at the SQL Database Mail and the only option that looked like Windows Auth. is the first one: The ...
3
votes
2answers
223 views

Failed to allocate BUFs during DBCC CHECKDB

I am writing to see if anyone can help me out with a problem that I just can't seem to figure out. This one is going to be a doosey and I am going to try and list out as much of the important info ...
0
votes
2answers
46 views

SQL Server 2016 -> 2019 AlwaysOn Rolling Upgrade Migration

I have a migration concept with an AlwaysOn configuration from SQL Server 2016 (on windows server 2016) to SQL Server 2019 (on Windows Server 2019) which are in different sub-networks. So with ...
0
votes
1answer
19 views

How to check who has permission to execute a stored procedure?

I have two environments/servers. Each has two databases, say DATABASE_A and DATABASE_B. There is a stored procedure in DATABASE_A (in both environments) that queries DATABASE_B and also calls multiple ...
0
votes
1answer
234 views

Security user audit

This has probably been asked a million times already and for this i apologise, but is there a way or any T-SQL I can run that will tell me, the server, the database, users login name, what permissions ...
0
votes
2answers
128 views

Database Mail Stop Working

SQL Server 2016 Enterprise Edition SP1 Database Mail was working fine with no problem until a server patching was performed. I tried to do all the troubleshooting available but still all mails got ...
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 ...
2
votes
3answers
49 views

Query to find sql server services currently running [duplicate]

Is there any query to find all SQL services currently running on the server including SSRS, SSIS, SSAS?
3
votes
1answer
165 views

How to decrypt dtsx package?

I've got some SSIS packages stored as varbinary, and I can load them into a temp table like so: Use SSISDB if object_id('tempdb..#package') is not null begin drop table #package end ...
0
votes
2answers
78 views

SQL Server: Who created an audit and who altered it last?

I am trying to find out who created an audit (found under Audits) but I don't seem to have any luck. I've already tried many queries such as these: Blog 1, Blog 2, Blog 3 And SELECT * FROM [sys]....
-2
votes
1answer
34 views

Is Buffer Pool Extension (BPE) needed if my database is already on SSD?

Here is my simplified scenario, let's say I have one (and only one) database of 500GB on SQL Server 2016 EE, this database is on SSD drives already (both data and log files), and my server has 64GB ...
0
votes
1answer
23 views

Install old Cumulative Update SQL Server 2016 Standard

We were running on SQL Server 2016 Standard Edition 2016 SP2-CU4 on our ALWAYS-ON HA cluster. Due to some recent issues one of our nodes got corrupted and we had to replace it with a new node. Since ...
1
vote
1answer
86 views

Error during sp_refreshsqlmodule after altering table type

I am trying to add a column to a user-defined table type. I have done some research and here is what I have now: -- First, rename existing table type to something else EXEC sp_rename '...
0
votes
2answers
28 views

Grant an AD Group permission to edit all MSSQL Agent jobs

We have a team of people, we all log in MSSQL Server from SSMS using our Active Directory accounts, by Windows Authentication. Our AD accounts are added to an AD group, which is used to grant all ...
2
votes
1answer
102 views

What is the difference between sys.dm_hadr_ tables and sys.availability_groups / sys.availability_replicas?

Browsing around some sys tables, I see: sys.dm_hadr_availability_replica_cluster_nodes.group_name = sys.availability_groups.name and sys.dm_hadr_availability_replica_cluster_nodes....
0
votes
1answer
80 views

In what order does sqllogship.exe process groups of log files?

In what order does sqllogship.exe processes groups of log files based solely on filename order or does it does it do it based on file timestamp primary sort then filename secondary sort? It appears ...
0
votes
1answer
162 views

AlwaysOn database not synchronizing/recovery pending due to cluster loss quorum [duplicate]

I have this error in cluster event 'Cluster node 'MMPDB1NEW' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to ...
13
votes
2answers
708 views

Unclear update conflict

I have two questions: 1. Why do I get update conflict in this situation instead of just blocking: -- prepare drop database if exists [TestSI]; go create database [TestSI]; go alter database [TestSI] ...
1
vote
2answers
58 views

MSSQL TempDb full

I have a problem with my MSSQL cluster. When switching to SQL Server 2016 (from 2014) an error occurs with the tempdb, which is full after a certain time for no apparent reason. Is there a proven way ...
1
vote
1answer
38 views

sp_send_dbmail: Failing When Adding Attachment

I have a simple script that should send an attachment using send_dbmail. It works when I exclude the attachment or send a very small file, e.g. 5KB. The prod file I want to send is 280KB. Exec ...
2
votes
2answers
924 views

Generate SQL Table DDL from a View

How do I generate table structure from a view, in one script? Will only need data types, I am seeking a Table Create Script with some type of dynamic sql (1) can remove primary key/constraint, (2) ...
1
vote
1answer
24 views

Why did full backup file size grow after data movement for Availability Group replica was suspended?

Found an error log informing that data movement to a secondary availability replica was suspended. I'm not sure whether the cause of the suspension is relevant to answering my question, but the cause ...
0
votes
1answer
412 views

SSRS2016 external parameters for mobile report

How can external parameters be passed to a SSRS 2016 mobile report in order to pre-select values / settings for the report? The same scenario is possible for paginated reports as it always was. It's ...
2
votes
1answer
285 views

SSIS:How retrieve and insert an XML Column

I'm trying to Insert two columns from one table onto another. The first column is an int but the second column is an XML. I'm having issues inserting the xml column In first SQL Task I'm doing a ...
5
votes
1answer
417 views

Reinitialize Table Values in SQL SSDT Unit Testing

I am creating SQL Server Unit Tests. We are testing various stored procedures. In Unit testing principles, it is good practice to setup a small database table, populate values, and tear down (...
2
votes
3answers
225 views

Backing up databases on SQL Server hosted in Windows Azure VM

One of the clients at my workplace asked me to find out information on what would be the best possible or recommended approach to run maintenance jobs such as backup job for a SQL Server 2016 hosted ...
1
vote
1answer
181 views

SQL Server 2016 issue regarding migration of in-memory table and foreign keys

Trying to use table memory optimization advisor on SQL Server 2016, seems I need to drop and re-create the foreign keys of some disk based tables, the size of the table is 300 GB and I'm wondering if ...
2
votes
1answer
467 views

UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' in Agent job

In brief, I added a computed column to a table and then Agent jobs began failing and reporting the error: UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'...
0
votes
1answer
27 views

Receiving “Query processor could not produce a query plan because of the hints defined in this query” when using FORCESEEK but can use FORCESCAN?

I have a fairly large table (500 million rows, 30 columns wide, about 130 GB of data total). One of the columns in that table is a DateTime data type, and I need to repeatedly select records from that ...
0
votes
0answers
32 views

Why is there no implicit conversion when using FORMAT vs CAST/Convert in CONCAT [duplicate]

When we have the following table: CREATE TABLE Test1 ( Name NVARCHAR(50) NOT NULL , Code SMALLINT NOT NULL ) With only 1 row for demo purpose: INSERT INTO Test1(Name, Code) VALUES ( 'ABC', 1)...
2
votes
1answer
204 views

SQL PROFILER Replay statistics event interpretation

I tried capturing a replayable trace on a SQL Server 2012 production environment and then replay it against a SQL 2016 test environment. After doing so , I got the following results : Can anyone ...
0
votes
1answer
30 views

Updating SQL Server 2016SP2 to latest CU in Cluster…what happens? [duplicate]

Updating to latest CU in a cluster with a bunch of databases. Can I do this on the passive node without SQL Server kicking on? Do I need to worry about that? I think I can run it on the passive, ...
-1
votes
1answer
24 views

Power shell script for failover from primary to DR

SQL server version is SQL Version 2016 Current setup is two nodes in primary having always on availability group. Adding a 3rd node which is DR(Disaster and Recover). Does anyone knows a power shell ...
0
votes
0answers
27 views

Combine multiple alerts in to single alert

I'm trying to find a way to generate a single alert by combining multiple alerts for AlwaysOn setup (for example error codes- 1480 , 19406) which gets logged in SQLERRORLOG file during planned or ...
0
votes
1answer
248 views

Missing Encryption Key when migrating Reporting Services from SQL Server 2008 to SQL Server 2016

Owing to a ransomware attack on our production server Reporting Services went down. This server was installed few years ago and its been in production for long. We have quite a few reports on this ...

1
2 3 4 5
46