All Questions
99,799
questions
1
vote
1
answer
24
views
Deletions getting omitted
We are facing an issue on one of our production systems where after we delete the data
the data doesn't seem to get deleted. We have a Get call just after the delete call.
The data shows up.
Versions
...
0
votes
1
answer
186
views
MYSQL Delete event is not running
I have below database tables,
Tables Definition
A - order_dateils :
id
order_bar
description
payment_status
created_at
B - invoices :
id
invoice_id
status
created_at
and created below event to ...
0
votes
1
answer
9
views
Selecting last value to be entered each month
I am looking to pull a closing balance from the database for each month.
I have tried
SELECT CloseBal As 'Balance', MONTHNAME(DateTime) as 'Month', DateTime
FROM Table1
WHERE MAX(DateTime)
Group By ...
5
votes
5
answers
2k
views
Subscription Initialization throws Error "The option "INLINE=ON" is not valid for this function" which is incorrect
Scenario
We are upgrading our SQL estate to SQL Server 2019 and have setup what will be the production environment.
From:
SQL2008R2 Standard/Windows Server 2008R2 - Primary Server/Secondary Server (...
0
votes
2
answers
18
views
Allowing negative values up to a specific value
I have a situation where we store the value of credits a user has in the database:
`credits` DOUBLE(22,2) UNSIGNED NOT NULL DEFAULT '0.00',
The credits are numbers going from 1 to any positive value. ...
1
vote
0
answers
53
views
Update fails on linked server after upgrading to SQL Server 2022
After upgrading SQL Server from 2019 to 2022 the following query with linked server has started to fail (I've omitted some column names and details for simplicity):
UPDATE [<remote server>].[...
0
votes
1
answer
235
views
How to resolve MDF and LDF files remaining in use?
I am attempting to copy off approximately 220 gigs of database MDF and LDF files from a MS SQL server 2000 install in order to transfer ownership to a third party.
The error I get when I try to stop ...
16
votes
3
answers
3k
views
Indexes for SQL query with WHERE condition and GROUP BY
I am trying to determine which indexes to use for an SQL query with a WHERE condition and a GROUP BY which is currently running very slow.
My query:
SELECT group_id
FROM counter
WHERE ts between ...
0
votes
0
answers
3
views
PostgreSQL replication and index usage counts
Postgres collects index usage statistics in pg_catalog.pg_stat_all_indexes. Does anyone know if those statistics are identical across master and replica nodes when using “physical” wal replication?
0
votes
1
answer
4
views
getting rows and adding a column to each with the timestamp of the next row, with postgres
I have the following object:
CREATE TABLE IF NOT EXISTS {schemaName}.{tableName}
(
id VARCHAR NOT NULL,
ticker VARCHAR(16) NOT NULL,
interval ...
3
votes
1
answer
878
views
Use of integer instead of interval (of one type)
Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation.
...
0
votes
1
answer
24
views
What makes the optimizer insert a sort operator in the plan
What are the reasons that the optimizer chooses to insert a sort operator in the plan (to satisfy a stream aggregate or a merge join, etc.) instead of going for hash match or hash join?
I have now see ...
1
vote
1
answer
292
views
Database deployment using QSR (Quest Script Runner) is slow
As part of the automatic deployment of a project, we deploy various Oracle scripts (packages, tables, data, etc...) using qsr - Quest Script Runner that is part of Toad. The script executes multiple ...
1
vote
2
answers
29
views
Is there any configuration not to use index merge in mariadb, if a cardinality of an index is below than that?
There is a table have 2 indexes, called state and CATEGORY, which has a very low cardinality respectively 4 and 24 within 7,110,590 rows in the table.
When running query of select statements that ...
1
vote
0
answers
28
views
dbatools, DbcCheck Does not use Saved credential
Last week i deploy dbccheck on the production environment. I found strange thing and have no idea what the problem is,
we have 40 db vm's which are not domain joined, each of db vm's have same windows ...
0
votes
1
answer
9
views
Does it make sense to enlarge "shared_buffers" for writing Postgresql?
I'm using Postgresql-11, with a traditional HDD as the physical storing device.
I need to optimize the performance for writing.
I have changed the wal_buffers from -1 to 512MB, that means that ...
4
votes
2
answers
199
views
Alert on Table size
I need to create an alert on my application table when it reaches 98% of its threshold. I wrote an SP which checks the rows and logs the information into a log file which was monitored by alert system ...
1
vote
1
answer
45
views
Lots of "key value" joins causes SQL Server query to exponentially slow down
I am migrating some data that's stored "key value" style, from an attributes table that uses an Object ID and an Attribute Type as a Clustered Index (I've also tried as a nonclustered index):...
2
votes
5
answers
8k
views
MYSQL database replication Error MY-002061
I've been trying to get two databases in docker containers to replicate. I've been working off this article:
https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-...
0
votes
0
answers
12
views
How can I nest a JSON array inside of a JSON object and retain it as an array?
I'm trying to build a JSON object with some data that I need aggregated. I first aggregate values into an array, and then I aggregate them into a json object. I have not been able to figure out how to ...
6
votes
2
answers
14k
views
MySQL - run SELECT statement on another server without defining the table structure
In MySQL I can query information on another server using federated tables, as long as I've defined the same table structure locally.
In MS SQL Server, however, I can run any SQL statement against a ...
0
votes
0
answers
9
views
Create a group number for rolling month
Starting from a table with dates in descending order, starting from the current_date:
date
----------
2023-02-03
2023-02-02
2023-02-01
2023-01-31
...
I want to add a column that contains a group ...
1
vote
2
answers
2k
views
pg_dump --exclude-table not working on one specific table
Wanting to exclude a few very large table in a pg_dump, I use the -T option multiple times, like this:
pg_dump --clean clientportal -T article_plain -T callstats -T cdrviewer_cdr -T calls -T ...
1
vote
2
answers
422
views
Which Oracle datafiles grow in size?
I have downloaded and started up Oracle's pre-built OTN Developer Day VM. The VM is running on an SSD. I want to move datafiles that can grow to a separate mount point (setup as a vmdk on a HDD. more ...
0
votes
0
answers
12
views
MySQL occasional performance issues
I am currently working on system with MySQL DB and SpringBoot (Hibernate). Both are located on the same server. All tables use InnoDB. Although there are no slow queries (5+ seconds), there is ...
0
votes
1
answer
7
views
Combining Multiple queries from the same table inrto 1 query
I am looking to find a way to achieve the below...
I have tried using Joins and SubQueries but no joy as yet...
I have a table that keeps track of wins/loses/draws as so
Date
WLD
01/01/23
Win
05/01/...
1
vote
1
answer
653
views
MariaDB: Duplicate entry for key on OPTIMIZE with no duplications
There is a table named contracts with following structure:
MariaDB [db_name]> DESCRIBE `contracts`;
+-----------------------------+--------------+------+-----+---------+----------------+
| Field ...
10
votes
2
answers
125k
views
How do I select data with a case statement and group by?
I'm trying to manipulate data a little by using a case statement but can't seem to figure it out.
Stops
--------------------
1Stop
1-Stop
1 stop
1 Stop
1stop
1 Stop
2-Stop
Im trying to come up with:
...
1
vote
0
answers
24
views
SQL HA Cluster TempDB Version Store blocking on secondary Replica due to open transaction?
I am currently investigating a repeating error which occurs on the secondary Replica of our 2 node Alwasy on High Availability cluster. The Replica is set up with Read-Intent only because we use a ...
1
vote
0
answers
24
views
SA disabled and no other user has access to master db [duplicate]
I have inherited an old SQL Server 2008 R2 where SA login is disabled. No other logins gives access to master db, so I cannot really gain access to administrative stuff (create new logins, change ...
-1
votes
1
answer
87
views
SQL Server edition support [closed]
Does anyone know whether Microsoft supports SQL Server 2005 Enterprise Edition SP2 anymore?
The following is from reported from @@version
Microsoft SQL Server 2005 - 9.00.5000.00 (X64)
Dec 10 2010 ...
53
votes
3
answers
4k
views
Huge slowdown to SQL Server query on adding wildcard (or top)
I've got a zoo of 20 million animals which I track on my SQL Server 2005 database. About 1% of them are black and about 1% of them are swans. I wanted to get details of all the black swans and so, not ...
3
votes
1
answer
419
views
Download the replication snapshot file using FTPS
I have two databases for two companies. Company A's database contains domain data. The other company is pulling the data using snapshot replication. We have used FTP to communicate:
Created FTP ...
6
votes
1
answer
13k
views
Multiple IPs registered for listener when using AlwaysOn AGs w/ multi-subnet clustering
We are testing AlwaysOn Availability Groups on a WSFC multi-subnet cluster and using dynamic DNS registration. Our issue is that sometimes the IP for the Availability Group Listener (AGL) in the ...
2
votes
1
answer
23
views
PostgreSQL: How to use INOUT parameter of a Stored Procedure called in dynamic SQL?
I have a stored procedure with an INOUT parameter, which value is modified inside the SP. I need to call that SP, providing a variable as that parameter, and then use the modified variable. But the ...
2
votes
3
answers
217
views
MySQL HAVING Causes Extra Increment of Session Variable
I'm writing a select statement to get the top 3 most recent items for a list of users. An example can be seen in this fiddle: http://sqlfiddle.com/#!9/cb25a4/12
The rows are getting ordered correctly ...
1
vote
0
answers
18
views
Find SQL version of Azure server
I have been restoring bacpacs provided by a third party on my on prem sql server via powershell. I copy over the bacpac from an SFTP site on to my local drive. This time I have been getting errors ...
2
votes
1
answer
81
views
Update statistics on a 4 billion record table
On a SQL Server 2016 SP3 database, we have several (under 10) tables with billions of records.
It's a 24/7 system with limited maintenance windows so it's essential that user impact is limited while ...
0
votes
2
answers
972
views
How to sync logins and jobs when using an availability group?
So I am configuring availability group which will sync the databases from primary replica to the secondary replicas.
How to sync the jobs and logins so that when there is failover to secondary then ...
1
vote
1
answer
623
views
Why does SSAS try to connect to the default instance?
I've installed SSDT and while trying to browse a cube in the SSAS instance on my local computer, I get the following error:
It is trying to connect to port 2383, which is for the default instance, ...
2
votes
1
answer
87
views
Does SQL guarantee that `SELECT *` in a subquery will be optimized to prevent a full scan?
I am exploring extended events query tracking and have question on couple of odd queries like following:
Example 1:
select top 10 * from ( SELECT [id] ,[date] ,[ordnum] ,
[...
1
vote
1
answer
2k
views
msdb.dbo.sysjobhistory data gets deleted whenever SQL Server services are restarted
I am facing an issue where my msdb.dbo.sysjobhistory data is getting deleted whenever my SQL Server services are restarted or the instance fails over.
I am using SQL Server 2014 SP2 in a cluster ...
2
votes
2
answers
32
views
How is Cassandra data stored physically on disk?
I am learning cassandra architecture and was going through how write and read is done. What I learned is that on a write the MemTable(sorted by partition key) and commitLog(append only) are written ...
0
votes
0
answers
21
views
What can produce slow down of mariadb
Our Mariadb database (1To) encounter slow down during some minutes but we don't find the root cause : there no CPU or IO overhead but locks OS dans lot of process taking time on sending data and ...
2
votes
2
answers
597
views
How to query through foreign keys while only having the value in the the same row as the value used as foreign key in another column
db admins, I require your help!
My experience working with databases directly using SQL is rather limited, since I've mostly been using ORMs to cover all my needs as a frontend developer, but this ...
2
votes
2
answers
8k
views
Load csv file to mysql using load data local infile
I need a simple way to load a csv file from my computer to the database (which is on a remote server).
I'm trying to load the file using a php page that executes this code:
$file = $_FILES['csv']['...
0
votes
1
answer
28
views
Does SQL Server eventually terminate idle connections to the database? How to terminate an idle connection to the database within 30 minutes or less?
We are only trying to answer the below questions regarding database connections for an audit. We are not having any issues, and nothing is broken. We are running SQL Server 2019 on Windows.
Does SQL ...
1
vote
1
answer
3k
views
timestamp with timezone issue with TZR vs TZH:TZM
I have a Hibernate with Oracle JDBC based application inserting/updating into the following column;
COLUMN_A TIMESTAMP WITH TIME ZONE DEFAULT systimestamp NOT NULL
but while it automatically appends ...
1
vote
1
answer
37
views
Getting ID when sorting by joined table using group also
I have small query to getting products based on variants (colors, materials), like:
SELECT
ANY_VALUE(id) AS id,
parent_id,
COUNT(id),
color,
material,
MIN(price) AS priceMin
FROM
(
...
0
votes
0
answers
10
views
Copy a partition from one database to another in postgres
I have a range (day wise) partitioned table managed using pg_partman extension and replica of the same is created in a different database.
I want to copy a partition specific of that day into another ...