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

Linked Server Performance

I have three MS SQL Servers, 2016. 1 source and two destinations. There is a linked server setup from each destination server to the source. We execute a simple select query from each destination ...
2
votes
1answer
19 views

Network Maintenance and High Availability Group Prep

We have had a RAID card on one of our switches die, and the replacement had issues which caused my availability groups to failover multiple times between my 2 nodes like a game of tennis and left a ...
-2
votes
0answers
26 views

Get the busiest hour and day of the week [on hold]

I have an ecommerce app. I was looking for a SQL statement have uses the [orderdate] column. Then can determine the busiest time of the week, and the slowest time of the week. e.g. Friday 14:00 .... ...
-6
votes
0answers
41 views

Same execution plan but huge difference in execution time. CREATE and DROP INDEX resolves problem. Why? [on hold]

I'm moving database from MSSQL 2008R2 to 2016. I have my own procedure which runs MERGE on 3GB table (syncs rows in same table of different "class"). After moving to new MSSQL I found that ...
1
vote
3answers
43 views

Int for identity/primary key and Guid for public identifier, best practices?

I almost always use an int for identity/primary (auto incrementing) clustered key for my tables. They're easy to query and debug, bounce around in SSMS, and plenty of other reasons. I have a case ...
0
votes
0answers
32 views

How to prevent large queries from causing timeouts? [on hold]

I'm finding that when I run a large query or a series of large queries the Page Life Expectancy drops and remains low and I soon see timeouts on other procedure calls. Is there any way to avoid this ...
0
votes
0answers
24 views

DBCC Command Not being run by current user

I have a stored Proc that runs a DBCC command CHECKIDENT The sproc is being run by a user with adequate permissions. however, it is failing on the DBCC command with the error. User 'DifferentUser' ...
1
vote
2answers
32 views

MS SQL Server create user who can access only those databases which they have created

How to create and give permission to a user in such a way that they will have full access only to the database(s) they have created not other database(s). I am using SQL Server 2016 Express.
1
vote
3answers
103 views

Obtain a “list” of stored procedures which include INSERTs without naming the columns involved

As part of some enhancement, a new column has been added to over 750 tables. Now my problem is within almost 3000+ of stored procedures, some old INSERTs do not name columns. Is there any way I can ...
0
votes
0answers
16 views

Are Data Quality Services (DQS) included in SQL Standard 2016 Edition license? [closed]

Are Data Quality Services (DQS) included in SQL Standard 2016 Edition license? While reading Microsoft SQL Service 2016 Licensing Guide I could say NO. BUT While looking on Standard Edition Setup ...
0
votes
1answer
44 views

How to Query Which Table Is Inserted Mostly In SQL Server?

I have a database and there is not any trigger which follows the transactions in said database. Now, I have to make a report that shows which table has mostly inserted so far. How can I do that?
4
votes
4answers
569 views

Software need db owner permission to master database (sql2016)

We have a manufacturer whose software requires authorization for the master database (only db owner working). Unfortunately, the manufacturer can not tell us why this access to the master database is ...
0
votes
0answers
22 views

Read performance of SQL Server data files data from Azure BLOB

I have a setup where there is SQL Server on premises (MS SQL Server 2016 13.0.5366.0) but database files (log and data) are stored in cloud storage account. Setup described here. The interesting ...
8
votes
3answers
411 views

Using SPID in DB Tables (instead of Table Variable)

Transactional database used for booking things... Our vendor was asked to replace #temptables with @tablevariables (because of heavy compile locks) but instead they replaced with an actual table ...
0
votes
0answers
76 views

SQL Server Query Timeout with NOT IN clause but IN works fine

So were using SQL Server 2016 Enterprise and suddenly have an issue with a query on the Production server. Works fine on Test and Dev and if we backup and restore the production DB to either of ...
2
votes
1answer
25 views

Facing permission error while trying to execute a stored procedure published through CLR

I've created a stored procedure using SQL CLR c# stored procedure in Visual Studio 2017 and I've published it in MSSQL Server. Now I can see my stored procedure, but I face this error when I want to ...
2
votes
3answers
253 views

How do I query for system views in a SQL Server database?

I have some queries that run against Query Store, the view sys.query_store_wait_stats is not in SQL Server 2016, but it is in SQL Server 2017. I want my queries to work in SQL Server 2016 and SQL ...
2
votes
1answer
68 views

