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.
29,648
questions
0
votes
1answer
35 views
Moving SQL MDF and LDF file
Is there a way to move an MDF and LDF file from C drive to D drive? I've tried to run
ALTER DATABASE myDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE myDb SET OFFLINE
ALTER DATABASE myDb ...
0
votes
1answer
19 views
Find closest match in two columns, one containing wildcards [duplicate]
no script to attach as I can pull the data just fine, I am struggling to join my two tables though.
The column I want to join on has the same information formatted differently in each table, making it ...
0
votes
0answers
22 views
Will database replication increase the size of the replicated database?
First thank you for your time. We have a SQL Server database that was assigned to be replicated. the original size of the database was 5 GB for the datafile, not the log file. Now after the ...
0
votes
2answers
42 views
MSSQL query to select top 2 values [duplicate]
I am not from a DBMS background and I couldn't find much help from reading some resources once.
I have a requirement like: There are 5 interfaces with some execution time. My goal is to find the top ...
0
votes
1answer
29 views
is it safe to grant permission to all users in Microsoft sql server
I have a global production server and database, as I all users should have access to it by remote desktop.
Is it possible to grant NTFS permission on all database files (mdf+ldf files) to all users? ...
0
votes
0answers
15 views
Will database replication increase the size of the replicated database?
We have a SQL Server database that was assigned to be replicated. the original size of the database was 5 GB for the datafile, not the log file. Now after the replication the data file is 4 times ...
0
votes
2answers
37 views
MSSQL and ALTER AUTHORIZATION on table has no affect?
I have a schema called [test] with an owner X.
I transferred an ownership of one table [test].[table] (ALTER AUTHORIZATION) to owner Y.
Now, if I understand correctly, even though the owner X owns the ...
1
vote
2answers
49 views
Disable AUTO_UPDATE_STATISTICS
We had an issue earlier on in the week whereby a query had horribly regressed and as a result, it had completely taken over the SQL instance (OLTP Server) as it is frequently executed. One of the ...
2
votes
2answers
195 views
Why does SQL Server do lots of unnecessary clustered key lookups in this SELECT - OFFSET - FETCH?
I have the following table:
CREATE TABLE [dbo].[MP_Notification_Audit](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[type] [int] NOT NULL,
[source_user_id] [bigint] NOT NULL,
[target_user_id]...
1
vote
1answer
31 views
T-SQL: Assign identifier to block of results
I'm not sure exactly how to phrase this question but... I'm working with an existing database and the structure is something I can't change. I want to write a query which assigns a unique id to each ...
2
votes
1answer
21 views
Executing sql linked server procedures through python
I am getting an error when trying to execute SQL Server system procedures such as sp_addLinkedServer and sp_dropserver etc from python script
USE [master]
EXEC master.dbo.sp_dropserver @...
3
votes
1answer
25 views
How do I fix the Code Page in SSIS Lookup Transformation to be 65001?
I have an SQL server 2019 with databases and tables all set with the collation Latin1_General_100_CI_AS_SC_UTF8.
The relevant table has code and desc columns which are both of data type varchar.
In ...
0
votes
2answers
78 views
Oversize non-clustered index
I have a table with 220 rows. It has about 20 columns. One of the non-clustered indexes has a datetime as the key value and includes a unique identifier and a varchar(128). The clustered PK is a ...
1
vote
1answer
32 views
Is it possible to “bind” sp_executesql to the calling procedure, as recorded in the Query Store?
Statements executed with sp_executesql appear to be generally "unbound" from the procedure in which they appear. By "bind", I simply mean "associated to the calling object&...
0
votes
0answers
11 views
Ideal CDC Latency for SQL Server Replication
I am seeking to understand what the ideal latency for an implementation of CDC in SQL Server Standard (2017) is.
The latency I am referring to can be found from the sys.dm_cdc_log_scan_sessions DMV ...
3
votes
2answers
233 views
Why doesn't SQL Server use my index in this SELECT … WHERE?
I've created a table with a nonclustered PK (this is by design), and an additional nonclustered index on the column I'm filtering with a WHERE clause ([target_user_id]):
CREATE TABLE [dbo].[...
0
votes
0answers
22 views
Service Broker error when ending a conversation. Second attempt
Let me rephrase my previous question because issue reproduction is much simpler than I thought.
When I close Service Broker dialogs sometimes I get a message The remote conversation endpoint is either ...
0
votes
1answer
16 views
Bulk DELETEs in publisher breaking SQL Server Replication in subscriber
We have SQL Server replication set up between 2 databases running SQL Server 2019. Every night, a SQL Agent job runs on the publisher that deletes all the data from a table and then rebuilds the ...
1
vote
1answer
45 views
MS SQL 2017 cluster: impact of disabling distributed transactions for an availability group
A company is having a Microsoft SQL Server 2017 Enterprise cluster (RTM-GDR, 64-bit, 14.0.2027.2, KB4505224) consisting of one Availability Group (AG) with two servers, a primary and a secondary.
A ...
1
vote
4answers
41 views
Azure sql database how to track table schema change
So someone dropped a column in a Azure sql db. How to show the table schema before the change? Restore to a previous version is not a option as that would override everything.
0
votes
1answer
37 views
Best practices for sql server log shipping method
We have about 20 databases, with no DR config yet.
Most of them are SQL Server 2016 with Failover cluster Availability group
What's the best method for a log shipping?
Log shipping directly between ...
0
votes
1answer
26 views
SQL Server 2019 (RTM) - 15.0.2000.5 period has expired after upgrade to CU 8 [closed]
Hi i have MS SQL with version
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows ...
1
vote
2answers
50 views
Ola Hallengren scripts: after a job failure, how to skip the databases that worked and backup only the remaining ones?
In our organization we have more than 150 databases in one instance and full backup should run for every day.
Our backup script should be in such a way that if backup job fails while taking 100th ...
0
votes
0answers
20 views
SSIS - DMA doesn't support migration assessment to SSIS in local SQL Server 2019
we will migrate from SQL Server 2008 R2 to SQL Server 2019 in near future, we already run DMA to analyze database, however DMA 5.2 doesn't support migration assessment type for intergration services (...
0
votes
1answer
33 views
Linked Server to Oracle error ORA-01804
Migrating SQL Server to a new machine. Migration involves setting up a number of linked servers, some SQL Server, Oracle and Teradata. No issues with SQL Server or Teradata servers, but every Oracle ...
1
vote
3answers
59 views
SQL Server - Unique Constraint Syntax
Is there a functional difference between the tables built in the following two ways?
CREATE TABLE [dbo].[test_uq](
[ID] [int] NOT NULL,
[f1] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[...
2
votes
0answers
58 views
Restore from local disk vs SAN on physical machine (SQL Server Linux)
I am new to Linux and our client decided to use SQL Server on Linux. As soon as we started testing, we ran into performance issues.
The server in question is a physical machine configured with a SSD ...
0
votes
1answer
29 views
SQL change record [closed]
What statement can I use to show what records have changed?
For example, can I run a query that shows when Rocco, eventually, logged in from Ireland, as opposed to logging in from Italy?
0
votes
0answers
24 views
SQL Server 2019 unable to install error [duplicate]
When installing SQL server 2019 receiving installation error.
Error log
Overall summary:
Final result: Failed: see details below
Exit code (Decimal): -2068052377
...
1
vote
2answers
33 views
Bypassing version store for changing column datatype
Database is Always On High Availability synchronised database on SQL Server 2019 with READ_COMMITTED_SNAPSHOT on and we are updating datatype from varchar to nvarchar (with "alter table" ...
0
votes
2answers
36 views
SqlPackage importing of very large bacpacs to Azure
I want to import a database from a .bacpac file to a SQL Server in Azure. I read the document.
It says that there is a flag called DatabaseMaximumSize=(INT32).
I wanted to know if there's a limit that ...
-4
votes
2answers
43 views
What is a merge log in SQL? How is it structured, and what are its uses? [closed]
While creating merges, I was told of something called a merge log. I have not heard of this before. What is a merge log in SQL? How is it structured, and what are its uses? I appreciate any responses. ...
0
votes
1answer
22 views
SQL 2016 - NU/NC Index Missing From sys.partitions
Is there a reason that a non-unique, non-clustered index would have 0 corresponding records in the sys.partitions view? There is a CI on the same table and there is a record for it in the view. My ...
0
votes
2answers
48 views
Recursive CTE to get Grandparent, parent, children [duplicate]
I have a data structure that relies on a parent/child model. The top level parent can have multiple children where they can also be parents.
Here's an example of how things can look in the tree:
...
0
votes
1answer
29 views
How to assign GUID to batches of data based on row counts and tunable batch size
I need to assign batches of rows a common GUID id (for consumption by an external process).
Below is a simplified setup describing what I'm looking for:
BASE_TABLE represents one of pre-existing ...
-1
votes
0answers
19 views
Inserting data into the table with few info from other table [closed]
/Hello Guys
I have a problem
I have 2 tables
Table A ( Groups )
Table B USers
This is how groups looks like
SELECT TOP (1000)
[USERDISTRIBUTIONGROUPID]
,[OWNERID]
,[ID]
,[CREATEDDATETIME]
,[...
0
votes
2answers
56 views
A slow query on MS-SQL-Server 2019 cannot be optimized
In the following thread I received some important and valuable information on how I can improve a slow query on MS-SQL-Server 2019:
Helpful Link
However, that didn't do very much.
First I deleted all ...
1
vote
1answer
26 views
SQL Server Replication: Is there a way to know which articles are being used in a replicated database?
I've come into a situation where there are about 1000+ tables being replicated to a database. When it was created, I think the creators just put a bunch of tables they thought would be needed. I'd ...
0
votes
0answers
39 views
SQL Server Numa config - Why is VAS reservered memory different on each numa mode?
I have a virtual machine running on hyper-V. The VM has 26 processors assigned to it and 300 GB of RAM.
The underlying physical server has 2 CPU sockets, both with 8 cores each.
Looking at the NUMA ...
0
votes
1answer
40 views
How can we implement “A student can enroll in up to 4 units in a semester ”
I have a practice problem on Database implementation and am confused. For this database, I need to store enrollment information on students. Information such as which units a student has enrolled into....
6
votes
4answers
2k views
Trick to remember which instance I am working with
I work as a BI developer and always switch between different instances on a same server. However, sometimes I forget the right instance and for example may cause to truncate a table on the other.
I ...
1
vote
2answers
71 views
Speed up Key Lookup (clustered)
I have a slow query in Sql 2017. Key lookup (Clustered) is taking 62%. There is also an Index Seek (nonclustered) taking 30%.
Is there anyway I can speed this query up?
Execution plan: https://www....
1
vote
2answers
54 views
Shorthand for “UNIQUEIDENTIFIER” data type name?
The data type keyword "UNIQUEIDENTIFIER" is a chore to type. I want a shorthand replacement.
At first glance, it seems that SSMS provides two; the words "GUID" and "UID" ...
0
votes
1answer
52 views
Migrating to server with much fewer CPU cores [closed]
We are in process of migration from our legacy SQL servers hosted on 2014 version to 2019.
Currently the no. of logical cores provided on physical host is 64 and we've been asked to migrate these ...
4
votes
2answers
292 views
Will installing a second SQL Instance cause an outage on existing instances?
Like the title says, I just want to verify if we have the need to install a secondary SQL instance on a server that already has one SQL Instance, will the installation of the secondary instance cause ...
0
votes
0answers
45 views
(Solved) Sql Server Failover Clustering Error
I want to install always on on Sql server 2019. I have two windows server 2016. I will not use active directory. I do the installation as described on the site where I will leave the link below. When ...
0
votes
1answer
45 views
Partition switching vs schema transfer
I'm designing a database whose data will be generated daily by an ETL.
After some research, I found that one can use partition switching or schema transfer to maximize the uptime of the "live&...
0
votes
1answer
39 views
Does the length of the fields play a role in index access in MS-SQL?
Last week I had already started two threads dealing with slow SQL selects that probably did not use the index.
Today I noticed the following: The customer and the material are involved in almost all ...
2
votes
2answers
46 views
How does JOIN involving database across 2 servers store the intermediate data?
In case of a SQL JOIN on 2 databases across servers.
For example:
Select *
From server1.db1.dbo.tbl1 a
Join server2.db2.dbo.tbl2 b
On a.common_id=b.common_id
Where a.some_col = 1000
Suppose this ...
0
votes
1answer
22 views
Can no longer connect to localdb after updating from Sql Server 2016 to 2019
I need to restore a backup for an assignment, but SMSS said I was running v13 while the backup was made in v15. So I download and installed SQL Server Express 2019 and uninstalled the older version.
...
