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

SQL Server 2016 Audit Actions, Can DatabaseAuditSpecification capture param values used in statement?

I am using Schema_Object_access_Group audit action type. When I run a select, insert, update SQL statement, it records all statements nicely. For example, captured Sql statement looks like : UPDATE ...
0
votes
1answer
29 views

Transactional Replication failing between SQL Server 2012 and SQL Server 2016 versions

As per the Microsoft documentation - A Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example: a SQL Server 2012 (11.x) Publisher can ...
0
votes
0answers
15 views

Error i updating SP2- CU11 on MS SQL server 2016

I am trying to update SP2-CU5 (KB4475776) to SP2-CU11 on Microsoft SQL Server 2016. I am getting below error again and again. Please suggest the possible resolution for this error.
-1
votes
1answer
31 views

SQL Job runs without it schedule and renaming or deleting it causes other jobs fail

I recently migrated the SQL 2008 to SQL Server 2016. I started noticing a strange issue where one of the SQL Job is causing the failure of other jobs. This job is disabled and there is no schedule and ...
3
votes
0answers
54 views

Why recovery took longer time even though analysis phase is not that long

We are doing Volume Snapshot restoration from Production to Warm Stand-By Server on daily basis. Daily it takes around 10 mins for DB to recover. But yesterday it took 44 mins to recover. I am ...
0
votes
0answers
18 views

How can I create a database diagram, showing clearly the FK and PK in SQL Studio 2016?

I would like to know the best way to create a database structure diagram, that shows relationships between certain tables. I am using SSMS 2016. I tried using "database diagram" option in the SSMS ...
1
vote
2answers
49 views

Is it possible to restore a database with database backups starting on top of a restored server point in time snapshot that is out of date?

If I regularly backup my whole server where a SQL instance and database lives, and restore it to a different machine, can I use the database backups that have occurred (on the original server) after ...
5
votes
2answers
92 views

More than 64 parameters to WHERE IN sentence makes MSSQL reading far too many rows

