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

How to map the newly created records in parent table with the child table?

have a table Employees Id Name EmployeeType_ID 1 John 1 2. Mathw 2 EmployeeTypes Id TypeName 1 Perm 2. Temp 3. Regular Now I am copying data from one server to ...
user avatar
1 vote
1 answer
27 views

Reinstalling server and SQL Server database

Due to some Windows issue, can't clean-up the WinSxS folder and the Disk Space process (cleanmgr.exe) makes the CPU going to 100% meaning that I am at 97% of disk usage, I am planning to reinstall the ...
user avatar
1 vote
3 answers
45 views

Can someone help explain the specifics of why a DBA would be against database clustering?

I'm a system admin and do not manage databases on a daily basis. My organization is considering database clustering to practically eliminate downtime for certain services. We use mostly oracle ...
user avatar
-3 votes
0 answers
32 views

Could I have any script that will trigger an email alert whenever Deadlock occurs in SQL Server?

I am looking for a SQL Script or Powershell script that will generate an email alert whenever Deadlock occurs in SQL Server along with Deadlock XML or Graph file attached with email alert. I was using ...
user avatar
1 vote
1 answer
34 views

How to Select Distinct Group By With Exclusions

I have a large table similar to: The goal: For each UserID with a Y in the Access column, show UserID only as "Y" (exclude "N", "?") For each UserID without any Y's in ...
user avatar
3 votes
1 answer
91 views

Incorrect Result When Doing String Concatenation With ORDER BY [duplicate]

I'm try to understand why we are getting an incorrect result when doing string concatenation with order by. There are a series of combination that works and gives us a correct data. One weird thing is ...
user avatar
  • 170
0 votes
1 answer
60 views

DBCC CLONEDATABASE returns error: Cannot insert duplicate key row in object 'sys.syssingleobjrefs

I'm trying to run the following line of code: DBCC CLONEDATABASE ([DBName],[DBName_Staging]) WITH VERIFY_CLONEDB And I'm getting the following error: Msg 2601, Level 14, State 1, Line 15 Cannot insert ...
user avatar
3 votes
1 answer
102 views

Reducing Table Size after Clearing TEXT Column Precautions

I have 3rd part app with a table that's 450GB which contains file attachments in a TEXT column (yeah, I know). With a document retention angle, I've exported about 35% of the older attachments to ...
user avatar
  • 31
0 votes
2 answers
62 views

how to find out if I am in azure or not?

I am talking about sql server here. I can connect to azure. I can also manage permissions. But while doing some automation, I need to know if I am in azure or normal sql. how can I achieve that? I ...
user avatar
2 votes
1 answer
187 views

simulate case sensitive collation in ssms

I know that it is not possible to set a collation for a session in sql server. I have different servers with different collations, however, one of them is a case sensitive collation and that can be ...
user avatar
-1 votes
1 answer
111 views

NUMAnodes and SQL Server performance

Pretty sure I understand this, but wanted to be sure I do. We have a SQL Server 2016, that is running with 2 NumaNodes, each with 8 vCPUs. The Max Degrees of Parallelism (MAXDOP) is set to 8. This ...
user avatar
3 votes
0 answers
36 views

Evaluating Success of CTFP Value Change

We have recently made a change to the CTFP setting on our test server with a view to rolling this out to live. Our current CTFP is 15 so we tried setting it to 50 which is often seen as a better value....
user avatar
  • 1,463
0 votes
0 answers
34 views

Blocked process while deleting and inserting rows

