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
0
votes
0answers
38 views

Unable to delete server_principal due to granted permissions

I want to delete a server principal from my server because the member is not working at our company anymore. Unfortunately this principal is owner of objects / grantor of permissions because he ...
0
votes
1answer
26 views

SQL Server Master Data Services Permissions for Subscription Views

I have an instance of SQL Server Master Data Services installed, there are users managing the master data via the Excel add-in, now we're ready to integrate the master data into products. We've ...
0
votes
0answers
34 views

Phantom SQL Agent Job - Reports Running But Isn't Actully Doing Anything

I have a very similar issue to what is presented here. Periodically on one of my production servers a SQL agent job of some sort (that are responsible for calling several EXE's) will report running ...
0
votes
1answer
48 views

DELETE FROM sys.dm_tran_active_transactions safe? [duplicate]

Long story short I've got my local development machine into a bit of a mess with MSDTC and I have the horrible red arrow in DCOM. I have an application that uses MSDTC and MSMQ, and it's got itself ...
-3
votes
2answers
63 views

Sync databases from different servers [closed]

I have the following setup: One SQL Server on one side, another on the other. The two can't connect, so no possibility to a linked server. It is possible to place the MDFs and LDFs in a shared folder. ...
-1
votes
1answer
78 views

How to execute join statement dynamically without using static group id? [closed]

I work on SQL server 2014 I need to run this statement below dynamically without using group id static because I don't know maximum group id using may be 10,15,20 so How to run query below dynamically ...
-1
votes
0answers
44 views

Fragmentation after maintenance plan index rebuild

After my index rebuild through the SSMS maintenance plan wizard, I have checked my fragmentation rate and all I saw was that the clustered index was the only one affected from the index rebuild. I was ...
0
votes
2answers
51 views

SQL server update locks complete table instead of single rows

Consider a simple table with an Id and Item: Column date type ------------------------ Id int primary key Item varchar(50) Insert three items: INSERT INTO [Items] ([Item]) VALUES ('first ...
0
votes
0answers
16 views

How do I convert a SQL Server Clustered to Stand alone SQL Server instance also change from Physical to Virtual (P to V)?

(Convert Physical SQL Server Cluster to Virtual SQL Server Standard version) For the virtual server, we don't need clustering (just a standalone SQL Server). Windows server team is going to do the P ...
-1
votes
1answer
36 views

SQL query: Find latest from table with two dates [duplicate]

I have a table with a lot of entries, and I'm stuck on how to get the records with the latest "CreatedDate" This is my table: I would like this to be the result: Date: Shows which date the ...
0
votes
1answer
41 views

Should I rebuild index in non clustered index?

I'll be rebuilding our index in SQL Server since our fragmentation rate on all indexes for all tables are so high. Should I include to rebuild my non-clustered index also? Or rebuilding of clustered ...
0
votes
1answer
31 views

MSSQL transaction replication - How to resync the data?

I have 1 transaction replication (peer to peer topology). After I setup, I found that the data on 2 table was difference. The first peer table [VPMYN204].[AdventureWorks2019].[Person].[AddressType] ...
-1
votes
2answers
56 views

Meaning of “rows between 15 preceding and 15 following”

WITH trips_by_day AS ( SELECT DATE(trip_start_timestamp) AS trip_date, COUNT(*) as num_trips FROM `bigquery-...
3
votes
3answers
208 views

Select distinct column values from multiple databases

I have 100 databases from customers who all have the same schema but different content. Now I wanted to some analysis and start out with running a distinct of one column over all databases. My ...
0
votes
2answers
35 views

Using Cloud and On Premise DB together in an Application

I am developing a textile shop application in .NET Core using the Entity framework. So I am developing it in Azure and using the same application for different shops using Shop ID as the primary key. ...
0
votes
0answers
34 views

Rebuild indexes makes switch partitions useless (SQL Server)

I have a big table containing multiple indicators per ticker. The table is partitioned by indicator. Each indicator has nearly 50 million rows and currently there are about 30 indicators and they will ...
2
votes
1answer
71 views

If a DB table is 3TB in size and read speed is 10mbyte/sec. Does it mean that 83hours are necessary to create new index?

One of our custom log tables is 3TB in size. We need to create a new index. Read speed on the disk itself is 10mbyte/sec. Does this mean that the index creation process will be 3.000.000/10 = 86 hours?...
0
votes
1answer
29 views

Sql Server consuming lot of memory Creating Bottleneck for my web application Hosted in the same server

HI I have a server with 64 GB Ram and I have assigned around 52GB to the SQl Server itself. However Even this has effected the performance of the server and the web application hosted in the same ...
0
votes
0answers
22 views

Using AUTO_UPDATE_STATISTICS_ASYNC on an ASP.NET Session database?

I don't see a lot of bulk operations within plan cache, mostly individual insert/update/delete type commands executed from sprocs, so on the surface it seems like a good candidate for async stats to ...
1
vote
2answers
125 views

Should the T and L phase of a well tuned ETL report process take the same amount of time regardless of the time bucket width and data size?

For an ETL reporting system, is it normal for the total execution time for a pull of 15 minutes with no data to be similar to a 24 hour pull with data? I had expected that the total time for ETL when ...
0
votes
0answers
79 views

Insufficient system memory in resource pool 'default' when attaching a database

There is a website which uses an MSSQL database, from which the master.mdf and master.ldf files were copied and sent to me, so I can create a local copy of that database. I put these files in a ...
2
votes
1answer
38 views

Error while querying 'Always encrypted' column

I have set up 'Always encrypted' on one of the column in Azure SQL DB. Everything worked well except when a user is trying to access he is getting below error. ' I have tried some of the rememdies as ...
0
votes
1answer
31 views

MSSQL peer to peer replication what is the best practice to drop and create multiple table

I have a Transactional Replication server in peer to peer topology with 3 server node. My development strategy was Agile, so that will be a frequency change on the database etc. Scenario: I need to ...
18
votes
1answer
2k views

SQL Server in-memory optmized tempdb metadata memory usage continually growing

Problem We have enabled sp_configure 'tempdb metadata memory-optimized' = 1, and now the tempdb meta data is taking over 400 GB on one of our servers and continues to grow. There are some drops in ...
0
votes
1answer
39 views

Return Current Connection CName

I'm wondering if anyone knows if there is a way to return the name that was used to connect to the SQL server rather than the actual server's name. For instance I typically connect to the server via a ...
0
votes
0answers
30 views

Cross contamination in replication

I have the following scenario: Pub Databases: db-A db-B db-C Sub Database: db-Sub Table in Pub: db-A.T1 db-B.T1 db-C.T1 Table in Sub: db-X.T1_a db-X.T1_b db-X.T1_c Replication: db-A.T1 -> ...
0
votes
0answers
67 views

Combining multiple column

First of all, I apologize for my wrong English. I have a problem that I have been trying to solve for about a year. I tried to solve it with 2 different scenarios but I could not speed it up. Scenario ...
-1
votes
1answer
31 views

how to get the email address from inserted data as the recipient in sp_send_dbmail

I would like to use the email address that is inserted in a column as the @recipient in sp_send_dbmail. This code is a trigger that I've been trying to achieve this with and is for test purposes only: ...
0
votes
2answers
107 views

Create Date Spans from Contiguous and Non-Contiguous Date Spans

I am trying to create a query that will return a series of dates based off of the 'BEG_DT' and 'END_DT'. The results should show a series dates based off of the 'BEG_DT' and with a span between each ...
2
votes
2answers
333 views

How to parse XHTML node value with TSQL?

I have the following XHTML and would like to be able to parse out My Node Value. Is there a way to easily do this with TSQL? <SPAN style="WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM:...
-1
votes
1answer
62 views

What does the “wi” stand for in SQL Azure Automatic Index Naming? [closed]

I have a SQL Database on Azure that is giving me a performance recommendation for an index. What does the "wi" part of the name? I'm guessing something to do with the include part of the ...
-3
votes
1answer
54 views

The media set has 2 media families but only 1 are provided. All members must be provided

Restore database Currency from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\Currency.bak' go
0
votes
0answers
31 views

The function fn_my_permissions is not showing ALTER permission even when appears assigned to the user

I have a DB user in SQL Server (2012) that I have granted ALTER permission for the DB (via the UI: DB name > Secuity > Users > affected user > Properties > Securables wherein I can see ...
5
votes
0answers
71 views

Memory Optimized intermittently very slow perfomance

I have a memory optimized table with (in my test) 10 million rows. Running some UPDATE in parallel against it, I get intermittent spikes in CPU and huge spikes in execution time (>1s from 1ms) of ...
0
votes
0answers
33 views

AAD users lose access to database after import

I googled for the last few days but I got a bit confused and I do not know how to proceed further. Also, I'm not a DBA and I was just faced with this situation for the first time. Using sqlpackage.exe ...
5
votes
1answer
200 views

In a multi-database AG, what decides which databases will have more than 1 redo thread?

We have a large physical server with more than 50 dbs on it; some are quite busy, others very quiet. When having multiple DBs in an AG, only some of them multiple redo threads. We wanted the databases ...
-1
votes
1answer
32 views

SQL Server Agent Jobs and Availability group

I have two servers (Primary and Secondary), as well as an AG that keeps them in sync. As we all know, System databases are not covered by the AG definition. 1.I require that all jobs on the primary ...
0
votes
2answers
49 views

Lockdown database access on certain tables

I have a dev team who have a number of SQL databases used as a back end for mostly excel front end. This originally came from an excel spreadsheet until it got made into SQL databases. At first, they ...
0
votes
0answers
32 views

How to remove encryption from all objects in SQL server? [duplicate]

I have more than a hundred encrypted procedures and functions that I want to decrypt. I tried using dbforge sql decryptor decryption wizard for in place alter but I get the error "Definition is ...
0
votes
3answers
74 views

Sum date between two date and group by month

I have a table as below: Name Start Date End Date Joe 20/04/2021 20/05/2021 John 01/05/2021 28/05/2021 I am using a SQL table-valued function to return a table that has 2 columns: Month and ...
0
votes
1answer
37 views

handling primary key with large tree, best way to get root id from child table X

I always used database design using this pattern; root table with PK id1 child1 table with PK id2 FK id1 child2 table with PK id3 FK id2 child3 table with PK id4 FK id3 etc... so if I have to get ...
0
votes
1answer
40 views

Database design for school management to keep track of current and previous school years

What's the best way to design a students registration records and keep tracks of previous academic years? I have a table to enroll students, something like this: CREATE TABLE [dbo].[Students] ( [...
0
votes
1answer
46 views

Will AlwaysOn work with hundreds of databases in 1 group/multiple groups?

I have inherited a SQL 2017 system that had 114 databases that were synched to a second instance via an Always-On AG, with another 13 databases needing to be added. It was supposed to be providing ...
6
votes
3answers
475 views

Is there a better way to handle a multi-level ParentId table structure?

I work for a publisher, and our products are mainly books and journals. They are most commonly structured as follows: Book > Chapter Book Series > Book > Chapter Book > Volume > ...
0
votes
2answers
28 views

SQL Server Migration Plan - Alias Options for Multiple Apps Connecting

We currently have a number of databases on a single SQL Server 2008R2 instance (server name: prdServer01; Win VM). We want to migrate to a SQL Server 2016 instance (server name: prdServer02; Win VM). ...
0
votes
2answers
41 views

Get the Record where only Two Records exists in SQL

I want to get the parentID where user1 and user2 are using and only the two of them are using. for example, ParentTbl | ID | Remarks | |------|--------------------------------|...
0
votes
0answers
35 views

How can I DENY a SQL Server Database User from seeing tables in a specific schema? VIEW DEFINITION doesn't seem to work

This is somewhat of an X/Y Problem: my original problem is that I want to use SSDT to maintain my database design, with the catch that my database design uses two schemas: The dbo schema that ...
0
votes
1answer
56 views

What would cause this SQL Server deadlock?

I have a node.js script that is attempting to geocode records in a table. The script selects 100 records at a time that do not have lat/log data, geocodes them using a third party api and writes the ...
3
votes
2answers
182 views

Test to see when table is candidate for compression (row or page)

Does anyone know a tool like this page https://columnscore.com/ where one can determine if a table is a good candidate for row or page compression. Also I am trying to understand the benefits of ...
0
votes
1answer
21 views

Using sql server alias via management studio on another computer

Suppose I setup alias in sql configuration manager, then on the same server, via management studio I can use the alias name to connect to the server. However, on another computer when I open ...