Questions tagged [sql-server]

All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.

Filter by
Sorted by
Tagged with
1
vote
1answer
21 views

How to track down app name for query in query plan cache?

I found a query in the plan cache that needs parameterizing. Not sure which app created this query. Is there a good way in SQL to track down the app that put a given query in cache? sql2014
4
votes
1answer
70 views

Why a dropped index is still there, and does it prevent a table from being altered?

I have a column (named RequestDate) which I need to change from NULL to NOT NULL, but there's a clustered index on that column, so I first need to drop that index. Originally, the index was created ...
2
votes
1answer
28 views

What are the undocumented values for referencing_class in sys.sql_expression_dependencies?

In SQL Server, the sys.sql_expression_dependencies system catalog view "Contains one row for each by-name dependency on a user-defined entity in the current database", to quote the docs. One ...
2
votes
1answer
521 views

Why the sudden increase of my database .mdf file size?

Suddenly my database .mdf file size is increased to more than 250GB but my total table data size count is 4GB only. I did a data shrink, but there are no results. Then I did the query below: ...
0
votes
2answers
31 views

SQL Server RDS Database Restore from S3 stuck in Restore Mode

Summary: We are doing a restore of a SQL Server database from S3 on an RDS Instance. And all though the job will say the restore is complete when we go to access the database it's stuck in "...
0
votes
3answers
33 views

BULK INSERT continue on PRIMARY KEY error