I've a table which contains 282M records. Here's the structure: CREATE TABLE [dbo].[EntityHistory]( [EntityHistoryId] [int] IDENTITY(1,1) NOT NULL, [EntityType] [nvarchar](100) NOT NULL, [...
user avatar
0 votes
0 answers
31 views

How do I reattach a snapshot file?

We've been trying to use snapshots on one of our test DBs in our automated testing so that we return to the prior state after the tests are complete. The restoring of the snapshot appears to have ...
user avatar
-6 votes
1 answer
100 views

How to better do a implicit conversion (cast) for a join? [closed]

I have a strange query like this: SELECT T1.* FROM Table1 T1 INNER JOIN #TEMP an ON an.Id = T1.AccNum LEFT OUTER JOIN Table2 T2 ON CAST(T2.Ref_tranID as ...
user avatar
  • 7,156
6 votes
2 answers
671 views

Trigger on partitioned table and performance

I'm not sure if what I've found is a bug, but it certainly looks like it. I wasn't able to find much information about it, so I've decided I'll put it here. So, in a nutshell, I'm facing terrible ...
user avatar
3 votes
1 answer
325 views

Calculate median of values in T-SQL

I've got a piece of code (stored procedure) written in T-SQL that inserts into a table several data from a temporal table as shown here: INSERT INTO [myschema].[CalculatedData]( Year, Month, ...
user avatar
0 votes
1 answer
180 views

Adding 'top 20' massively slows down query!

I have a query: select * from Aview where field=20 order by id desc This returns 2700 rows from the view in about 1 second. Adding 'top 20' to the query makes MSSQL return in 43 seconds!! This has ...
user avatar
2 votes
1 answer
108 views

SQL Server PATINDEX issue/bug when using different case-sensitive collations

I have a function (which I found here years ago) that uses STUFF / PATINDEX to strip out non-alphanumeric characters from a string. When running on a Case Insensitive collation, it works fine. ...
user avatar
0 votes
1 answer
106 views

Query to find the count of active days (days with status = ON) between specified dates

I have a table as you can see below : create table z_test_duration ( Days date, Status char(8) ); Sample data is as below : Days Status 1/1/2022 on 1/2/2022 on 1/3/2022 on 1/4/2022 off ...
user avatar
  • 1,304
2 votes
1 answer
153 views

Can I delete these SQL Server files to clear up disk space?

We're looking for opportunities to clear up some disk space on a Windows 2019 server. We're running SQL Server on this instance as well. I see that I may be able to free up 10GB if we clean out the ...
user avatar
  • 123
1 vote
1 answer
57 views

Case sensitivity not working

I have an issue where I keep getting the value 'CtP_PETER_Fact' out of the query below. It should be a case sensitive where-clause. I tried it in a few different ways: setting the COLLATE statement ...
user avatar
0 votes
0 answers
41 views

bcp import from CSV producing strange results

I am trying to automate the monthly import of a CSV (created by pandas/Python) into a table in SQL Server 13.0.4001. There are 100k+ rows and 20+ rows in each import, and I do not have BULK INSERT ...
user avatar
0 votes
0 answers
60 views

SQL Failover Cluster - 'SQL Server' service terminating because of 'fatal exception c06d007e'

I have an SQL Failover cluster (2016) with 2 VMs. The 'SQL Server' service was down when seen from 'Failover Cluster Manager'. On checking the Event Viewer logs, the SQL Server showed below error. ...
user avatar
1 vote
0 answers
36 views

Peer to Peer replication ID number

I'm familiar with P2P replication conflicts which normally show along the lines of conflict detected between peer 1 (incoming) and peer 2 (on disk). The issue I'm seeing is that the conflicts are ...
user avatar
  • 11
0 votes
0 answers
29 views

Backup with Compression of large TDE databases

I'm looking for help with Backup Compression for TDE databases, I'm working out of SQL 2016 (SP3-CU1) and am aware that the MaxTransferSize must be specified as over 64K. A few these DBs are over ...
user avatar
0 votes
0 answers
27 views

How to use column names stored in another table?

I have a few tables in which I store some table names, column names, and some data. I need to get some column names from the content of these tables, and then use those names to create a bigger query. ...
user avatar
  • 135
0 votes
1 answer
37 views

How to generate ER diagram for specific tables in MSDB

I would like to generate ER diagram for tables in MSDB database, but in my SSMS (v18.6) I don't see Database Diagrams folder which is common for other databases. Is it something related to the ...
user avatar
  • 825
0 votes
1 answer
63 views

No column name was specified for column 1 of 'd'

I am trying to get some data from 3 tables in SQL Server, but getting an error. I have created a fiddle with similar structure and sample data Based on the data in tblData, I am trying to get the ...
user avatar
  • 135
-3 votes
2 answers
44 views

How to get numbers that have IE in case of data type float?

I work on sql server 2017 i face issue when save data on float data type it display as IE so how to get data only that display as IE on float data type CREATE TABLE #TestTable ( ...
user avatar
0 votes
2 answers
40 views

AlwaysOn Availability Group creation - Is it possible to delay the adding of a secondary replica?

Currently I have a windows failover cluster with a single node only, because the second node will only become available on an production go live date. Prior to this, I was wondering if it would be ...
user avatar
2 votes
0 answers
74 views

Lined Server - Cannot create an instance of OLE DB provider "IBMDADB2.DB2COPY2" for linked server "MYDB2"

I would like to create Linked Server from SQL Server 2016 on Windows to Db2 v11.5.7 on Linux/x86. I created linked Server using Microsoft OLE DB Provider for DB2 and it works fine. Now I would like to ...
user avatar
  • 99
4 votes
0 answers
53 views

Log Backup occasionally blocked by automated update

We have a canary table in each of our databases that our automation updates once a minute. The automation is a sql server agent job that utilizes a cursor to iterate over the databases and then ...
user avatar
  • 2,968
4 votes
1 answer
124 views

How does SQL estimate the number of rows in a less than < Predicate

I have been doing some testing to try to better understand how SQL Server uses a histogram to estimate the number of rows that will match an equality predicate and also a < or > predicate Given ...
user avatar
  • 1,463
0 votes
1 answer
101 views

MS SQL Server 2016 SP3 upgrade failing

I tried to apply the SP3 on one of my SQL Server 2016 DB server, and it kept failing with the below error. Error description: The installer has encountered an unexpected error. The error code is 2203....
user avatar
10 votes
0 answers
227 views

Has SQL Server not Killed a Deadlock?

I have encountered a situation where a user has run something in one of our applications which runs a stored procedure. The stored procedure does a few bits of logging (INSERTing to a log table) and ...
user avatar
  • 1,463
0 votes
1 answer
87 views

Trigger execution order after delete

Let's say I have two tables, one called ParentTable, and another called ChildTable, with a one-to-many relationship, where one Parent may have many Children. I'm creating an AFTER DELETE trigger on ...
user avatar
  • 139
2 votes
1 answer
84 views

Why Does SQL Server not have 200 buckets in the statistics histogram when there are >100k distinct values in the table

Given I am using the AdventureWorks2016 OLTP database why does the statistics histogram for the index PK_TransactionHistory_TransactionID on table Production.TransactionHistory only contain 3 ...
user avatar
  • 1,463
-3 votes
1 answer
95 views

How to convert data separated pipe to rows?

I work on sql server 2017 I have table name #countriesData data as below my issue here How to convert countries column data separated pipe to rows . meaning every rev and company and year will have ...
user avatar
1 vote
1 answer
83 views

DBCC CHECKDB Error 2706

A CHECKDB job has recently started returning error number 2706 for 2 databases (msdb and a user database). The msdb.dbo.suspect_pages table is empty though. When I run CHECKDB manually it says: ...
user avatar
  • 23
3 votes
1 answer
209 views

Always On Availability Groups connection with secondary database established for primary database

I'm seeing this messages almost everyday in one of the production server. I don't feel comfortable seeing this..is there anything we can do to suppress this messages ? though it's showing No user ...
user avatar
15 votes
5 answers
3k views

Can ransomware embed itself in to a SQL backup file?

One of the best protections against ransomware is to back up all of your database files to a completely separate system. Which we have done. But one thought is the backup of the database could ...
user avatar
0 votes
0 answers
143 views

AG node getting "misaligned log IOs which required falling back to synchronous IO" events in error log

SQL 2016 AOAG. 3 node cluster. Primary and 1 secondary on-prem configured with synchronous availability mode. 1 node in Azure using asynchronous availability mode. We are using AOAGs to move data ...
user avatar
2 votes
2 answers
43 views

Find Which Queries will be affected by Proposed Change to CTFP

The Cost Threshold for Parallelism Setting on one of our servers is set at what is generally considered too low (15) and we are considering increasing to 50 in hope to reduce CPU as it is becoming ...
user avatar
  • 1,463
0 votes
1 answer
45 views

In Always on AG How can we setup jobs with a failover

In SQL Server 2019, We have primary and secondary replicas. if primary goes down then secondary will become the primary server, In such case how to handle the SQL jobs. Please help me to get it ...
user avatar
6 votes
2 answers
525 views

DateTime2 and Page Life Expectancy (PLE)

As I understand it, when you define a column on a table you define its precision. This precision takes 1 byte and is stored at the column level. If you use a precision of 5 or more, then a DateTime2 ...
user avatar
  • 2,404
4 votes
1 answer
80 views

SET SINGLE_USER WITH ROLLBACK IMMEDIATE only disconnects sessions when account is a database user

The following code creates a user that is able to restore a database: CREATE LOGIN RestoreUser WITH PASSWORD = 'MyPassword' ALTER SERVER ROLE dbcreator ADD MEMBER RestoreUser This works fine and the ...
user avatar
  • 1,463
0 votes
1 answer
37 views

What does it mean to reach a stable point during the execution of a job?

Trying to use sp_stop_job to stop a simple job that inserts data on a table when 100 rows were inserted I got confused to see the table end up with more than 100 rows every time. Under the Remarks ...
user avatar
  • 4,589
0 votes
1 answer
47 views

upgrading sql version with ssis and ssrs on that server

If I upgrade a SQL server 2016 STD to ENTERPRISE and that server has the following services installed: SSIS and SSRS can I just upgrade the version of the SQL and it will automatically upgrade the ...
user avatar
  • 325
1 vote
2 answers
224 views

SQL Server Backup Jobs Failing. The error seems to be related to MaximumErrorCount, but I am not sure how to fix value or if changing it will resolve?

My backup jobs are failing with following error when check it's history. I have already checked this link regarding maximumerrorcount value: SQL Server 2008 - Change the MaximumErrorCount or fix the ...
user avatar

1
2 3 4 5
53