Most Popular
1500 questions
33
votes
5 answers
Handling concurrent access to a key table without deadlocks in SQL Server
I have a table that is used by a legacy application as a substitute for IDENTITY fields in various other tables.
Each row in the table stores the last used ID LastID for the field named in IDName.
Occasionally the stored proc gets a deadlock - I…
Hannah Vernon
- 70,041
- 22
- 171
- 315
33
votes
5 answers
How to properly kill MySQL?
I have CentOS 64bit with CPanel installed and I use:
service mysql stop
It just keeps ticking periods and never seems like it stops. In the logs it just posts a lot of:
130303 17:42:38 [Warning] /usr/sbin/mysqld: Forcing close of thread
In the…
Tiffany Walker
33
votes
12 answers
How to search whole MySQL database for a particular string
is it possible to search a whole database tables ( row and column) to find out a particular string.
I am having a Database named A with about 35 tables,i need to search for the string named "hello" and i dont know on which table this string is…
Kevin Parker
- 431
- 1
- 4
- 5
33
votes
3 answers
Source several files at once with psql
I want to execute several sql scripts sequentially with psql as a single transaction to set up my database schema. What is the best way to do this? In the past I know I had a master script that I ran psql against that included the other files…
xenoterracide
- 2,851
- 5
- 30
- 33
33
votes
2 answers
Is it useful to have the SQL Server instance root directory on a separate drive?
I know it's possible to change many of the default paths when installing SQL Server, and generally when I do an install I change the data and log folders to be on separate drives (typically D and E), however I've recently been given a pre-installed…
adhocgeek
- 473
- 1
- 4
- 8
33
votes
1 answer
SQL Server 2019 executes unreachable code
[Update: This question describes a bug which has been fixed in Cumulative Update 5 for SQL Server 2019.]
Consider the following repro example (fiddle):
CREATE FUNCTION dbo.Repro (@myYear int)
RETURNS datetime
AS
BEGIN
IF @myYear <> 1990
…
Heinzi
- 3,145
- 2
- 29
- 42
33
votes
7 answers
Import a Oracle DMP file into a Fresh install of oracle
A client sent us an Oracle database we need to test against. We don't use Oracle or have any in-house Oracle expertise.
We need to setup the database so we can connect to it and debug a problem.
I did a fresh install of Oracle 9 (the version the…
James
33
votes
5 answers
Is it bad to have index space larger than data space?
Often I need to run queries against large tables that don't have the right index. So I ask the DBA to create such index. The first thing he does is look at the table statistics and see the index space size.
Often he would tell me to find an…
hjf
- 461
- 1
- 4
- 5
33
votes
1 answer
Warning about memory "Excessive Grant" in the query plan - how to find out what is causing it?
I am running a query that is giving the warning about a memory Excessive Grant.
There are too many tables and indexes used, including a complex view, and therefore it is difficult to add all of the definitions here.
I am trying to find what might me…
Marcello Miorelli
- 16,170
- 52
- 163
- 300
33
votes
1 answer
How does SQL Server determine key column order in missing index requests?
How does SQL Server determine the order of key columns in its missing index recommendations for a query plan?
Bryan Rebok
- 1,169
- 8
- 15
33
votes
3 answers
How do I enforce a write-once then read only database table in SQL?
Is it even possible?
My use case is a ledger table, with a requirement that once a record is created, it should be read-only, i.e. no-one should be able to edit or delete it. This only applies to the ledger table and tables with a direct relation to…
altanqa
- 433
- 4
- 6
33
votes
3 answers
Why index REBUILD does not reduce index fragmentatation?
I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It seems that this happens especially with small…
jrara
- 5,333
- 20
- 56
- 65
33
votes
7 answers
Does innodb_file_per_table affect mysql performance?
There are many articles exaggerating (IMHO) the need for innodb_file_per_table. I understand that with innodb_file_per_table there should be better control over individual tables, like being able to back up each table separately. However, the claims…
Googlebot
- 4,521
- 24
- 66
- 95
33
votes
2 answers
Database "frozen" on ALTER TABLE
Our production environment just froze* this morning for a while when altering a table, adding a column actually.
Offending SQL:ALTER TABLE cliente ADD COLUMN topicos character varying(20)[];
* Login into our system requires a select from that very…
Gonzalo Vasquez
- 1,019
- 2
- 18
- 30
33
votes
1 answer
Cannot execute as the database principal because the principal "dbo" does not exist
I restored a backup of a database from SQL Server 2008 R2 to SQL Server 2012.
When I try to access a particular page of my application, I get this error:
Cannot execute as the database principal because the principal "dbo" does not exist
It works…
Liquid
- 433
- 1
- 4
- 5