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.
33,669
questions
0
votes
1
answer
45
views
Batch update in a large dataset
We have 3 tables:
Product (product)
Stock (product_Stock)
Price (product_price)
In each table, we have more than 290 bilions of rows. These tables have a compound primary key: product_fk and ...
- 191
1
vote
1
answer
42
views
Ring Buffer Rollover?
I'm currently using the ring buffer in extended events to track deadlocks, but noticed that it stops being queryable after it hits the memory limit. When I've used the event_file option in the past I ...
- 243
0
votes
0
answers
15
views
SSRS 2016 - How to Add Custom Web Portal Connection
We are replacing an old VM which was configured with reporting services 2016. The Web Service and Web Portal URL's were configured for both a "public" URL which looks nicer as well as a URL ...
0
votes
2
answers
132
views
Convert varchar into datetime and use date diff on the where clause
I have a field ABCD stored as varchar(16) and the data stored as 20170509074744CD.
I am not versed with SQL Language. I will greatly appreciate any help converting the field ABCD into datetime and use ...
- 1,039
0
votes
1
answer
62
views
Need help on SQL Server data compression, hope to compress more on a large table
We have made good progress following our previous question about SQL Server data compression. For one of the tables, we compressed it from 20 GB to 2 GB, so this proves that the best compression ratio ...
- 139
-1
votes
0
answers
63
views
Stored Procedure execution became very slow all of a sudden in SQL Server 2012
I have a stored procedure that gets executed over 100,000 times per hour, with each execution typically taking less than 1 millisecond. However, there was a sudden occurrence where it started taking 1 ...
- 437
-1
votes
1
answer
60
views
Move Large tables between databases
I have 2 tables in a 1.2 TB database on SQL server 2019 and want to move them to a new database .
First table is (171 GB Data , 45 GB indexs and 750 Millions rows)
second table is (24 GB Data , 55 GB ...
- 1,381
0
votes
0
answers
15
views
prerequisite functionality for which validation for Circular dependency needed
I am working on a training module where I need to add a prerequisite functionality for which everything is done except for the validation for Circular Dependency( for example training1 is required for ...
- 1
-1
votes
0
answers
35
views
Page Life Expectancy Drops in SQL Server
I have a server with 512GB of RAM and a dual-CPU NUMA architecture. It's configured for "Always On" distributed availability, but I've been experiencing intermittent issues with a low Page ...
0
votes
0
answers
24
views
How to check with TSQL if I'm connected to an Azure Managed Instance in a Failover Group?
How can I check with TSQL if I am connected to an Azure Managed Instance that's in a Failover Group?
For Always-On we can use this:
SELECT SERVERPROPERTY('IsHadrEnabled')
But in the case of Azure ...
- 628
0
votes
1
answer
57
views
Sql Server Open Transactions
I was notified that the transaction log drive for a sql instance I manage is filling up. I jumped on, found which transaction log was growing, and went to take a look at why the tlog was growing. ...
- 678
4
votes
2
answers
155
views
How can SQL Server's Query Store Reports show data for minute-length intervals, when "Statistics Collection Interval" is set to 1 hour?
In the database properties under Query Store, the "Statistics Collection Interval" is set to 1 hour. This is also confirmed by querying the view query_store_runtime_stats_interval - all ...
- 83
-2
votes
1
answer
42
views
Why force plan is not working with failure "NO_PLAN"?
We have query which is inserting data to heap:
INSERT INTO [heap]
(
80 COLUMNS...
)
SELECT
14 columns
65 NULL VALUES
, '...
- 295
0
votes
1
answer
58
views
best practice for side by side sql version upgrade with Availability groups
We are attempting our first ever side by side upgrade of a 2 node(multi-subnet, enterprise edition) AG from sql 2016 to 2019. I have some questions on how best to approach this:
Do we create a brand ...
- 257
0
votes
1
answer
83
views
Is there a stronger guarantee of minimal logging when running INSERT INTO [...] WITH (TABLOCK) on a heap than on a B-tree?
The documentation lists the following requirements for bulk imports with minimal logging
The recovery model of the database is set to simple or bulk-logged.
The target table is an empty or non-empty ...
- 45
4
votes
1
answer
521
views
SQL Server memory requirements
At this moment I run into some database performance problems for a SaaS application. During the day the RESOURCE_SEMAPHORE wait stats shoot up to 30 to 60 seconds for 1 or 2 minutes. During that time ...
- 43
0
votes
1
answer
99
views
Is this Parameter Sniffing?
I have a particular stored procedure that takes 1 parameter.
This proc is executed once a day with the same parameter value everyday. The row count is also the same with each run of the proc.
Day 1 - ...
- 319
-1
votes
0
answers
22
views
have a table with an 'after delete' trigger in one database. I want to insert a record into a table in another database when a record is deleted
Your scenario is as follows: you have a table with an "after delete" trigger, and you want to insert a record into another database's table if someone deletes a record. The problem is that ...
0
votes
0
answers
38
views
Monitor connectivity from SQL Server to other data sources
I am MSSQL Server Database Administrator. I work in a BI Team and manage an operational data store too. For data sources which their RDBMS is MySQL we have linked server to connect.
Sometimes the ...
- 210
3
votes
1
answer
76
views
Is the WITH (NOLOCK) hint ignored on the non-target tables of an UPDATE or DELETE statement?
In SQL Server Databases, the WITH (NOLOCK) query hint is ignored on target tables in UPDATE and DELETE statements. However, does this also apply to tables included in a join for an update or delete? ...
- 155
-1
votes
0
answers
94
views
Avoid deadlocks with insert, select then delete
So I have these queries I run where I get a number of records based on a list of ids. Normally I just do an in statement with the list of ids. If that list is too large than I load the list of ids to ...
2
votes
2
answers
814
views
Do database connections get automatically reconnected if a database is restored?
I'm not a DBA/SQL expert, so please excuse me if this is the wrong forum to post in.
There's an issue with our production database where log files are not being stored in LDF format. DBA wants to ...
- 23
-1
votes
0
answers
22
views
Mass Deletes and SQL Full Text Specification
We have a database that has 90 million rows in the primary table.
There are several full text columns split out into separate tables but most people use the one that has the record title in it.
This ...
-3
votes
1
answer
99
views
How to exclude some column from history table in temporal table
I have a same question in Stack Overflow about this, But I hope to find answer here
I have temporal table like below
-- Create a Temporal Table
CREATE TABLE dbo.Sample
(
SampleId int identity(1,1) ...
- 95
0
votes
1
answer
40
views
Group by a column and select the group id on condition that applies to all the rows in it
I have the below table
CREATE TABLE result(
id INTEGER NOT NULL
,cid INTEGER NOT NULL
,aid INTEGER NOT NULL
,agid INTEGER NOT NULL
,ft INTEGER NOT NULL
,ct INTEGER ...
- 197
0
votes
1
answer
49
views
Joining SQL 2016 to a one-node cluster that has SQL 2014 installed
I have currently 1-node (it's a lab) Windows 2012R2 cluster with SQL server 2014 installed (patched to the last patch based on sqlserverupdates.com). The goal is to upgrade the cluster
to something ...
- 347
1
vote
1
answer
68
views
Does SQL implementations (or at least SQL Azure) skip obvious duplicate data with joins when returning data?
Say I have:
very wide table A where I need all columns
that I need to join with a small table B that has a LOT of rows.
Of course when I join these two tables I get returned all the content of A for ...
- 355
0
votes
1
answer
46
views
Creating AGL for Named Instance on SQL Server 2016 [duplicate]
I have installed a Named Instance on an existing server so the set up is like the following:
Server 1:
QA01 - Default Instance
QA01\BU0 - Named Instance
Server 2:
QA02 - Default Instance
QA02\BU0 - ...
- 563
3
votes
1
answer
276
views
Are there any possibilties to mitigate data loss risk on delayed durability
Due to poor disk performance I need to evaluate using the delayed durability setting in SQL Server 2016.
Microsoft states clearly, that this comes with a data loss risk because transactions are ...
- 171
0
votes
1
answer
42
views
How to calculate the time for a transaction start to commit to be communicated to the client application?
I have a 2 node AG in sync mode.
AG transaction delay value is about 60ms/s. This is the sum of all transactions delays in that second.
The transactions/sec value is around 1000.
So the delay per ...
- 3,430
0
votes
0
answers
88
views
Performance issue in SQL Server Analysis Services Multidimensional Data Model
We have a star multidimensional model in SSAS in SQL Server 2019 with 4 fact tables and about 600 million records on each fact and many dimension tables with about 1.5 million records and there is no ...
- 159
1
vote
0
answers
78
views
Why does msdb of dev server show the backup history from another server after I perform a restore?
Observed a strange thing today.
I have full/diff/log backups configured on prod server.
I restored one of the prob db onto the dev server.
Backupset, backupmediafamily msdb tables on the dev server ...
- 3,430
0
votes
1
answer
47
views
How to add Primary key using new column in database
i converted my database from access to sql server ... now i want to replicate it and database is giving primary key error....i wrote below code to add a new column in all database but i am not able to ...
- 3
0
votes
1
answer
174
views
query bad performance
Is there anything I can do to improve this query performance? I have included the execution plan and the Server Profiler output. Is there a index that can help? I see there is a Index Seek that is ...
- 47
-1
votes
2
answers
67
views
Database tenancy model
I'm redoing a legacy system, and currently, I have a database with the same structure for each client. I'm really unsure whether I should maintain it this way for the new system or if I should create ...
- 1
1
vote
3
answers
87
views
Does a SQL Server Log Backup Include Log Records Backed Up in a Previous Full or Differential Backup?
I've been reading the Microsoft documentation on log backups in SQL Server and came across the following statement:
(Log Backup) Specifies a backup of the transaction log only. The log is backed up ...
- 1,149
0
votes
1
answer
79
views
What does the "D" mean in SQL syntax?
Piotr Kononow's post List all indexes in SQL Server database contains an example of SQL query to list all indexes in a SQL Server database. And we have confirmed in testing that the example works ...
- 139
0
votes
0
answers
159
views
User does not have permission to alter database '', the database does not exist, or the database is not in a state that allows access checks
Our log file was full, so I made a mistake in trying to shrink it. My original thought was to move the log file to a different drive, shrink the log, then move it back. However, I did it incorrectly....
- 9
1
vote
1
answer
45
views
Download certificate from Key Vault w/ private key for use in SQL Server (TDE) via powershell
I need to download an x509 Certificate from Azure Key Vault and import it in SQL Server to use for TDE.
Note: I'm not referring to EKM providers/ASYMMETRIC KEYs, where SQL accesses key vault to pull ...
- 2,073
2
votes
3
answers
184
views
Performance implications of OPENJSON in view
Earlier today, I needed to put some small data quickly in a SQL Server database:
CREATE VIEW MyView AS SELECT * FROM OPENJSON('[ ... ]') WITH ( colA int, etc. );
intending to go back and replace this ...
- 21
0
votes
1
answer
38
views
Modeling a data processing system for consigned employees
I'm currently in the process of developing a payroll system using SQL Server and PHP. However, the system involves a multitude of processing rules.
During this processing, I may receive files ranging ...
- 1
0
votes
0
answers
21
views
Issue with SQL Server 2019 SSMS connection
I have a SQL Server 2019 Express installation. It was acting weird in that even though I had set it to mixed mode auth, I could not change the sa password (I know we shouldn't use this, but it's a ...
CBrianC
0
votes
1
answer
28
views
SQL Server : Partitioned table data type change
We have 40TB size SQL Server 2016 database. we have tables with 100+ billion rows per table which are partitioned. The older partitions are read only and new partitions are Read write. Recently for ...
- 1
0
votes
1
answer
86
views
Auto-increment [id] field in a table in an SQL-Server database
I have different tables, containing an id field. That field is typically defined as id (PK, int, not null).
Normally, when adding an entry to a table, I add the value of the id field myself, but I'm ...
- 523
0
votes
2
answers
72
views
Growing SQL Server Data Warehouse - How to reorganize efficiently?
we've been using SQL Server for quite a while now in our company to host all application data. We use a typical set-up with data being loaded from various data sources into a Staging Area, which ...
- 3
0
votes
2
answers
71
views
Why password return null for table master.. sys.logins? *SQL Server
I have made comparison between two sql servers on my environments
A - SQL SERVER 2016 - Passowrd return value
B - SQL SERVER 2019 - Password is null
Is there any setting to check.
Thank you in ...
- 11
0
votes
0
answers
45
views
SQL Server 2008 R2 version needed
Does any one have SQL server 2008 version?
I couldn't find it anywhere on microsoft website, because it's not supporting it anymore.
I need to migrate 2000 sql server to V19. For this I need ...
0
votes
0
answers
23
views
Best Strategy to copy database using Secondary Database (not touch primary) to dedicated database server
i have application team that have dedicated database server for datawarehouse. I want to know what is the best way to copy database from secondary.
Current Environment
Always On
1 Primary
1 Secondary ...
- 11
0
votes
0
answers
37
views
SSDT building error saying "unresolved reference to Login"
We retrieved source code from a SQL Server 2016 database and made a Visual Studio 2015 SSDT (SQL Server Data Tool) project. The source code contains a file Security\foglight5.sql with the below ...
- 139
0
votes
1
answer
35
views
SQL Trigger Connection Case With NT AUTHORITY
I am trying to limit the connections to my SQL using a CASE to monitor the connections as laid out here.
In the CASE I am trying to add the NT AUTHORITY\NETWORK SERVICE to the case. Yet when it ...
- 121