I get a CSV file with a several million records every few days. I need to insert them into our database. What I do is quite straightforward: BULK INSERT dbo.activity FROM 'C:\tmp\activity.csv' WITH ( ...
1
vote
1answer
20 views

SQL Server BULK IMPORT Truncation error

I am trying to import a simple CSV file into a table on SQL Server 2017. The CSV file and table match each other in number of columns and data types. This is an extract of the CSV file: UID,...
1
vote
1answer
32 views

where is memory going in In-Memory OLTP?

I have only memory optimized table in my database. When I am running below query, I am getting pages_mb for that DB as 710 MB. SELECT type , name , memory_node_id , pages_kb/1024 AS pages_MB ...
1
vote
0answers
31 views

Problem with unmasking a database user during a sproc

I am having an issue with a stored procedure that refreshes dynamic data masking on a database. This sproc is run as a job against all databases on this server. The problem that I have is that I have ...
1
vote
1answer
48 views

Non-clustered index with WHERE clause

I have a large table (around 100 million rows). I am trying to optimize searches. Most of my searches use two columns: bar nvarchar(64) and foo nvarchar(64). I am only interested in foo when: foo = '...
0
votes
1answer
39 views

SQL Server second transaction log seems to be used as well - not only after first one is full

we have come across a strange phenomenon using a second transaction log file for a database. Microsoft SQL Server 2016 (SP2-CU7) (KB4495256) Standard Edition. On Windows Server 2016, a single instance,...
3
votes
2answers
267 views

Replication in sql server 2019? [closed]

I am reading 'Pro SQL Server 2019 Administration' book from Carter, and when detailing HA and DR options, 'replication' is not mentioned: Why is it omited? Will Replication be deprecated soon?
0
votes
1answer
16 views

Configuring 2017 SQL Server SSISDB Server-wide Default Logging Level does not seem to work

Using SSMS, I have changed the SSIS Catalog Logging Level from Basic to Performance to a Custom Logging Level in a 2017 SQL Server Instance and then I run the report: "All Executions". It ...
1
vote
1answer
39 views

Assign Failover Cluster and High Availability Always On from Old to New Domain

What are best practices steps when the Windows Server 2016 machine hosting SQL Server and failover cluster (2 node and shared folder witness) is joined to another domain? What steps definitely/might ...
0
votes
1answer
30 views

In Memory OLTP causing OOM Issue in SQL Server even though no data in In Memory OLTP Table

We have only 1 memory optimized table in each database and there are total 10 databases as such. This table is completely empty. But still we can see in DBCC MEMORYSTATUS Output that In memory OLTP is ...
1
vote
1answer
58 views

SQL Server restarts, unable to find the reason

One of our SQL Server instances restarts itself in a clustered environment, but it is not fail-overed. When I checked the error logs, event viewer and cluster log I'm unable to find any particular ...
0
votes
0answers
15 views

Are “xxx/sec” performance counters in sys.dm_os_performance_counters actually rates or just counters? [duplicate]

I am trying to track SQL Server metrics using Prometheus by querying the system dynamic management views. I am looking into the Buffer Manager: Page reads/sec counter in sys.dm_os_performance_counters,...
0
votes
2answers
43 views

Tuning Clustered Indexes in case of Soft / Logical delete

I have a table (say dbo.AppUsers) with a clustered index on the always increasing, application generated column AppUserId. I have also a Bit column IsDisabled, that is used to logically delete/disable ...
0
votes
0answers
27 views

Affinity side effects / TF 8002- how to measure

Environment: We have Microsoft SQL Server 2016 (SP2-CU15) Standard Edition (64-bit) on Windows Server 2016 Standard 10.0. We have multiple SE instances on one physical server. We use processor ...
0
votes
2answers
66 views

When no index is faster than having a clustered index?

I ran the same query, which involves joining two tables A and B, when B had no index at all and then added a PK clustered index on the ID column used for joining to it. The time elapsed when there was ...
2
votes
1answer
27 views

DBCC CheckDB : Operating System Error 27(The drive cannot find the sector requested)

So my regular DBCC check had this lovely message waiting for me in the error logs this morning. I've been trying to find out more about this issue and what can be done about it. Manually running DBCC ...
2
votes
1answer
34 views

log send queue size and redo queue size in AG

I am trying to figure out the way to monitor these 2 events log send queue size - I can see this in perfmon Redo queue size - I can see in dmv but not in perfmon counter Is there any way of using ...
4
votes
1answer
88 views

Limiting sp_whoisactive output

All, I am currently putting together a knowledge based article that we can hand to our helpdesk to run through in order to do some preliminary troubleshooting or information gathering. One of the ...
0
votes
0answers
28 views

Understand the relationships between tables and external keys

Suppose you're working in a company as a software deployment and support. And they put a database before you, and you have to report some pools, how can you quickly understand the relationships ...
2
votes
1answer
75 views

Basic alternative to fn_LocalTimeToUTC

My cloud provider has prevented me accessing fn_LocalTimeToUTC and fn_UTCToLocalTime. How would I go about getting the same functionality that these two functions offer manually and directly in SQL ...
4
votes
6answers
1k views

Why is there automatic failover for HA and manual failover for DR?

I am reading this summary from the book 'Pro SQL Server 2019 Administration' from Carter: And it specifies: hot → automatic failover → used for high availability (HA) warm → manual failover → used ...
0
votes
2answers
35 views

Always On Synchronous Commit Mode With Large Delays [duplicate]

For SQL Server Enterprise's Always On availability groups in Synchronous Commit mode, is there some amount of latency that can't be exceeded? For example, can it work between very distant data ...
2
votes
1answer
42 views

Deadlock and schema modification lock

I have a deadlock that I need some input for <deadlock> <victim-list> <victimProcess id="process4c86a61868" /> </victim-list> <process-list> <process id=&...
0
votes
1answer
49 views

How to fix below blocking problem on SQL server 2014

I am having a strange blocking problem which i am not able to understand. During a slighter more volume due to some app pooling issues we see sleeping spids with open transactions, so those SP's tend ...
0
votes
1answer
38 views

SQL Server Ubuntu server - failure: remote connection from Win 7

Our analytical lab (chemistry) has an information management system (LIMS) that uses a Microsoft SQL Server database. Technicians interact with the system through a Windows form application that is ...
0
votes
0answers
81 views

Yet another SQL Server calendar table question

After the wonderful help I received on my original question, we have discovered that our current method of calculating our fiscal calendar is wrong. Fiscal calendar rules: fiscal year starts 2 ...
0
votes
1answer
43 views

Subtotals per category not showing data (all subtotal columns show NULL)

In my query I have one last obstacle I'm trying to overcome. My goal is to group products ("prod") by the specified category ("prodcat") and have a subtotal at the end of each ...
2
votes
1answer
38 views

Dynamic Data Masking Issue when Concatenating Fields

You can reproduce the issue here: CREATE TABLE [dbo].[EmployeeDataMasking]( [RowId] [int] IDENTITY(1,1) NOT NULL, [EmployeeId] [int] NULL, [LastName] [varchar](50) MASKED WITH (FUNCTION = '...
3
votes
1answer
38 views

Azure Readable Secondary - How to: Different Access Policy to Primary?

We have primary databases. They are in a mix of elastic pool, and standalone. We have successfully created active geo replicated secondaries. (Our use for these is solely workload isolation.) We want ...
0
votes
0answers
38 views

How can I call a SQL Server stored procedure from Oracle and retrieve a scalar return value?

I'm developing a PL/SQL solution in Oracle Database Enterprise Edition 12.1.0.2. I'm calling stored procedures in Microsoft SQL Server 2014. I am using DG4ODBC with the DBMS_HS_PASSTHROUGH package to ...
0
votes
1answer
44 views

Timeout for Always On Synchronous Commit Mode

For SQL Server Enterprise's Always On availability groups, how can you configure the timeout for Synchronous Commit mode? Is there a maximum? Can it work, for example, between continents? Thanks
0
votes
0answers
70 views

IF @@trancount > 0 COMMIT TRANSACTION Causing blocking

We have a Java application / hibernate connecting to SQL Server. It looks like implicit transaction is on by default. I am not sure if this is from hibernate or JDBC Driver . However this implicit ...
2
votes
2answers
47 views

Gaps and Islands

I have a table with StartDate and EndDate. I wanted to get the missing Date and The script I have does it. But if given this data: LocationID StartDate EndDate ---------- ---------- ---------- ...
0
votes
0answers
19 views

Complex rights structure for users/members and keeping it fast

We are working on a database that holds various information about 15.000 members in a complex structure. These members are managed/viewed by about 1.000 and we need a system to handled rights for ...
0
votes
1answer
25 views

SQL Authenticated Account is unable to select a View in MS SQL Server

I am trying to select a view using a SQL authenticated account. This view is actually reading a spreadsheet from a local drive (through a LinkedServer). I am getting below error: Msg 7416, Level 16, ...
0
votes
1answer
59 views

MSSQL - Should I change min server memory to 0

I Works as a DBA and "inherited" about 50 instances (all server has about 8GB-24GB of RAM), all of them are configured Min Server Memory equal to Max Server Memory, and 4GB RAM reserved to ...
0
votes
1answer
25 views

How to export Sql Server DB schema as an ALTER statements?

I have a 2 months old DB & I want to fetch new Schema for that DB without deleting the data in it using ALTER statements. Is it possible? Currently I am taking a backup of table data of Old DB in ...
0
votes
0answers
38 views

HADR_SYNCHRONIZING_THROTTLE Waits

We recently migrated a large chunk of our databases to a new data center and on one of our clusters we are now seeing high amounts of waits for HADR_SYNCHRONIZING_THROTTLE. We didn't see this in our ...
13
votes
3answers
2k views

Colleague says to never use an OR statement in SQL, is this true?

My colleague now responsible for SQL development says that I should never use an OR statement because it will mess up the query optimizer and ignore table indexes producing slow queries. I couldn't ...
2
votes
1answer
46 views

Dynamic Data Masking Doesn't Seem To Work Correctly With ISNULL

Here is the code to reproduce the issue: CREATE TABLE [dbo].[EmployeeDataMasking]( [RowId] [int] IDENTITY(1,1) NOT NULL, [EmployeeId] [int] NULL, [LastName] [varchar](50) MASKED WITH (...
0
votes
2answers
43 views

How to update the statistics of an index that has just been reorganized, both with IndexOptimize script

I would like to know if we can update the statistics of an index that has been reorganized, using the IndexOptimize script. Indeed, an index that is reorganized needs to have its statistics updated. ...
-4
votes
1answer
33 views

Error when running query as a subquery [closed]

The inner query runs successfully alone, but when I run it as a subquery, an error pop up. My query: Select TOP 3 * FROM (select fr.rating , sp.SpId ,sp.FName ,sp....
0
votes
0answers
17 views

SQL Server Cluster - READABLE SECONDARY

On our SQL Server cluster, I get messages when the backup runs on secondary node. BACKUP failed to complete the command BACKUP LOG activity. Check the backup application log for detailed messages. ...
0
votes
2answers
39 views

Violation of PRIMARY KEY constraint in SSMS import/export wizard

I have table MYTABLE with one PK constraint, generated by a third-party app: CREATE TABLE [dbo].[mytable]( [ID] [int] IDENTITY(1,1) NOT NULL, [FIELD_A] [nvarchar](255) NOT NULL, [FIELD_B] [...
0
votes
2answers
22 views

Cannot Find Job Associated Active Email Notifications

We have a email notification going via our SQL Server however I cannot find any job or setting associated with it. Its definitely the correct server and I am logged in as admin. The email is sent via ...