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

Specific format/formula when changing column datatype

Can one modify a column's datatype using a very specific conversion format? For example, when converting from datetime to varchar I'd like to use the yyyy-MM-dd hh:mm:ss format, not the system ...
0
votes
0answers
16 views

SQL Server: Cannot connect remotely after changing server name successfully

My local computer's operating system is Windows 10 Professional. We have a remote server named XXXXAD01. Its operating system is Windows Server 2016 Standard. It is part of a Domain ISYYYY.ZZZZZ. ...
0
votes
0answers
9 views

In SQL Server, data returned by sys.dm_os_performance_counters for “SQLServer:Broker Statistics” is not changing over time

In SQL Server [Microsoft SQL Server 2016 (SP2-CU10) (KB4524334) - 13.0.5492.2 (X64)], I'm trying to determine just how much activity our Service Broker queues are processing. One tactic is to use sys....
0
votes
1answer
28 views

Trouble with differential backup - full backup not being recognized

I have recently implemented backups using https://ola.hallengren.com/. Backups EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES, -%Archive', @Directory = N'H:\sqlbackup', @BackupType = '...
1
vote
0answers
23 views

DBCC CHECKTABLE - 2570 error

Running DBCC Checktable on one of my tables causes several errors: Msg 2570, Level 16, State 3, Line 1 errors, Page (1:442), slot 1 in object ID 565577053, index ID 0, partition ID ...
-1
votes
0answers
42 views

Uninstall SQL Server 2016 [closed]

I have SQL Server 2012 and SQL Server 2016 standard named instance on same server and we would like UnInstall Sql 2016 t and Install Sql 2014, what is the best way to uninstalling process as sometimes ...
0
votes
1answer
29 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 ...
1
vote
1answer
53 views

Need advice on how to migrate from existing database mirroring to availability groups

Need some practical/general guidance on moving from database mirroring to AOAG Current setup - 2 standalone servers with database mirroring configured as async. Server A: Windows 2016 running SQL ...
1
vote
1answer
28 views

Where Database Alerts & Operators Exist In SQL Server?

My SQL Server database server has crashed. I have system databases backup. I will reinstall the sql server but i don't know how to find my operators, alerts and config settings. Is there a way to find ...
2
votes
1answer
33 views

How to get certificate expiration date for certificate in mirroring endpoint in SQL Server 2016 Always On Availability Group?

I created a domain-independent 2-node SQL Server 2016 Always On Availability Group using the Microsoft instructions: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/...
0
votes
0answers
31 views

Index rebuild not finishing and Log not growing anymore [closed]

I have a maintenance job running on my database (sql server 2016) which does an index rebuild on all tables including one large table ( 6 GB / 150.000.000 rows). I ran the job several times in the ...
1
vote
0answers
29 views

SQL Server: calculating memory requirements for Full db backups

In 2018, we inherited a production SQL Server 2012 FCI running on Windows 2012 with 32 GB RAM. SQL Server max server memory was set at 23.6 GB, and things were running fine. However, in 2019, we ...
0
votes
2answers
26 views

How can I track down an issue with a locked table, endless query?

I have a table that is periodically getting locked in a manner that I don't quite understand. I cannot do the following: select * from thetable select count(*) from thetable There are roughly 2,000 ...
0
votes
1answer
20 views

Cost of indexed view versus filtered index?

I have met the following table design for recording references in non-normalized way: RecordGUID uniqueidentifier DestinationEntity uniqueidentifier DestinationRecordGUID ...
0
votes
2answers
19 views

How to check if staff member has end date with a Null value

I have a database that records employees tasks throughout the organisation and when they finish etc., I need to identify if each employee, has at least one row with a null value in either finish. ...
1
vote
2answers
28 views

What are the benefits of using Manual Failover Mode in an AlwaysOn Availability Group instance as opposed to Automatic Failover Mode?