How to check if backups are reliable?

I generally have to restore databases to different servers after important data imports or other important and delicate database operations. I need to be sure that my backups up to that point in time ...
1
vote
1answer
29 views

This failure occured while attempting to connect to the Principle server

One of our clients recently came to us with a problem he's facing while working with our application. The error in the log file is the next: System.Data.SqlClient.SqlException (0x80131904): Timeout ...
2
votes
1answer
62 views

How to send mail from a restricted SQL Server

I have created an DB refresh job on a pre-prod SQL server, with latest successful Prod backup to refresh pre-prod DB every monday morning. However, i want to send a status mail post refresh. Due to ...
2
votes
1answer
39 views

How to use grouping sets in this query that shows database sizes - adding the last time it was updated

I have the following query that shows me the database sizes, including log dna data files. SELECT [Database Name] = DB_NAME(s.database_id), [Type] = CASE WHEN s.Type_Desc = 'ROWS' THEN 'Data ...
2
votes
2answers
56 views

Adding a database with Service Broker to an existing Availability Group on a new replica

Server 1 - SQL Server 2016 (Primary in existing AG) Server 2 - SQL Server 2016 (Secondary in existing AG) Server 3 - SQL Server 2016 (Offsite Secondary in existing AG) Server 4 - SQL Server 2017 (New ...
0
votes
1answer
36 views

Error in WSFC Role while applying SQL 2016 CU7 in Multiple SQL instance environment

Kindly advise on following error: Cluster resource 'AG1_NAME' of type 'SQL Server Availability Group' in clustered role 'AG1_NAME' failed Based on the failure policies for the resource and ...
0
votes
0answers
44 views

how i can solve this problem in sql server management studio 2016 [closed]

Copying to [dbo].[Sheet1$] Error Messages Error 0xc020901c: Data Flow Task 1: There was an error with Source - Sheet1$.Outputs[Excel Source Output].Columns[AA_change] on Source - Sheet1$.Outputs[...
0
votes
1answer
21 views

Why merge replication wants to replicate view before replicating related table?

We have a publication and a subscriber running on 2 SQL 2016 servers. Now the error that we are receiving during subscription initialization is: Error messages: The schema script 'vw_BlackList_961....
0
votes
2answers
39 views

Copy a database to a new schema in an existing database

Just curious if there is a way to create a new schema that is based on an existing database. Assuming the existing database has .dbo schema only.
-1
votes
0answers
40 views

SQL server 2016 -SQL Log Shipping Two Databases using the same shared folder [duplicate]

Server A -Primary database, Server B - secondary database and Server C is file server. Server A and Server B not able to connect or different network. Server A and B able to connect server C. ...
-2
votes
1answer
46 views

Can I use log shipping between different editions of SQL Server 2016?

Will log shipping work between different editions? Primary is SQL Server 2016 SE and Secondary is SQL Server 2016 EE. If not, how else can I set up high availability for this combination of servers?
-2
votes
0answers
134 views

Execution of Predicates SQL Server [closed]

Quick question regarding predicates. Does SQL Server 2016 execute predicates in certain order? For example, I have a query where I look at 2/3 tables and perform joins on them and then perform a ...
2
votes
1answer
138 views

BST/GMT to UTC time

Anyway to convert from BST datetime stored in DB to UTC Had a look at AT TIME ZONE but seems its from UTC to other times. DB stores datetime as BST/GMT historically need to now return these as UTC ...
-1
votes
2answers
85 views

Slow query, index present

This query is executed many times as part of an application, but runs slow: SELECT [c2], [c1], [c5], [c4], [c3], [c6] from [MyBigTable] where [c7] = 1 and [c3] in ('a','b','c','d','e') and [...
1
vote
1answer
14 views

Use Powershell to query SSAS connection string

I want to retrieve the existing connection string from SSAS for each OLAP Cube or Database. The data will be used in a control/audit report. I've found that Powershell allows me to drill into SSAS ...
2
votes
0answers
44 views

View with where clause slower than query with same where clause

This query with periods (PeriodStart,PeriodEnd,ticker,FundamentalId,dimension) as (select b.reportdate PeriodStart, MIN(a.reportdate) PeriodEnd,b.ticker, b.FundamentalId,b.dimension from Base a ...
1
vote
3answers
57 views

Prepared statements with many parameters is slow

I have an application written in PHP with Laravel that regularly prepares and executes statements like this: -- All parameters are varchar(10) SELECT c1, c2, c3, c4 from MyBigTable ...
0
votes
1answer
26 views

Get last Reoganized OR Rebuild index date

I am currently working on migrating sqlserver 2008r2 to SQL server 2016.The DBA before me used "IndexOptimize" from https://ola.hallengren.com/downloads.html to create the iIndex solution. I can see ...
2
votes
1answer
35 views

Catching slow queries with prepared statements

I use extended events to identify slow queries, defined as queries taking more than 2 seconds to complete, like this: CREATE EVENT SESSION SlowQueries ON SERVER ADD EVENT sqlserver....
1
vote
1answer
39 views

SQL Server Setup has encountered the following error: Mixed mode assembly is built against version 'v2.0.50727'

I had SQL Server 2016 Standard Edition installed on a server. Due to some drive reconfiguration, I had to do a reinstallation. I uninstalled using the program and features and just as I was about to ...
1
vote
1answer
50 views

UPDATE to linked server (Pervasive Database) is failing using OPENQUERY

I have 2 database servers: ServerA : Windows Server 2012 R2 Standard, SQL Server 2014 (SP3) (KB4022619) Standard Edition. ServerB : Windows Server 2016 Datacenter, SQL Server 2016 (SP2-CU6) (...
0
votes
1answer
20 views

Transactional replication Issue

I am getting this error when i try to create a new publicaton in dbserver-1 after reinstalling SQL Server in my database server 2. Msg 18482, Level 14, State 1, Line 1 Could not connect to server '...
-1
votes
1answer
27 views

Raid Reconfiguration of SQL Server 2016 Box

I did raid reconfiguration of SQL Server 2016 box. All my systems databases files (mdf and ldf) are in D drive and SQL Server installation also is in D drive and i have them intact. I have lost user ...
1
vote
0answers
10 views

When does the quarantinethreshold counter reset in WSFC cluster?

An outage that occurred on our SQL availability group this morning caused us to look into the quarantine settings on WSFC - something we never needed to configure. Now there seems to only be two ...
0
votes
1answer
33 views

Bulk Import appending the data to the data file

We have a bulk import task running in SQL Server 2016 which imports more than 5 million rows. The task itself is configured at cloud using third party services. The strange thing is that the new data ...
0
votes
1answer
51 views

how to convert from raiserror to throw in the current context?

I have a function that checks is the file is present on disk in an specific location. Using that function I am checking for the latest backup, in the code below. If we don't have a backup anymore, ...
0
votes
0answers
51 views

Data integrity in one-to-many distributed architecture

We are a retail shop that has a central data repository which collects data continuously from geographically distributed 1500 odd servers (each store has one SQL Server instance). Like a one to many ...
0
votes
0answers
43 views

IIS Causing High SQL Processes Load in SQL Server [closed]

Sometimes, - especially since last 7-10 days- there are hundreds processes are coming from a spesific web application which calls same stored procedure many times at the same time in my production sql ...
0
votes
1answer
34 views

upgrade MSDB only?

good morning. we currently are running on SQL server 2008 r2 and going to Sql server 2016. we are told we cannot do an in place upgrade and will have to restore all SSIS and jobs manually (script, ...
5
votes
1answer
82 views

How can SQL Server 2016 deadlock with a pagelock on two different tables?

I'm using SQL Server 2016 and running into a situation where a deadlock, via pagelock, is happening on two different tables. As I understand the database never shares pages across tables, so how is it ...
4
votes
1answer
78 views

Failed to allocate BUFs during DBCC CHECKDB

UPDATE 7/8/19 - The problem still persists, and I still need help if anyone has any ideas? 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 ...
4
votes
2answers
250 views

Way to find when system health file is rolling over

Is there a way to find the events when system health extended event files are rolling over rather than manually monitoring for the events? For my medium load server they stay upto 2-3 days. But for ...
1
vote
0answers
83 views

DNS lookup failed with error: '11001(No such host is known.)'

I have a server in the USA that should connect to one of our servers in Europe through service broker. The server in Europe is an Availability group, whose listener is called sqlst. when I run the ...