I have some queries where I need to get more than 64 specific rows, like this example with 65 IDs. TableID is primary key, type BigInt. SELECT * FROM TableA WHERE TableID IN (260905384, ...
1
vote
1answer
42 views

SQL Server Availability Group - Listener configuration problem

Platforms: Windows Server 2016 SQL Server 2016 SSMS 2018 Problem: Create failed for Availability Group listener 'lsn'. None of the IP addresses configured for the availability group ...
0
votes
0answers
90 views

Could not find a login matching the name provided

SQL Server 2016 We had a user on several servers that had sys admin. As a new DBA, I was told by my director to remove the user and create a new one to run jobs and do server linking. I removed the ...
0
votes
2answers
41 views

SQL Server 2016 Express, need to add account with Administrator access to a database

Working with a new SQL Server 2016 Express installation. I loaded SSMS and have Administrator access to the instance. The vendor supplied a database for a new app that has only a couple internal ...
-1
votes
1answer
38 views

General Question as best practice to collect data from extended events

Hope ya all doing good and safe:- I am collecting query level metrics from extended events using below events for - SP statement completed SQL Statement completed SQL Batch Completed RPC Completed ...
0
votes
1answer
11 views

Is it possible to setup multiple RW Replicas for a database in an AlwaysOn Availability Group?

Can I have multiple RW replicas of an AlwaysOn Availability Group database across different servers? (I.e. two-way replication so that multiple servers can write to the same DB.)
0
votes
1answer
17 views

Granting access to users to run specific SQL jobs that runs SSIS packages [duplicate]

We need to grant a couple of users access to run only specific SQL jobs that runs SSIS packages. Is there a solution that can be used to accomplish this?
1
vote
1answer
41 views

How can I script administrator permissions on a SSAS server?

I am an administrator for a bunch of SSAS Instances. Sometimes I need to install or migrate SSAS instances to different machines or even different AD domains (we have a couple of different AD domains ...
-3
votes
2answers
77 views

Why does IO Statistics show a lot of LOB Logical Reads on the Worktable when none of the tables in my query use any LOB datatype fields?

I have a procedure that runs a query dynamically across a few hundred databases that all have the same exact schema and aggregates the results to a temp table. The query only involves 3 tables (each ...
1
vote
1answer
29 views

64 Availability groups max on SQL STD : Failure when attempting to create a new NetBIOS interface while bringing a resource online

I was patching our DB Servers this morning and encountered a really strange error when failing over some Availability Groups : In our environment, we are running a somewhat unconventional setup, but ...
-1
votes
1answer
52 views

Restore a single SQL Server Temporal table, including the PERIOD columns?

In testing a data cleanup script, several rows have been deleted. The table has a temporal pair, but SYSTEM_VERSIONING was off at the time, so the history didn't save. There is a backup, which has ...
0
votes
2answers
34 views

Backup Files growing quickly on SQL Server 2016 Always On Cluster

I have a two-node SQL Server 2016 Always On Cluster. I also have a job that updates about 200 records in one table every 3 minutes. Even when there are no updates, it does change a TimestampDt field ...
-1
votes
1answer
33 views

Microsoft SQL Server Database Miroring

I have few questions about Microsoft SQL Server database Mirroring. Fail over of database is Automatic or Manual in case of Microsoft SQL database mirroring? In case of failure in Primary database ...
1
vote
1answer
74 views

What is the best way to include a large amount of configuration data in a SQL project?

I have a BIDS SQL Server Database Project with a database that I publish dynamically to various servers (SQL2016). It initially starts blank and everything is parameterized. Everything is in Azure ...
0
votes
0answers
21 views

000 Failed to retrieve job from the server error

I see following errors in SQL Server agent log There are no maintenance plans on the server Didn't find anything regarding those in sysjobs, sysjobschedules, sysjobhistory Also those ids are different ...
1
vote
0answers
20 views

SQL Server Management Data Warehouse creates a lot of sleeping user connections

I am wondering if someone already experience this weird scenario regarding my SQL Server Management Data Warehouse; it creates a lot of user connections. Around 2500 connections which were all ...
4
votes
1answer
245 views

SQLServer 2016 continually increasing stolen memory

We are experiencing a slow growth of Stolen Memory on our database servers over the space of several days. It appears to plateau around 130-140GB, at which point we start having larger problems such ...
0
votes
0answers
19 views

Generate integer on SQL insert with unique constraint to value in another table

I have two tables: Assuming a user already exists, when I insert a vehicle I want the unique_number field to auto-populate with a value between 00000-99999. It only has to be unique based on the ...
2
votes
1answer
178 views

Grant Permissions to all databases using server role

I know this question has been asked many times but they are all 3+ years old so I'm going to try my luck by asking again. I need to grant Read permissions to all databases on MSSQL server. I would ...
0
votes
0answers
22 views

Recursive sum of children with calculated column

I'm trying to create a recursive query to determine the total cost of a multi-level bill of materials (kind of like a baking recipe list, that has sub-recipes). The top level bill of materials could ...
0
votes
1answer
20 views

SQL Server 2016 Log shipping restore job stuck

SQL server 2016 log shipping restore job will run every 15mins, Database size is 1TB, Job will not take two minutes, Yesterday job was running for last 13 hours and the database is in "InRecovery" ...
0
votes
1answer
40 views

sqlcmd returns Msg 701 when importing +10MB queries

I have a powershell script that exports data from DBF files, saves them as .SQL files and then imports the SQL files into MS SQL 2016 using the sqlcmd command. There's one SQL file per table in my ...
-1
votes
1answer
73 views

Use a temp table's result in a view [closed]

I am using a temp table in a stored procedure. I need to use that temp table's result in my view. How can I do that?
0
votes
1answer
148 views

Does Traceflag 1800 require a restart?

We have recently migrated our database mirroring secondary to new hardware. The log drive (and other SQL Server drives) on the secondary has been formatted to 64KB block size, whereas the primary ...
0
votes
0answers
16 views

SQL 2016 - TDE Restore Failing Even After Certificate Restore

I'm trying to ship some TDE enabled database backups from one server to another and I'm having a problem. Specifically, I'm getting the following error: RESTORE FILELISTONLY FROM DISK = N'<path>...
0
votes
1answer
109 views

How to Set Recovery Mode in SQL Server Always On Availability Group without downtime?

I am running a SQL Server 2016 2-node Always On Availability Group, and I want to do some backups. I normally run this query to do my backup: -- To permit log backups, before the full database ...
1
vote
1answer
31 views

how to script out proxy accounts?

I have servers where I use proxy accounts to run ETL processes or even to backup SSAS databases using powershell through a job. I can see what AD Accounts are related to my proxies: use msdb go ...
0
votes
1answer
28 views

Configuration file missing after successful SSRS migration

Getting the backup of all SSRS databases including compressing and encrypting can be straight forward. However, when planning a complete solution for SQL Server Reporting services backup, following ...
0
votes
1answer
23 views

Permissions required to execute UDF Scalar Function inside a trigger

I have a scalar function that user don't have execute permissions on. However the users have select , insert, update , delete permissions on a table that has a trigger and the trigger calls the scalar ...
0
votes
2answers
41 views

How to change/add to schema in a SQL Server Always On Availability Group?

I have a 2-node Always On Availability group cluster in SQL Server 2016. I am wondering how should I go about adding a new table, or modifying the schema of an existing table in an Always On ...
1
vote
0answers
42 views

Can I do a combination of SQL Server Basic availability groups with SQL Replication?

I need to use SQL Server Standard 2016 to keep the costs low but achieve HA. In addition we need to have two Read nodes for reporting purposes. I am wondering if I can use SQL Server Replication to ...
0
votes
0answers
4 views

Un-publish a database with no publications

I'm trying to remove all replication from a SQL Server instance. There are no subscribers. There is one database published for snapshot replication: SampleDb According to sp_helppublication there ...
0
votes
1answer
34 views

The service broker is administratively disabled

I have just restored some databases what use service broker from LIVE to staging. it is not working and when I put a profile trace to run it only shows messages related to database id 1 and 4, ...
1
vote
1answer
460 views

Activity Monitor paused

Some of our end-users still like to use the Activity Monitor in SQL Server. When setting up our SQL-server 2016 servers the activity monitor always ends up in a paused state when the users are not ...
0
votes
0answers
102 views

Cannot run JSON_VALUE in SQLite database (.db file) in SQL Server Management Studio

I want to update some JSON data in SQLite database (.db file) and I met a problem. I work in Microsoft SQL Server Management Studio where I added SQLite/SQL Server Compact Toolbox through Visual ...
0
votes
0answers
16 views

Select last transaction and “pivot” results

There are two tables, billing and billing_details containing all pertinent details. I am attempting to produce a list of the most recent transaction per each customer with certain details of the ...
2
votes
2answers
115 views

Find specific query in Query Store

I have enabled Query Store on a database. I have a specific query which I want to track. I have a lot of details about the query from sp_BlitzCache (like SQL Text, SQL Handle, SQL Hash, Plan Cache ...
0
votes
0answers
16 views

SQL Server Group Managed Service account

Is it the best practice to use same gMSA account for both SQL Server and SQL Agent services? or should I use separate gMSA accounts? I couldn't find any Microsoft documentation on this specifically. ...
0
votes
0answers
114 views

Generic “An Error Has Occurred” When Editing SSRS Subscription - No Parameters Appearing

I'm relatively new to SSRS Reporting. I'm attempting to manage a data-driven subscription for a two-part report on a SQL Server 2016 Developer instance that is acting as a dedicated SSRS server. At ...
3
votes
1answer
50 views

Reporting table needs to get refreshed without killing user's read connection to it, any ideas?

I'm investigating a solution that will allow a reporting table to be refreshed that can potentially have users reading from it at a time that it needs to be refreshed. Has anyone implemented a way of ...
0
votes
1answer
27 views

Is it possible to restore a database with only database roles membership?

I have an existing database, that i want to restore from a backup by overriding the existing database. at the moment I have db_owner db_backupoperator roles I thought that would be enough to ...
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 ...
-3
votes
1answer
27 views

Reformatting Date with Pivot or Unpivot [closed]

I'm struggling trying to covert the format of a table with the following fields: Customer|Fiscal Year| September Amount| October Amount|November Amount|... to: Customer|Fiscal Year|Month|Amount I'...

1 2
3
4 5
46