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.
31,202
questions
0
votes
0answers
10 views
Retention not working on temporal table or very slow?
I have a table with retention set to 25 months (from today is 2019-12-19) but when I query the validfrom/validTo dates directly in the history table, I see records with ValidTo dates before that. We ...
0
votes
2answers
112 views
Will I need to fix fragmentation after shrinking a database full of only HEAPs?
I have a 1 TB database that contains hundreds of quarterly archive tables and I am looking to drop any table older than 5 financial quarters, which will free up ~600gb of space. To provide an example ...
0
votes
1answer
26 views
Migrating from old AlwaysOn Availability Group to new AG cluster but keep ip addresses
We want to migrate our SQL Server 2012 on server 2012R2 to SQL Server 2019 on server 2016 and keep the ip addresses but change hostnames.
i can not change the name of listener and ip addresses from ...
0
votes
1answer
39 views
Filter by field, and if zero results then filter by other field
How should a WHERE clause be constructed, such that:
It filters by a certain field
If there are zero results for filtering by the aforementioned field, then it filters by another field?
For example, ...
0
votes
1answer
38 views
My SQL server cannot start after a sudden shutdown
Today I found that I could not connect to my SQL server. It was working well yesterday until my computer shut down suddenly.
Here is text information.
在 System.Data.SqlClient....
0
votes
0answers
24 views
lock / blocking lifetime on union queries MS MSQL
Can anyone shed any light on how locking works on union queries? Say I have the query below and the Archive tables take 1 min to run and the non-archive tables take 5s. Are any locks taken on the non-...
0
votes
2answers
53 views
Why is the result of DATABASEPROPERTYEX not consistent for AdventureWorks?
I'm using the function DATABASEPROPERTYEX to check the Recovery property of databases, but the result is wrong for the database AdventureWorks (later I noticed the same strange behavior for ...
3
votes
2answers
293 views
Identify all queries that target a given table?
Is there a good way to identify all queries that target a given table (extended events, procedure cache, etc)?
0
votes
1answer
30 views
Are there any special considerations for a log-shipping role change when the database has memory optimzed files?
We're noticing a problem with our log-shipping setups when the database contains a memory-optimized file. Specifically, after changing the primary/secondary roles, restoring log backups taken after ...
1
vote
1answer
46 views
Data corruption-Errors found in off-row data- am I loosing data?
I need your help with understanding what is going on in my database.
Check DB job detected some issues in one of the tables. There are two types of error messages. A lot of errors like below:
Table ...
2
votes
3answers
126 views
Constant rebuilding indexes to fix performance - why?
We have a database server (2016 SQL Server), that we have added a step of 'rebuilding indexes' to the deployment process.
In decades of working with MS SQL Server at a many companies, I've never ONCE ...
0
votes
1answer
21 views
Importing CSV Flat File - how can we change defaults of all columns to n characters
I have to import a CSV file into a table that has 75 columns. The Flat File connection defaults all the columns to 50 character strings. Some of the fields are much smaller and some are 255. The only ...
0
votes
2answers
46 views
Best practice for a database used by multiple versions of an application
I'm working on a new product which will run on-premise at our customers. The configuration and usage of this application will be stored in a database. One of the requirements is that a newer version ...
-1
votes
0answers
15 views
I want to update Remaining_Payment by add advanced_Amount with Sum of Recived_Amount from and subtract from Total_Amount ) By Triggers
Here is My Database tables create in SQL SERVER MANAGEMENT STUDIO 18
-2
votes
0answers
41 views
Tempdb filling up on secondary always on server
Please advise . The tempdb disk fills up unexpectedly on secondary always on server . Is there a way to check what is causing or a way to prevent it ?
-3
votes
0answers
41 views
LIGHTEST weight relational database for node [closed]
I know that SQLite is lightweight but I wonder how much of the ram it takes up. I am trying to set up a personal POC in a aws micro (.5GB ram) and I am trying to max the efficiency at the knitty ...
-2
votes
0answers
88 views
What are some ways that I can optimize this SQL query and make it run faster?
Any tips on how I can improve the speed for this query would be helpful. I've tried forcing plans on SQL server, which helps temporarily, but it seems that the speed slows down after a couple weeks/...
-2
votes
2answers
76 views
How well MSSQL can survive a server hard reboot?
Always hears people saying that hard resetting/rebooting may result in corrupted files. If the machine is running a database server such as MSSQL, the database may be corrupted as well. Does anyone ...
0
votes
2answers
56 views
How to reduce database size in SQL server?
I have a scenario that there is a Backup DB where we normally store BackUps of tables from production DB before doing any updates/deletes so that if anything goes wrong, we can restore the data from ...
5
votes
1answer
252 views
Is the data exchange on connection from client to server sent as plain text over the wire?
I have a new SQL Server 2019 on-premises instance. Assuming the client is the SQL Server Management Studio or any other application:
When the client connects to the server using SQL Server ...
5
votes
1answer
154 views
Door Number 1, 2, or 3?...Security change, bug, or failed upgrade with an in-place upgrade from SQL Server 2016 to SQL Server 2019?
What I Did:
Recently I tested doing an in-place upgrade on ServerA, a development server that was running SQL Server 2016 (SP 2 CU17) Standard Edition to SQL Server 2019 Standard Edition. (I know this ...
0
votes
0answers
39 views
Microsoft SQL Server - Recovery MASTER Database
I have backuped "master" database from 1 server and need to recovery it to another server. In "master" database or in another database - not matter. Just i need access data.
When i ...
0
votes
1answer
34 views
Sql server replication for HA and DR
1.)According to brent ozar's HA and DR chart,for Failover Cluster Instance - SQL Server minimum version supported and SQL Server minimum edition required is given as 'any'.
Does that mean it works ...
0
votes
2answers
41 views
Does only a full backup (and no tlog backup) of a db in full recovery model allow for point in time recovery?
I have a database in full recovery model.
Full backups are configured to run every Sunday and log backups run every day.
Log backup files are destroyed after 30 days. Full backups are destroyed after ...
0
votes
1answer
30 views
Is there a way to disable database encryption before initial encryption completes?
I am able to start, suspend, and resume encryption. Once started I can't find a way to disable encryption until initial encryption completes. Is there a way to accomplish that?
For example...
-- ...
-3
votes
0answers
57 views
SQL server query takes a long time for short span dates but a short time for long span dates [closed]
Auto-create statistics and Auto-update statistics are also on for the database. The query is still taking a long time for the short span dates. Whereas for the long span dates, it is taking no time. ...
0
votes
1answer
103 views
Ensure that SQL server "avoids" the clustered index when a non-clustered index can be used
I have a query, the short of which looks like the following. While this is not exact, it should show how the problematic behavior is introduced and what I am looking to prevent. And "prevent"...
0
votes
3answers
84 views
CASE statement issue
Can anyone help with this error?
SQL:
SELECT
InvoiceDate,
BillingAddress,
BillingCity,
Total,
CASE
WHEN Total < 2.00 THEN 'Baseline Purchase'
WHEN Total BETWEEN ...
-4
votes
0answers
31 views
Help with indexes and complex execution plan
Working with an application that I don't have the ability to change queries being submitted to MS SQL Server. I've come across this monster and need some advise on which indexes to adjust for ...
0
votes
2answers
70 views
Why Is There a Sort on this Column
This query has a plan which includes a SORT and the sort is taking up the majority (77%) of the cost:
SELECT
LFTYLM_STATUS
FROM
LFTYLM
WHERE
LFTYLM_LIS_ID = 138998 AND
LFTYLM_LFTY_ID = 78 AND
((...
0
votes
2answers
49 views
Order of Key Columns in Index
The execution plan for this query appears to be making a very bad choice (of course I know it is not):
SELECT TOP 1 E_PER_ID FROM E WHERE E_CUS_ID = 1912 AND E_TYPE = 'R' ORDER BY E_ID DESC
The two ...
0
votes
1answer
43 views
SQL Server Access Database in Filesystem
We have a Windows Server 2008 which is offline. We no longer have access to the server OS, or any GUI tools, but we do have a backup of the entire C:/ of this server.
This server used to host an SQL ...
0
votes
1answer
86 views
SQL Agent Job error
It may be really simple question, but at the moment I have a kind of mental loop and need your help to break it.
SQL server 2016 is installed using a domain account. I didn't create an explicit login, ...
0
votes
1answer
28 views
SQL Agent Job Import Data
I'm having a problem with importing data. I created a package in SSIS and a connection between SSIS and sql server. It all works fine outside SQL Server but I wanted to make a 'Job' SQL Server Agent, ...
0
votes
0answers
42 views
Is there a way for a SQL Server Login to gain permissions from multiple groups?
I have a security solution that I am trying to build.
But I am unable to get it to a state that I am happy with.
I am hoping someone will be able to shed some light on the correct path or deny the ...
0
votes
1answer
56 views
SQL Install Fail - 2012 on Win11
Cannot use file 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Template Data\master.mdf' because it was originally formatted with sector size 4096 and is now on a volume with sector ...
0
votes
1answer
37 views
Certificate (SHA1) loaded in a database but couldn't be found under SQL Configuration Manager and Key Registry
I have two instances on a server. sql 2014 (port 1433) and sql 2016 (port 50019)
I have a certificate with SHA1 algorithm on a server that is not expired.
**When sql server started I can clearly see ...
0
votes
1answer
44 views
Restoring to .bak process hung up because network path change
I have a SQL Server Agent job that grabs a bak file and restores it to a database on the server (code below). IT added memory and remapped network drives, so the "oldnetworkdrivename" is now ...
-1
votes
0answers
32 views
Insert blocked but not being reported as blocked
We came across an interesting scenario yesterday and I'm trying to figure out if there was an easier way to diagnose and remediate the issue.
We are running SQL Server 2019 CU 12 Enterprise edition.
...
0
votes
0answers
44 views
After a restore my transaction is slow, so I stop it, do a roll back, then re-run and it's fast...why?
On my development server I delete all data from two tables then pull fresh data from the production server via Delete From then Select Into. It's only about 100,000 rows total. On dev I run a routine ...
0
votes
0answers
19 views
SQL Server peer to peer replication error
I'm facing a problem with SQL replication, I have two servers SRV1 and SRV2 that are running Windows Server 2012 R2 and Windows Server 2019 (respectively), both having Microsoft SQL Server 2017, SRV2 ...
1
vote
1answer
40 views
Recreating history from snapshoted data
i have a case where i need to compare snapshoted data(dbo.src) with destination table(dbo.dest) and find out which of the countries are new and which ones are not valid anymore.
DATA:
create table dbo....
5
votes
3answers
804 views
Does GDPR require deleted backups to be rendered unrecoverable?
Although GDPR has been in place for some time, my organisation has had a dispensation to essentially ignore it up until now. The dispensation was because the contract from the government we were on ...
5
votes
3answers
266 views
Column property of updatable view is incorrect when inner join is used
I have a table atbl_UserSetting with a column with name ID which is a primary key with identity and a seed/increment.
This table is accessible through a view for regular users. This is the view:
...
0
votes
1answer
39 views
Query to find data where value of data changed to 0
I am trying to write a query to find the rows where value of particular column changes to 0 from some other value say 1 or above.
The value may change from 0 to other value again. But we need data for ...
-1
votes
0answers
42 views
Non deterministic query in MSSQL
I have this MSSQL query I need to debug the functioning of a stored procedure that runs every 10 minutes.
SELECT DISTINCT TOP(10) *
FROM [Main].[dbo].Listing cs
WHERE ...
0
votes
0answers
48 views
How to retrieve min/max values and corresponding dates in a table
I'm trying to figure how to retrieve minimum/maximum values and minimum/maximum dates from a data set, but also the date value that corresponds to each minimum/maximum value.
Example Data:
CREATE ...
0
votes
2answers
104 views
What index should be used if all queries only need a perfect match (no partial text searches)
I want to improve the performance of the query var dbObj = dbSet.Where(x => x.Name == name).FirstOrDefault(); done on the table pasted below. I was thinking about adding an index on [Name]. What ...
0
votes
1answer
47 views
How to find last value from Ms Sql
I have an example table like this:
ID
UserId
Name
1
00009
Jomy
2
00012
Tomy
3
00012
Tomy
4
00012
Tomy
5
00009
Jomy
I want to get the last value of UserId = 00012, (UserId from ID number 4). I ...
0
votes
0answers
32 views
If I rename a table with dependencies, and then create an synonym of the old name pointing to the new table name will the dependencies still work?
In an attempt to remove some downtime to my users, I want to redirect them to a back up table while I'm repopulating the production table. The tables in question have been in place for years so my ...
