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.
28,725
questions
0
votes
0answers
28 views
SQL Server Setup Failure
Unable to upgrade the enterprise edition of SQL Server 2014 SP1 (12.1.4100.1) to SP3 version. I checked the registry key "ConfigurationState" and it's already 1 but still got the below error
...
2
votes
1answer
57 views
Online Index Rebuild of large table takes exclusive lock
I'm trying to rebuild the clustered index of a large table (77GB) on Sql Azure.
There is high concurrent transactional activity on the table so I'm using the ONLINE=ON option.
This has worked well for ...
0
votes
1answer
19 views
Can a member of the SQL server 'sysadmin' role login to any database?
I've been researching and can't find a definitive answer. So I apologize if this question is more obvious than I suspect. All documentation I can find definitively says 'sysadmin' roles can do ...
0
votes
0answers
9 views
Reaching a Microsoft SQL Server 2017 cluster from an other device
Let say I have a python service in one instance. This service needs to reach a MSSQL cluster of 5 nodes that is in an other location, with different IP addresses. Cluster creds:
user: my_user
password:...
-1
votes
0answers
22 views
Estimated Execution plan: How to reresh after new indexes
I have created two new indexes on a large table but the estimated execution plan for my query is still the same. How can I update some information so it is more accurate?
0
votes
1answer
28 views
SQL Server 2019 Database stuck In Recovery state after restart
SQL Server was restarted by mistake, when it came online, database came back in "In Recovery" mode.
Check from error it says "Recovery of database 'DB1' (5) is 8% complete (...
0
votes
2answers
23 views
How to combine multiple EXEC statements into a single SELECT?
I run this every month to extract database logs, and I have to change the dates as well.
DECLARE @start DATETIME
SET @start = CONVERT(DATETIME, '2020-08-01 00:00');
DECLARE @end DATETIME
SET @end = ...
2
votes
1answer
57 views
VARCHAR missing index not showing up in missing index dmv or query plan suggestions
I have a table with 307200 records, with f_column being VARCHAR(30). No missing index are shown in the missing indexes dmv or suggested in the execution plan.
SELECT f_column, f_column2 FROM t_table ...
0
votes
1answer
22 views
Insert in to table without duplicate records TSQL
Team,
Need some assistance with TSQL
table1
ID |Name| pin
1 a 101
2 b 201
3 c 301
4 d 401
table 2
ID|Name|pin
3 c 301
4 d 401
5 e 501
6 f 601
I want to ...
0
votes
2answers
25 views
User cannot query cross-database, despite having permissions for both databases
SQL Server 2017 Enterprise (14.0.3356.20), running on Windows Server 2016.
I have a situation where an application's user needs to join on a table in a separate database in the same instance, but I'm ...
0
votes
1answer
24 views
Is it possible to specify a different collation for the data vs the entities within a single database?
I have a database with a case sensitive collation because the data inside of it should be treated as case sensitive in the context of how it's used.
The entities and their column names within that ...
0
votes
0answers
18 views
Running sp_BlitzCache on remote server
I have installed the scripts for First Responder Kit 2020 release. I have a central server (A) and remote server (B). I am trying to run stored procedure sp_blitzCache on remote server B without ...
0
votes
0answers
16 views
The Replication Time in Short Time? [closed]
Background info:
Apply CQRS model in relation to SQL server.
Two similar database and its content.
Database 1 is only used for add, update and delete.
Database 2 is only used for read only.
Database 2 ...
0
votes
2answers
16 views
Schedule Job to Start at Different Steps
I am working on a system that generates a number of recurring emails as parts of various jobs scheduled in SQL Server Agent. The emails contain text-based reports that are loosely associated to the ...
4
votes
1answer
59 views
Suddenly SQL server restarted with pending task count increased
We are trying to understand the issue where SQL server was suddenly restarted on its own-
Its a SQL server 2012 SP4 -GDR applied with 40 CPu's HT enabled , thus total of 80 logical processors-
MAXDOP=...
0
votes
0answers
10 views
MFA and Scheduled Jobs/Tasks against your data sources
I have a question for those of you who are running with MFA as part of the auth process against your data sources: How do you handle Scheduled Jobs? Said job might be launched by your Data Platform's &...
1
vote
1answer
70 views
Eliminate filter operator before columnstore index scan operator
I have a large fact table with millions of rows called MyLargeFactTable,
and its a clustered column store table.
There is a composite primary key constraint on it there as well
(customer_id,...
0
votes
0answers
25 views
SQL Server 2017 Always-ON
I have configured Distributed AG from DC1 to DC2 (SQL 2017 Ent edition with CU22 version)
DC1- DB1, DB2 and DC2-DB3, DB4
I have restarted DC1-DB1 SQL services, after restart DC1-DB2 should be primary....
1
vote
0answers
21 views
SQL Server TDE why not use a self signed certificate?
I am implementing TDE in an enterprise environment with several domains and the typical Dev/Test/QA/Prod separation. The target instances are SQL Server 2016 Enterprise. There are over 60 instances ...
0
votes
1answer
16 views
New user creation option not available in SSMS sql server 2106 , how to enable?
i need to create New user through SSMS in SQL server 2016
0
votes
1answer
17 views
Invalid column name SIS ID?
How do i get this column to execute, it has this exception 'Invalid name [SIS ID]? Meaning there is a space in between and want to find a way to allow it not to happened.
insert into dbo....
0
votes
1answer
27 views
Is it possible to put mdf and ldf to NAS (Synology)
I'm planning to purchase NAS (synology) for personal use. I'm working also as developer, it is possible to put my test database (SQL server data and log) to NAS? This just for personal and for ...
0
votes
1answer
27 views
SQL Server user and role backup
I'm trying to figure out how to backup the users, roles, and what permissions they have on a SQL Server instance. I need to be able to restore the users, roles, and permissions without changing ...
0
votes
0answers
22 views
SQL Server installation failure as first attempt to install
Hi i have been trying to install SQL Server 2019 as downloaded from the website, however when I mount the disc image from the folder the wizard attempts to start however it blinks and fails. I also ...
-3
votes
0answers
24 views
Enabling CDC on 2019 Developer Edition [closed]
I'm running a docker container from mcr.microsoft.com/mssql/server:2019-latest image on Docker for Mac. I'd like to enable CDC, but I keep getting this error.
[S0001][22989] Could not enable Change ...
3
votes
0answers
51 views
Understanding DBCC TRACEON and TRACEOFF Globally and Session-wise
For those of you that keep track of what I discuss in The Heap™ you might have been following my endeavours whilst migrating a database from SQL Server 2008 R2 to SQL Server 2016 and battling with ...
-1
votes
1answer
60 views
How to keep a schema separate from the rest of the database, so it doesn't lock? [closed]
We are using a propriety SQL Server 2012 database, from a company that has given it's tables, views, objects, etc a schema name - lets call it COMP, for "the company's" schema.
We, as the ...
0
votes
1answer
37 views
The backup set holds a backup of a database other than the existing
I'm trying to restore my production .bak file to a new local instance using the below script but I still get this error:
The backup set holds a backup of a database other than the existing MyDatabase ...
0
votes
0answers
11 views
SQL Server 2017 MLS - cant add python package
I'm new-ish to MLS and have been trying to add new python packages like attrs to the python but it hasn't been working.
Our set up is SQL Server 2017 with CU14 on Win 2016.
Python version is 3.5.2
...
-1
votes
0answers
17 views
Unpivot Issue adding additional records for every column [closed]
i have an CSV import file that i have no control over concerning the column names or format
the headers consist of
id,[temperature range 1],[temperature range 2],[temperature range 3],[temperature ...
0
votes
2answers
38 views
How to delete records with FK constraint using Delete Trigger
I'm learning MS SQL Server and trying to implement a trigger to delete records in the child table if the record in the parent table is deleted. I'm using AdventureWorksDW database provided my ...
-4
votes
0answers
27 views
MSSQL Enterprise License question [closed]
I am trying to validate something someone told me. I am far from understanding some of it, but here goes. So I have a cluster setup with 3 vm servers running SQL Enterprise edition. We have 4 ...
-1
votes
1answer
33 views
MSSQL delete with output [closed]
I'm trying to create an agent job that archives records from a logging table. I created an archive table based on a create script from the original. I am using the following SQL but, when I execute, ...
0
votes
2answers
60 views
“Cannot continue the execution because the session is in the kill state” Error?
We use SQL Server 2019 Standard edition on Windows Server 2019. We have 200 users and thousands of connections at the same time.
SQL Server 2019 raises an error
Cannot continue the execution because ...
0
votes
1answer
68 views
Legacy CE vs current
I recently updated a SQL server to the current version. I have major performance issues with some queries for example this query takes 8 seconds with legacy CE:
But 5 minutes with CE 150:
https://www....
0
votes
1answer
24 views
grant select in just one DMV instead of grant VIEW SERVER STATE
Is there some way to grant select in just one sys.table.. for example select in sys.dm_hadr_availability_group_states without to grant VIEW SERVER STATE?
0
votes
0answers
21 views
Can parameter sniffing happen in other RDBMSs, not just SQL Server? [duplicate]
I just found out that when googling for the parameter sniffing, SQL server is also on the recommended search list but not other RDBMS such as Oracle, MySQL, PostgreSQL. Even in this community, ...
0
votes
1answer
24 views
SQL Server-Is It possible 'lock waits and timeouts high but average wait time does not'?
I use Windows Server 2008 Standard and SQL Server 2008 version.
I always thought that if the 'Lock Waits' number was high, the 'Average Wait Time' number would be high as well.
In addition, it is a ...
5
votes
1answer
167 views
How do pages get updated checksum when switching page verification mode from torn page to checksum?
Have an older but large db (6tb on the san) that still has its page verification option set to TORN_PAGE_DETECTION. How do you apply the new checksum to all existing pages when changing page ...
0
votes
1answer
38 views
Ensuring relationship between child tables exists prior to SQL insert
I have a situation where I have three tables: user, assignment and test. A user must have completed an assignment before he can take the test. This means the test table has both both a user foreign ...
1
vote
1answer
15 views
Is there a plugin to add a map behind the spatial results?
Using SSMS 18.5.1 and dealing with spatial results. I would like to visualize this data against a map of the world using Google Maps, Bing Maps, or any other mapping software. Is there a plugin or a ...
0
votes
1answer
39 views
Query optimizer triggering statistics update seems to be slowing down queries
We are running SQL Server 2016 and have a very simple stored procedure that does a select using a clustered index seek of a single row in a table with 140 million rows. In that table, we keep around ...
0
votes
2answers
57 views
Avoid downtime of Microsoft SQL Server during restarts
We are running an SQL Server Standard 2016. From time to time Microsoft updates need to be installed and the server will be restarted.
We have been asked to look into keeping the database available ...
0
votes
1answer
56 views
What is the best way to store optional values in table?
I have a large table that will be partitioned (about 300 million rows). There are currently 57 columns in the table. At the moment we also have an 'Option' column, which is to hold values that would ...
0
votes
0answers
18 views
SQL Server 2016 Altering avaibilty group listener IP
I cannot find the right method/way/syntax to change my IP listener address on an Always On availability group under SQL Server 2016.
I have a listener, myldag on port 1433 IP 150.1.2.3 and I want to ...
0
votes
1answer
52 views
Clustered Index during data load in MS SQL Server
Until today, I was under impression that it's been considered as a best practice to disable the indexes in sql tables during the data load. And with the fact that one cannot disable the clustered ...
0
votes
1answer
23 views
SQL Server Automatic Tuning (for query plans) vs. Availability Groups
In SQL Server 2017 (and up), for a database configured within an Availability Group, with query stored enabled, will automatic tuning work on the read-only replicas?
I don’t think so, because query ...
0
votes
0answers
91 views
Increasing CPU count to 64 results in slower SQL Server response time [closed]
I'm running SQL Server 2017 CU 15 on a Windows 2012 R2 Azure VM. Our network admin recently upped our CPU to 64 which I understand was just a matter of enabling them as the box has 64 CPUs but we ...
1
vote
0answers
55 views
Linked Server throws metadata error
I have a function in SQL Server 2019 that copies data from a local table to an Oracle 11.2.0.4 table via Linked Server, using Oracle 19.3 client. Recently, a new column was added to the Oracle table, ...
0
votes
0answers
12 views
SQL Server Persisted Computed Columns, Issues during Mass Insert or Update?
I have a database with a computed, persisted column which validates columns, checks if its an integer, and converts. Sometimes streetnumber is alphanumeric, like 5C, other times it's simply an integer....
