All Questions
18,625
questions with no upvoted or accepted answers
27
votes
0
answers
1k
views
Splitting Snapshot files with MaxBCPThreads for Transactional Replication
I've just set up a publication, and I'm attempting to get the snapshot to apply faster. So far the Distribution Agent is respecting the MaxBCPThreads settings, but the Snapshot Agent is not. I'm ...
- 271
13
votes
0
answers
3k
views
Best practices for keeping protobuf spec in sync with relational database (e.g. PostgreSQL)
1. We have a schema definition in Google's Protocol Buffers format that we use to generate code for different gRPC implementations (Python, PHP)
2. We have a database (PostreSQL) that is initialized ...
- 343
13
votes
1
answer
1k
views
HADR high worker thread usage
Why would an availability group's number of worker threads in an HADR pool increase well beyond minimum usage of "typically, there are 3–10 shared threads" per replica?
In one case we've observed ...
12
votes
0
answers
4k
views
PostgreSQL trigram GIST vs. GIN indexes
I have a PostgreSQL 9.1 database that contains 10M+ rows and some text fields that need similarity and %word%-like searches, so I decided to use trigram indexes.
Initially, I started to use GIN ...
- 771
11
votes
1
answer
15k
views
Conceptual data model for a billing system
I am working on an existing MySQL database. My task is to create a new data model for a billing system on that existing application.
I have created a conceptual data model (click to enlarge):
##...
- 2,034
11
votes
1
answer
833
views
MySQL deadlock: upsert query acquiring gap lock twice?
I am trying to debug a deadlock scenario which has been puzzling me for quite some time now. I am wondering if someone can shed any light into how this can happen.
Here is the deadlock output:
------...
- 111
10
votes
0
answers
202
views
Has SQL Server not Killed a Deadlock?
I have encountered a situation where a user has run something in one of our applications which runs a stored procedure. The stored procedure does a few bits of logging (INSERTing to a log table) and ...
- 1,379
10
votes
0
answers
310
views
dm_os_memory_cache_clock_hands understanding
As I mentioned in this question I'm trying to understand the working of clock hands. Simply put clock hands move when there is memory pressure so that cache entries can be removed or the cost can be ...
- 1,982
9
votes
1
answer
7k
views
DBeaver launch script from file
I'm trying to find a good alternative to SQL Developer for performing queries and scripts on Oracle instances.
I find DBeaver to be an interesting tool, it has a lot of great features, BUT it seems ...
- 91
9
votes
1
answer
1k
views
Convert query in words to relational algebra
I have a follow-up question to a question I have previously asked on SO.
Instead of the query from my initial question, I want to convert the following into relational algebra:
List the names and ...
- 91
9
votes
1
answer
756
views
SSMS 2014 (and 2012) CTRL+TAB requires pressing enter
Sometime last week my CTRL+TAB behavior changed. I used to hit CTRL+TAB, release, then I would be in my previous document tab. Now I hit CTRL+TAB, release, and the nav window is still open requiring ...
- 191
8
votes
0
answers
233
views
Why does the cast from double precision to numeric round to 15 significant digits?
The cast from double precision (float8) to numeric rounds to 15 significant decimal digits, thereby losing information. Clearly, more precision is possible. The cast to bigint (for values within its ...
- 152k
8
votes
0
answers
3k
views
Working on a decision tree database design
I'm doing the conceptual model for a Decision tree database. The goal is store any Decision tree.
I have four entity types: DecisionTree, Node, Branch and Leaf.
Each Decision tree is composed of ...
- 81
8
votes
1
answer
579
views
SQL Server Profiler: why the RPC Starting event does use an different decimal separator from SP Starting?
Trying to diagnose an problem, I've ran into that inconsistency.
In SQL Server Profiler, the RPC Starting event uses the regional settings to write real numbers (example: in Portuguese(Brazil) 14.5 ...
- 937
8
votes
0
answers
6k
views
How to backup some selected tables, but not all, in pgAdmin 4?
In pgAdmin 3, the dialog to perform a backup allowed you to check and un-check the tables you wanted to be part of your backup.
In pgAdmin 4, either I am unable to find this feature, or it is not ...
- 675
8
votes
0
answers
894
views
Extended Events - sqlserver.process_login_finish Not Firing?
We're trying to see whether a client is able to reach a database server, as it's receiving error:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied.
The error ...
- 1,811
8
votes
1
answer
987
views
Oracle Ref Partitioning: Deadlock due to child table row migration
My ordering application uses Oracle 11g Database. This DB has a primary table ORDERS and multiple child tables like ORDER_DETAILS, PLAN etc.
ORDERS table is LIST partitioned on STATUS column and all ...
- 245
8
votes
2
answers
3k
views
verify that mongos server is connected to config servers
I've been writing a backup script for sharded replica-sets and it's almost done. Except I can't seem to get it to successfully start the balancer backup after everything's all said and done.
Here's ...
- 223
8
votes
1
answer
1k
views
Select * from statement execute very slowly, innodb io read speed is low
I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: 5....
- 81
8
votes
1
answer
884
views
Database Stuck 'In Recovery' with command XTP_DB_RECOVERY
We recently converted a table to memory optimized data. Our backups have all ballooned (3x300GB files to 3x600GB files for Full, 3x50GB to 3x250GB for Diffs), and startup has been getting ...
8
votes
2
answers
40k
views
The INSERT/UPDATE/DELETE permission was denied on the object 'TheTable', database 'TheDb', schema 'dbo'
I have two SQL Server user accounts on a database that behave differently on INSERT / UPDATE / DELETE. SQL Server version is 2008 R2 (10.50.4042, 64 bits).
UserA (SQL Login): can do these commands (on ...
- 181
8
votes
1
answer
1k
views
Postgresql with just index storage
I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated.
...
- 81
7
votes
1
answer
104
views
Why does only one version of these procedures get blocked under moderate load?
The table:
CREATE TABLE [dbo].[Session] (
[SessionId] UNIQUEIDENTIFIER NOT NULL,
[CID] INT NULL,
[DEST] VARCHAR (50) NOT NULL,
[...
- 306
7
votes
1
answer
785
views
SSIS Package Not Being Deployed
I have an issue with an SSIS package, that when deployed via VS2017 to the integration services catalog is not actually being deployed. This is an individual package deployment, and not a project ...
- 71
7
votes
0
answers
2k
views
How to measure the memory usage of a Mongodb aggregation?
I recently started running into the Sort exceeded memory limit of... exception on an aggregation pipeline in MongoDB. I got around this by setting AllowDiskUse=True for the aggregation. However, I ...
7
votes
0
answers
748
views
How to minimize access exclusive table locks while reducing bloat or ALTER (ing) a table?
In some circumstances, I've been told not to do a VACUUM FULL (or CLUSTER) of a table that is in production, because that will exclusively lock it for longer than wished. The same might apply to ...
- 12.5k
7
votes
0
answers
13k
views
Generate UUID v4 automatically in MySQL
Is it possible to generate a UUID version 4 (completely random) in MySQL? I would like to use it as a primary key.
Use Case
Many systems will insert to the table, some may specify a UUID when ...
- 171
7
votes
0
answers
609
views
Inconsistent equality checking of Unicode text in Postgres 9.5?
Using PostgreSQL 9.5. It seems like Postgres is being inconsistent with how it compares strings similar to لى. A unique constraint is considering some strings equal that GROUP BY is considering ...
- 801
7
votes
1
answer
3k
views
MySQL query to create a Matrix view using pivot
I have a table with the following fields:
p1
p2
s1
s2
where p1 -> player1, p2 -> player2, s1 -> game1, s2 -> game2
The number of players can vary from 3 - 10.
The table have the results between them....
- 171
7
votes
1
answer
109
views
Does MySQL/InnoDB use table caches when dealing with foreign tables?
TL;DR: Does a table referred to via schema.table get held in table cache?
I have a large number of client databases that our application connects to based on an http request. These are used for every ...
- 241
7
votes
1
answer
2k
views
Change Tracking across several Tables
I'm looking into providing DB level change tracking within my system. I need to be able to track changes at an entity level, rather than just individual tables.
Right now, we have triggers on each of ...
- 405
7
votes
1
answer
1k
views
RDS MYSQL instance rebooted and won't load INNODB table
So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error ...
- 193
7
votes
1
answer
2k
views
Service Broker: Queue Monitor is dropped after poison message
I have searched everywhere and there isn't an answer online. There is one similar question on stackoverflow but it's not exactly the same and it has no accepted answer.
When I setup event ...
- 121
7
votes
2
answers
118
views
Changes access method for non-correlated subquery
Oracle 11g R2
Unfortunately our application has per row security "features".
We have a query that looks about like this:
Bad, slow:
SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
...
- 1,529
7
votes
1
answer
3k
views
Deferrable Foreign Key Constraints
What are the consequences of making a foreign key constraint DEFERRABLE INITIALLY IMMEDIATE instead of NONDEFERRABLE?
This answer on StackOverflow mentions the performance impact that comes with no ...
- 234
6
votes
0
answers
822
views
SQL Server 2019 service terminated unexpectedly at startup but manually starts
I've had a SQL Server 2019 server working great for the past few months, but it started stopping by itself since last Friday. This morning, I installed latest cumulative update on it.
Here is my ...
- 1,949
6
votes
1
answer
179
views
Do any of the graph based/aware databases have good mechanisms for maintaining referential integrity?
Do any of the graph-based/graph-aware databases (Neo4j, ArangoDB, OrientDB, or other) have mechanisms for maintaining referential integrity on a par with those offered by relational databases?
I'm ...
- 161
6
votes
1
answer
282
views
Using a Table as a Queue without sp_getapplock/sp_releaseapplock
I have a list of commands I need to execute, all of which are contained within a table I've named myQueue. This table is a little unique in that some commands should be grouped together such that ...
- 9,102
6
votes
0
answers
3k
views
How to control how JSON_ARRAYAGG orders results
I am testing MySQL JSON_ARRAY and JSON_ARRAYAGG functions in order to move some application expensive processes to SQL stored functions.
In the following e-commerce application example, I am trying ...
- 195
6
votes
0
answers
795
views
SQL Server 2012+ & Service Broker: SSBDT Spam ending conversations after sending message to NULL conversation_handle
I'm mostly posting this for community awareness, but feel free to give feedback if you think there is a solution other than coding around the problem as we are doing. Crossposting this from my bug ...
- 103
6
votes
1
answer
10k
views
Restore mongoDB by --repair and WiredTiger
We accidentally deleted the directory rm -rf /data/db
which was our MongoDB path, and thanks to
extundelete, we recovered it and got the directory /data/db.
Here are our files in the directory, and ...
- 61
6
votes
0
answers
70
views
Authoritative Extend Event object list?
I am looking for an authoritative list of objects for extended events, I want to know what version an object becomes available and some clues about what the value really represents. I have googled ...
- 6,028
6
votes
0
answers
1k
views
AWS RDS postrgres massive disk use, small tables
I can't figure out why our AWS postgres server has consumed all of it's space. We just had to up the storage space allocated to it, but can't find any hint from postgres that it's using that much ...
- 469
6
votes
0
answers
234
views
Using the Schema Generation Wizard to regenerate a previously-generated schema
Lately, I've been trying to clean up a group of projects that my company received from an outside contractor. Among them is an SSAS cube, with an associated data warehouse.
The contractors generated ...
- 171
6
votes
0
answers
605
views
Postgres 10 Logical Replication without truncating existing table
I have been fooling around with Postgres 10 logical replication, trying to learn about it and potentially apply it to a couple of projects. I have observed something that I am wondering about, but I ...
- 161
6
votes
0
answers
1k
views
RDS (MySQL): Swap usage growing but plenty of freeable memory?
We run a db.m4.10xlarge instance on Amazon's RDS. It runs 3 fairly busy databases and has a replica instance connected to it.
Over the last few weeks I noticed the swap usage has been steadily rising ...
- 171
6
votes
0
answers
383
views
UUID V1 and V4 bloats substantially after sequntial inserts and deletes. Why is it so, compared to serial and temporal BTree (which does not bloat)?
I am trying to understand the impact of deletions to bloat of tables and indexes after large number of inserts and deletes. The inserts and deletes are following a rather strict pattern: first the ...
- 820
6
votes
0
answers
3k
views
SQLPackage : Import or Export SQL Azure BACPAC without users
I know how to use SQLpackage.exe. I can export from SQL Azure and create a bacpac. I can also Import to SQL Server via SQLPackage.exe.
I want to export or import without the users in the database. The ...
- 1,325
6
votes
0
answers
613
views
CHANGE_TRACKING_CURRENT_VERSION() incorrect on database in standby
We are using a change tracking solution to export increments for BI purposes for years now. But it was running directly on the production database. To lower the load on the system we wanted it to run ...
- 161
6
votes
0
answers
5k
views
Postgres 9.6.1 Full Text Search dictionaries for most spoken languages
Using Mac OSX Yosemite 10.10.5, I am trying to run full text search operations, such as to_tsvector, to_tsquery, etc and have a need for dictionaries in about 80+ languages.
Postgres seems to only ...
- 213