Why wouldn't I want to use Automatic Failover as the Failover Mode for an AlwaysOn Availabiltiy Group instance? Are there pros for Manual Failover Mode that don't exist for Automatic Failover Mode?
0
votes
1answer
30 views

Backup up database to compressed local folder - what will be the performance implication?

I have sql server 2016 web edition on Windows 2016 server VM. I want to backup the database, to a folder on a local drive (SSD) - please don't ask why, long story. Due to the size of the DBs, and ...
0
votes
2answers
74 views

T-SQL Query or DB Engine Optimization? [closed]

This query takes 100 ms on avarage but when you have hundreds or thousends of calls of them every second this would cost a lot. The dataset can not be cached as it may be changed at any second. Any ...
1
vote
1answer
22 views

How do you minimize chance of collision between system-named constraints on temp tables?

We have occasionally hit the error “There is already an object named 'PK__#TempTab__0796211ACE71813B' in the database” when some of our procedures attempt to create temp tables with a system-named PK ...
0
votes
1answer
27 views

SQL Server AlwaysOn Availability Group - Creating an Index Online in SSMS

I am running a 2-node SQL Server 2016 AlwaysOn Availability Group. I would like to create a non-clustered index online in SSMS for one of the availability databases in the AlwaysOn Availability Group....
10
votes
2answers
560 views

Break the link between a Database User and SQL Server Login

When restoring database from once instance to another one sometimes has to relink SQL Server Logins to the Database User. This is normally achieved with one of the following procedures. Deprecated ...
0
votes
1answer
51 views

Determine the source of user permissions in a database

I have a developer that just accidentally ran an UPDATE and ALTER TABLE script he was working on in a production database instead of the development environment. How can I track down what security ...
6
votes
4answers
487 views

How can I configure Ola Hallengren IndexOptimize script to run against a subset of tables

I'm using Ola Hallengren IndexOptimize script against a SQL 2016 database that is 7 TB in size, with over 300,000 tables. I only have a 6 hour window each night to manage indexes. I'm using the ...
1
vote
0answers
26 views

Index Exists, data is present, but statistics are empty. Why? [duplicate]

I’ve created a dump of all statistics on all tables in a Production database (based on the three sets returned by DBCC SHOW_STATISTICS). The database has been up and running for several months, and is ...
0
votes
1answer
32 views

Logon failed for login due to trigger execution / LOGON TRIGGER

I have this situation. I created a logon script to block any session for specific logins. These logins are in a table. So the behaviour of my script is simple. Get the login from the table, compare it ...
1
vote
1answer
69 views

Script to monitor database growth on daily basis

I am fairly new to databases, I have got a specific requirement to script a Job/Stored Procedure to monitor the database growth. We are currently using MS SQL Server 2016 Standard Edition with ...
0
votes
1answer
31 views

Multi-Subnet Always On with Availability Group for DR [closed]

I researched a lot of mutli-geographical DR - Disaster Recoververy solution architectures for MSSQL and most of the proposed solutions I came across recommends HA using 2 nodes Failover clustering in ...
0
votes
0answers
22 views

Correct format to combine text and columns to create a new column? [duplicate]

Long story short, my company's system creates a "keyword search" column when we enter in new stores manually. When they need to add 100's of stores, they have me insert everything via SQL. I can do ...
13
votes
1answer
1k views

SQL Server Spill level 0 - what is it?

I was asked to look into performance of a query in SQL Server 2016 SP2, and I found something that I have not seen before: <Warnings> <SpillToTempDb SpillLevel="0" SpilledThreadCount="1" /&...
0
votes
1answer
25 views

Subqueries for JSON path where outer query is part of a UNION result in strings rather than JSON

Bear with me - the example code has bit going on and I'll do my best to explain it, below. with ENTITIES as ( select [name] as entityName from (values ('A'), ('B'), ('C')) X([name]) ), PROPERTIES as (...
0
votes
1answer
18 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
28 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
14 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
53 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
48 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
88 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
38 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
13 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
34 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
26 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
10 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
21 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
66 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
40 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
2 3 4 5
45