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
3
votes
1answer
70 views

ALTER DATABASE SET ARITHABORT not taking effect

I've executed the following query as described here: ALTER DATABASE [{database_name}] SET ARITHABORT ON WITH NO_WAIT; SELECT DATABASEPROPERTYEX('{database_name}', 'IsArithmeticAbortEnabled'); ...
1
vote
0answers
44 views

Query disk performance in VMware database

i need help to build a query to retrieve disks performance from VMware database. The query must bring the host LUN or the VM disk, and their respective IO and latency. I found the view ...
7
votes
1answer
103 views

Why does SQL Server require a private key to encrypt a backup?

I am setting up an experiment where I only have the public key portion of a certificate on the server. The certificate was generated on another server and I did not restore the private key. When I ...
8
votes
2answers
125 views

Change referenced index for a Foreign Key

I have something like this: CREATE TABLE T1 ( Id INT ... ,Constraint [PK_T1] PRIMARY KEY CLUSTERED [Id] ) CREATE TABLE T2 ( .... ,T1_Id INT NOT NULL ,CONSTRAINT [FK_T2_T1] ...
11
votes
4answers
1k views

Alternatives to Network Backup

In our environment we have some servers that are in an Always On Availability Group, and some that are standalone. We normally backup to a network share, but we have recently observed that as the ...
0
votes
1answer
43 views

How to check user defined schemata creation date in SQL server

I want to find Schema creation date and user who created schema in SQL server, I am unable to find information in system views. Note: Not asking about schema scoped objects
3
votes
1answer
93 views

Availability Groups automatic failover is not so automatic

Behavior when I stop SQL Server Service : The availability group fails (resolving on SECONDARY SQL SERVER and fail state on Cluster management). SQL Server does not connect with the Cluster ip (I'm ...
2
votes
2answers
65 views

Availability Group - Deleting old backup files

I'm using Azure VMs with a SQL Server Availability Group, and Ola Hallengren' scripts. Backups are working without issues, however I've noticed that old backup files are not being removed from the ...
7
votes
2answers
350 views

Force SQL Server to use fragmented indexes?

I have a quite large table (>10M rows) with frequent crud operations. It has proper indexes, but they get fragmented quickly. Without a periodic index reorganize/rebuild maintenance plan, the index ...
0
votes
1answer
67 views

Sum of values each hour at half past (that hour)

I need to aggregate the "sum" of values of a table with TS, Value columns of each half past hour (of 60 minutes), i.e. 00:30, 01:30, 02:30, etc Sample data: Val TS 1 2019-08-12 00:00:00.013 3 ...
0
votes
3answers
84 views

AlwaysOn High Availability MDF and LDF file locations

We have 4 replicas with 03 replica in read only mode with manual failover. We have some drive issues and we need to rearrange the MDF and LDF files. On the primary we have 2 drives I and J. The I ...
1
vote
1answer
41 views

Moving from Azure to Hyperscale

Does Azure Hyperscale allow for Memory optimised tables and does it allow/have rollback facilities
1
vote
1answer
53 views

SQL Server Resource Pool and Workload Groups

Below is the scenario: Scenario 1: I got two workload groups assigned in the same resource pool with MAX CPU 20%. In the event of CPU bottleneck, the limit will come into effect with 10% each ...
2
votes
3answers
53 views

SQL Server Auto-growth Handling

I have this scenario: DB Log with initial size of 1193 and auto growth by 100 MB unlimited Everyday at 8am, I have 20 auto-growth events firing per second which is my problem Is the correct solution ...
1
vote
1answer
9 views

SQL Connector for Azure Key Vault - Cannot use more than one key vault

I am using SQL Server 2016 Enterprise Edition with SQL Connector for Azure Key Vault. I am trying to attach more than one Key Vault to my SQL instance but it is not working. I went through these ...
1
vote
2answers
41 views

Microsoft SQL Joins and Where Clause

I am sure a very simple question for people out there but my brain has shutdown. I have joined two tables with a left join which has worked as hoped as I do need to bring back the NULLs in one of ...
1
vote
1answer
28 views

SQL Server 2016 Standard Edition Replication

I have a single database running on SQL Server 2016 Standard edition in our primary datacenter. I would like to keep a live copy as close to realtime (1-2 minutes) in our disaster recovery datacenter ...
0
votes
1answer
46 views

How to Load XML child nodes in to multiple tables

I would like to load the below XML into 2 tables Table 1: Acct Table - it should have the ACCT_NUM and a Key to tie to the Table 2 below Table 2: Should have all the Contacts (1 record per ...
2
votes
0answers
40 views

Merge replication Locking on MSMerge_genHistory Table and lots of unhappy chickens

I do contracting with about 10 chicken farms. Each chicken has a GUID so we can track it through the entire life process (govt. requirement). We have 2 million chickens in the tables at any one time....
-2
votes
1answer
62 views

How to create a VIEW when Table1 is having relation to Table2 on several of it's fields?

My situation (greatly simplified) is as follows: Table1 ID Name Ver1 Ver2 Ver3 Ver4 Table2 ID Name ... This table holds the versions The columns Ver1 - Ver4 are foreign keys and are IDs of ...
0
votes
1answer
29 views

Oracle Linked Server Long Preemptive_COM_Cocreateinstance Waits

I have a Linked Server using the OraOLEDB.Oracle provider from the 11g Oracle Client and it was working fine for a while. However, an Oracle 12c Heterogeneous Connection was setup recently. This ...
0
votes
1answer
48 views

Force Client to Validate SSL Certificate?

When SQL Server uses the Force Encryption with value "yes" then if in the client-side we use encrypt=true option in the connection string, then the client application tries to verify the server ...
0
votes
1answer
46 views

The fulltext filter daemon host (FDHost) process stops abnormally on SQL Alwayson Cluster

We added a new SQL Always-On Cluster (FCI) 2017 and some of the databases needed full text filter service. So we also added that feature to the install. Ever since we did that we have been getting ...
0
votes
1answer
48 views

Does using secondary replica for read operations still required license? [closed]

I wanted to move some of the reporting workload to the secondary replica but the following things stopped me: no writes can be performed (including creating temporary tables); a license is required; ...
4
votes
1answer
160 views

Server Integrity Check CheckCommands question

I am trying to figure out what is the best CheckCommands option for ServerIntegrity check. Should I leave the job defaults as is? EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES'...
3
votes
2answers
79 views

Sql server sleeping state is increasing using ADO.NET?

We have one live ASP.Net application which is used by around 1000 concurrent users. In this application we have used connection pooling. There are 2 connection string defined in web.config with ...
1
vote
1answer
53 views

Is it safe to stop the shrinking of MSDB?

In a production server (Microsoft SQL Server 2014), the file MSDBData.mdf ballooned up to more than 450 GB, eating almost all the space in the server (only 38 GB remaining). First thing, I created ...
1
vote
1answer
44 views

How to query/monitor index fragmentation?

I have been a DBA for 3 months now at my first job out of college and I am having a significant amount of difficulty trying to figure out how to accurately query and report on index fragmentation ...
1
vote
1answer
46 views

Incremental backup between now and backup taken one week ago

We do a full backup of our SQL Server 2012 database every night. A new developer needs an incremental (or differential) backup of a backup we took one week ago. The reason is that for them, ...
-2
votes
2answers
65 views

Size of an empty database too large

Related to SQL Server 2016. I have a database with five empty tables, let's say db1. I have another database with almost 100 tables and with a data in it, db2. I observed that the size of db1 ...
1
vote
1answer
36 views

Remove expired certificate SQL Server

I am trying to delete an expired certificate from my database server. The certificate was used to encrypt connections to sql server 2014 r2. We no longer have a need to encrypt connections and so we ...
2
votes
2answers
34 views

Azure blob storage container vs VM managed disk for SQL on Azure VM database files

Are there benefits specific to SQL Server on Azure VM when using the SQL Server Data Files in Windows Azure feature? Aka using Azure blob storage container for database files versus an Azure VM data ...
0
votes
1answer
20 views

Should Amazon RDS SQL Server 2017 Web Edition Max Memory settings be different from that of a locally installed instance?

We have an Amazon RDS SQL Server Web Edition on Windows Server 2012 with 2 processors and 8GB RAM. Should I configure the Max Memory similar to that of an instance on a local server i.e keeping back 3-...
-1
votes
0answers
26 views

Partition every table [duplicate]

Recently i read about partitioning. I was thinking whether it is good idea to partition every possible table(if it's schema allows us to do so). Every table that has some Date column can be ...
2
votes
1answer
21 views

Implementing a degenerate dimension with an alphanumeric varchar(30) at the transaction level?

I am currently working on designing my first data model and I am confused about this particular implementation of degenerate dimensions. According to Kimball's design tip 46, a degenerate dimension ...
1
vote
0answers
39 views

Remove DTC Support From SQL Server 2016

My application is running in Azure SQL which doesn't support DTC. We only use Azure SQL in upper environments. In the local development environments, we're still using SQL Server 2016. I want to ...
2
votes
1answer
39 views

Log Shipping and Daily Backup

My SQL Server 2012 DR setup is a mess. We've had a maintenance plan in place from the beginning that takes a daily full backup, with hourly log backups. Recently, we set up log shipping to an off-site ...
0
votes
2answers
40 views

Memory issue in Transactional Replication

I want to replicate a data of 500 GB through transactional replication once in a day, so my query is this, will it create a new file in the snapshot folder on daily basis of 200-300 GB or will update ...
1
vote
2answers
50 views

Joining unrelated data

I have two tables. Each table has a single column with 2 records. How would I go about merging the columns from both tables into another? For instance, the first table has records 1 and 2, and the ...
0
votes
1answer
50 views

CU8 for 2016SP2 error

When upgrading to CU8 on 2016SP2, I get the following error Final result: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files. ...
1
vote
1answer
45 views

AD groups and ms SQL security: is this default behavior?

I have a question regarding security in MS SQL Server (right now, I'm trying this on a SQL 2016). Scenario: User AD\testuser is in both AD security groups (AD\testgroup1 and AD\testgroup2). A new ...
0
votes
1answer
46 views

Create an incremental number with year and month without updating the entire table using a trigger

My situation and code I'll add a number to my MS SQL Server table. It must be an unique number beginning with the year and month of the time when it's created, followed by an incremental index number....
2
votes
2answers
66 views

Can I loop through a pivoted row in Sql Server?

My data is like this from my tsql query. The duration column is the count of 0 from the recent month backwards till it reach/hit 1. As of now I'm doing it via excel but can it be done via T-SQL ...
3
votes
1answer
50 views

Using a second table for unique key?

This is a fairly noob question. I work with an old-school PowerBuilder software (PowerCampus) that has some strange design decisions from the 80s and 90s. What's perhaps the most important table in ...
1
vote
1answer
41 views

Accessing Administrative Share Using Listener Name

Can a SQL Server AG listener name be used to access an administrative share i.e \\DB-listener\c$\Somefolder? I can RDP using the listener name but can't use the listener name to access the $ ...
1
vote
1answer
43 views

SQL Server - MERGE duplicate SOURCE rows validation is only evaluated on UPDATE and DELETE clauses

I want SQL Server to throw an error at me if my SOURCE table contains duplicates as per the ON clause in every case, including INSERT, but it only does for the UPDATE and DELETE cases. Why doesn't it? ...
7
votes
2answers
358 views

See details of old sessions

Is there any way of seeing details of sessions from the past (I fully expect the answer to be "no"). I am trying to find out what server a certain application is running on, that application accessing ...
0
votes
1answer
38 views

Passing Variable in json_value in sql server 2016 [duplicate]

declare @id int set @id = 1754 select (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."1754".value')) CorrespondanceNumber, (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"'+...
5
votes
3answers
226 views

A more intelligent ntile

When using the ntile() window function, the major issue is that it arbitrarily groups into roughly equal parts regardless of the actual value. For example with the following query: select id,...
-3
votes
1answer
43 views

How to convert UTC time to New York time zone in Microsoft SQl [closed]

How to convert UTC time to New York time zone in Microsoft SQL