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.
30,662
questions
0
votes
0answers
20 views
Can UPDATEs on non-leading, non-nullable INT field in NC index cause bad page splits?
I have a NC index that is keyed on two fields where the first field is 100% unique and does NOT change under any circumstance rendering the second keyed column essentially useless, but that is beside ...
0
votes
0answers
14 views
During recovery, how can we apply transaction log backups to snapshot backup created by the veeam tool?
Veeam tool (SAN disk) is configured to create snapshot backups at 3am everyday. And log backups every hour.
When there is a disaster, usually given a full backup, we can restore the full backup and ...
1
vote
1answer
20 views
What is the advantage of full backup over snapshot backup when it comes to restore by applying tlog?
What is the difference between following restore options:
A. Full backup and applying to it subsequent log backups.
B. Snapshot backup and applying to it subsequent log backups.
0
votes
2answers
15 views
Will snapshot backup (non copy only) break restore chain?
I have full backup running at 6am on Sunday. Differential backup running at 6am everyday. Log backup running every hour.
There is also a Snapshot backup running at 3pm everyday. This is not a copy ...
0
votes
0answers
29 views
When do parameters not exclude tables from a partitioned view
It seems that I have some instances where passing a parameter to a partitioned view on the partitioned column still queries all the tables. But now there's more to it...
I have a four table [...
0
votes
0answers
39 views
SQL Server Logon Trigger Error after server restart
I have a logon trigger running that is executing as another user to log connection details. It works totally fine without issue except right after a server reboot. At that point, I have to connect ...
1
vote
1answer
285 views
Why engine choose Index Scan instead of Index Seek for Group By query?
I have query:
SELECT
M_nvarchar_3
, MA_nvarchar_40
, W_nvarchar_4
, LB_nvarchar_2
, SUM(ST_decimal_13_3)
, SUM(CW_decimal_13_3)
...
0
votes
3answers
46 views
SQL Server TDE - encryption_state 2 stuck at 100% complete
I have enabled TDE on two databases.
One is around 700 GB and that completed over the night and has encryption_state=3
The other one is almost 2 TB. That one has encryption_state=2 and ...
2
votes
1answer
114 views
Why can't I do dirty reads of dm_db_missing_index_details
One of our 3rd Party Production system went through a major upgrade and with sp_whoisactive I'm now seeing a lot of queries - 1 transaction deep - that are blocking any of my attempts to read ...
2
votes
0answers
29 views
MS-SQL and HierarchyID
I have been using HierarchyID for the last 2 years in some of my tables for obvious purposes
And I have kept a Column (int) for the last node number
for example if I have these rows so the int column ...
3
votes
2answers
121 views
Alternate Solution For REPLACE() In ntext Column
Overview
I have a picture database that catalogue information of pictures taken with cameras. The database contains a table pictures which has 256 columns which contain information about the picture ...
0
votes
0answers
33 views
SSMS Query store keeps getting disabled
I activated query store about a month ago with the settings below. Now, every morning starting up SSMS I find the Query store folder gone and query store disabled. Initially I used 1 Gb size, but ...
0
votes
0answers
49 views
Why deadlocks occurs on two queries that access same table, but different data?
I have a multiple deadlocks when running multiple backups in parallel. I tried to read the graph but I have not so much experience with this part. What I found in the deadlock graph was that deadlock ...
-1
votes
0answers
42 views
Update statistics and Index Optimize jobs are taking a long time
My SQL Server 2014 Enterprise Edition has 256 GB RAM, 8 vCPU and the database is about 2.5 TB in size. The weekly DBCC CHECKDB job WITH PHYSICAL_ONLY is taking over 4 hours. The weekly Index Optimize ...
0
votes
2answers
70 views
Free disk space on SQL without impacting application
We have 1 TB disk which is almost full(85%). We're going to free up disk space without any impact on application availability with below approach. Please suggest if this approach looks good or there ...
0
votes
1answer
45 views
how to get result value JSON and send result Format JSON
I have Query for result JSON but I need result like this:
result:[ "B":{1628424359816,35},
"A":{1628424359816,1},
"B":{1628424380816,63},
"...
0
votes
1answer
30 views
Supported RSA Key Lengths for Encrypted SQL Connections
I'm having trouble finding any documentations regarding supported key lengths for RSA certs to encrypt SQL connections. Our internal CA is issuing certs with a 4096 key length and that seems to work ...
2
votes
1answer
135 views
Need to determine log space required for a large transaction (MSSQL)
I need to update billions of rows in a partitioned table; it's also likely that the updates will move data between partitions. I'm trying to figure out how much additional log space I would require to ...
2
votes
1answer
79 views
Why does adding a WHERE clause break my working query, with error "Query processor could not produce a query plan because of the hints defined..."?
I have the following (stupidly-simplified) query that leverages two CTEs that refer to the same table and joins them to each other:
WITH CTE1 AS
(
SELECT dbo.RemoveNonNumericCharacters(PhoneNumber)...
0
votes
1answer
37 views
SQL Profiler shows multiple calls for one TVF
There is one table valued function in SQL Profiler I have noticed which seems to "call itself multiple times".
The definition doesnt do any odd thing either, it is a query with few JOINs and ...
0
votes
0answers
9 views
Are There Any Viable Alternatives to Master User Usage in RDS for SQL Server?
I've inherited an RDS instance, and I'm trying to figure out a way to create a server role as similar to that of SA as I can, so that I can give it to a couple of additional employees, without having ...
-4
votes
0answers
22 views
The query processor could not start the neccesary thread resources for parallel query [closed]
How to solved this problem?my database SQL Server 2008 R2
1
vote
1answer
49 views
Why can't I control the I/O affinity?
I'm running a 2008r2 EE version of SQL Server on a machine (Not a VM) with 4 Sockets of Intel® Xeon® Processor E7-8880 v3 with Hyperthreading enabled. I'm attempting to validate with the affinity for ...
0
votes
0answers
49 views
How to change IOPS on SQL DB server
Can anyone advise please? An application I'm supporting requires IOPS on DB server to be increased during a process, in order to improve throughput and then put back to original value after the ...
0
votes
2answers
34 views
Determine Available Blocks of Contiguous Ids
We have a SQL Server table that has an int autoincrement primary key column on it.
The table's primary key space is fragmented. For example, ids 1 through 10 are in use but then ids 11 through 100,000 ...
-2
votes
1answer
42 views
Find a doubled value between two columns in the same row
We discovered a bug in our program where there are some instances of a doubled value. I am trying to write a query to find all instances of this doubled value.
Right now I am using
WHERE
(GRIR....
0
votes
1answer
32 views
Is there a way to customize SQL Server Performance Dashboard?
The report in SQL Server Management Studio presents System CPU Utilization broken out by a minute. Is there a way to specify the interval - perhaps by 10 seconds?
0
votes
1answer
22 views
How do you detect a change in grouping when using Partition By in SQL Server 2012+ and execute logic based upon the change in the same SQL statement?
If I have a SQL statement that contains something like,
ROW_NUMBER() OVER(PARTITION BY school_area ORDER BY school_area)
I get:
1 someschool1 schoolarea1
2 someschool2 schoolarea1
3 someschool3 ...
0
votes
0answers
24 views
How to get all the extended properties inheritance and regarding this heritage apply the values of extended properties?
Hope you are well
I'm been a bit stuck trying to develop the code to get the inheritance of extended properties.
I will explain the situation:
The thing is have a query to list all extended properties ...
0
votes
0answers
53 views
Multiple SQL instances on the same server and how to manage TempDB
Probably a bizarre approach but I don't really have much say in the setup.
We have a large server deployed in Azure and the plan is to get 16 separate instances on this machine. We plan on using the ...
0
votes
0answers
14 views
SSIS and SQL Agent - running jobs using proxy vs using MSA
We have multiple servers, each with its own SQL Server instance.
We have external companies that develop applications for us. This companies deploy SSIS packages and run them via SQL Agent.
I'm ...
0
votes
1answer
47 views
Understanding how T-SQL transactions are working
I would like to understand deeply what is going on with my transactions:
I have a rule which executes each second 1 or 2 transactions (I receive a purchase event and I must save the purchased item and ...
0
votes
1answer
37 views
Doubts about table partitions
I am studying table partitions and I still have some things that I am not clear about.
I created a partition function that organizes the data for the last three months.
CREATE PARTITION FUNCTION ...
1
vote
0answers
70 views
How to determine the Tipping Point of which Physical Join Operator gets used to process a query?
I've been noticing some strange occurrences lately on my development SQL Server instance where the Nested Loops Physical Join Operator is used when a Hash Join would be much more efficient, within a ...
4
votes
1answer
60 views
Is Trace Flag 6534 Relevant Past SQL Server 2016?
Based on this link, I see that trace flag 6534 is relevant for SQL Server 2012 through 2016, but no mention of SQL Server 2017 or 2019. Is it still relevant for those editions or has it been folded ...
1
vote
0answers
64 views
Stored procedure running indefinitely, same statements executed in batch completes in a second; WITH RECOMPILE has no effect
Why would a proc just run without terminating (increasing IO and CPU in sp_whoisactive, nothing blocking) when all the exact same statements run fine by themselves in SSMS in a few seconds, but the ...
0
votes
1answer
46 views
Is it the right scenario to use spatial index?
I have used spatial index recently on one table to improve query which filter records by given date range, reference identifier and whether they are inside polygon.
Here's the example of the query
...
0
votes
2answers
67 views
Does changing database growth rate force a new execution plan?
For many years there has been a long running SQL Agent job that has taken nearly two hours to complete. Approximately two weeks ago on a slight whim a decision was made to change the growth rate on ...
0
votes
0answers
15 views
Azure SQL Server DTU Percentage degrades over time with API Throttling
I've been facing a weird DB behavior when trying to post records to my api. I have a function that throttles requests at a request rate of 10-15 req/s, each request results in a lookup and an insert (...
0
votes
2answers
61 views
What are the more important columns to include in NONCLUSTERED INDEX - from Group by or Where?
I am thinking about adding a NONCLUSTERED INDEX to a table in SQL Server 2016. I found that most queries refers to WHERE clause like:
WHERE varcharColumn <> ''
and varcharColumn2 = '2'
and ...
0
votes
0answers
54 views
Stored Procedure fails on tempdb-diskspace when executed by SQL Agent
I developed a bunch of ETL procedures which are executed by an overlaying SQL Script and write Data to separate DB. The Script uses a single Transaction, ensuring we only get complete data.
This ...
0
votes
0answers
56 views
SQL Server Tempdb space usage high on secondary always on [closed]
I am facing issue where in my secondary always on server, tempdb usage is very high.
Both primary and secondary are allotted same resources and have same disk size.
Secondary is configured for read ...
-2
votes
0answers
35 views
Insert Rows Until Condition Is Met [closed]
I'm trying to create a temp table that will have a field as the concatenation of year and quarter, e.g. 20212, as well as other fields.
I would then like to insert additional rows for each row in my ...
3
votes
3answers
114 views
Reducing Temp Table Scans when joining two temp tables using OR conditions
I'm working on a complicated query that I have up to this point been able to refactor to reduce execution time as well as number of scans and reads. At this point in the query, we have two temp tables ...
0
votes
1answer
59 views
How to reduce big size database files (Data File ) SQL Server without SHRINK?
I found a couple of 'question' regarding shrinking big datafiles but I think that is not very accurate to my situation. So here is my question
I have a big database with almost 8TB of Data File Size ...
5
votes
1answer
343 views
Why does switching two column values work by simply reassigning the values in T-SQL?
For example, the following query works just as intended in Microsoft SQL Server (T-SQL).
UPDATE Customer
SET ContactName = Customer.City, City = Customer.ContactName;
I would like to know why the ...
-1
votes
0answers
33 views
Which performs better on average Oracle Database pulling data from MSSQL or MSSQL pulling data from Oracle database? [closed]
I'm working on a task that requires data that is spread on different database engine, namely Oracle and Microsoft SQL Server, too be processed as one and to generate certain data through several ...
-1
votes
0answers
49 views
Updating SQL Server Maintenance Solution to use CommandExecute
All,
Looking to implement the Stored Procedure 'CommandExecute' in our environment. Cannot find any documentation about the parameter options for CommandExecute:
@DatabaseContext nvarchar(max),
@...
0
votes
1answer
36 views
Failover and Failback SQL server Distributed availability group with different versions
I know that SQL server Distributed availability groups can sync data between two different Major versions,
Lets say Global Primary - 2016
Secondary AG - 2017
This sync will work and I can do failover ...
0
votes
0answers
56 views
SQL Server - BackupDiskFile - Referencing to deleted TR Backup file [duplicate]
We have a backup job which creates transaction log backups every 10 mins for databases. Backup files which older than 2 days are deleted.
In ERRORLOG file - I see the record which I cannot get/...
